转置数据框

Question

愉快的周末。

我一直在尝试用 R 复制博客文章中的结果。我正在寻找一种方法，在不使用t的情况下，最好使用tidyr或reshape来转置数据。在下面的示例中，metadata是通过转置data获得的。

metadata <- data.frame(colnames(data), t(data[1:4, ]) )
colnames(metadata) <- t(metadata[1,])
metadata <- metadata[-1,]
metadata$Multiplier <- as.numeric(metadata$Multiplier)

虽然它实现了我想要的功能，但我觉得它有点不熟练。有没有更有效的工作流程来转置数据框？

数据

data <- data.frame(
  Series.Description = c("Unit:", "Multiplier:", "Currency:", "Unique Identifier: "),
  Nominal.Broad.Dollar.Index. = c("Index:_1997_Jan_100", "1", NA, "H10/H10/JRXWTFB_N.M"),
  Nominal.Major.Currencies.Dollar.Index. = c("Index:_1973_Mar_100", "1", NA, "H10/H10/JRXWTFN_N.M"),
  Nominal.Other.Important.Trading.Partners.Dollar.Index. = c("Index:_1997_Jan_100", "1", NA, "H10/H10/JRXWTFO_N.M"),
  AUSTRALIA....SPOT.EXCHANGE.RATE..US..AUSTRALIAN...RECIPROCAL.OF.RXI_N.M.AL. = c("Currency:_Per_AUD", "1", "USD", "H10/H10/RXI$US_N.M.AL"),
  SPOT.EXCHANGE.RATE...EURO.AREA. = c("Currency:_Per_EUR", "1", "USD", "H10/H10/RXI$US_N.M.EU"),
  NEW.ZEALAND....SPOT.EXCHANGE.RATE..US..NZ...RECIPROCAL.OF.RXI_N.M.NZ.. = c("Currency:_Per_NZD", "1", "USD", "H10/H10/RXI$US_N.M.NZ"),
  United.Kingdom....Spot.Exchange.Rate..US..Pound.Sterling.Reciprocal.of.rxi_n.m.uk = c("Currency:_Per_GBP", "0.01", "USD", "H10/H10/RXI$US_N.M.UK"),
  BRAZIL....SPOT.EXCHANGE.RATE..REAIS.US.. = c("Currency:_Per_USD", "1", "BRL", "H10/H10/RXI_N.M.BZ"),
  CANADA....SPOT.EXCHANGE.RATE..CANADIAN...US.. = c("Currency:_Per_USD", "1", "CAD", "H10/H10/RXI_N.M.CA"),
  CHINA....SPOT.EXCHANGE.RATE..YUAN.US.. = c("Currency:_Per_USD", "1", "CNY", "H10/H10/RXI_N.M.CH"),
  DENMARK....SPOT.EXCHANGE.RATE..KRONER.US.. = c("Currency:_Per_USD", "1", "DKK", "H10/H10/RXI_N.M.DN"),
  HONG.KONG....SPOT.EXCHANGE.RATE..HK..US.. = c("Currency:_Per_USD", "1", "HKD", "H10/H10/RXI_N.M.HK"),
  INDIA....SPOT.EXCHANGE.RATE..RUPEES.US. = c("Currency:_Per_USD", "1", "INR", "H10/H10/RXI_N.M.IN"),
  JAPAN....SPOT.EXCHANGE.RATE..YEA.US.. = c("Currency:_Per_USD", "1", "JPY", "H10/H10/RXI_N.M.JA"),
  KOREA....SPOT.EXCHANGE.RATE..WON.US.. = c("Currency:_Per_USD", "1", "KRW", "H10/H10/RXI_N.M.KO"),
  Malaysia...Spot.Exchange.Rate..Ringgit.US.. = c("Currency:_Per_USD", "1", "MYR", "H10/H10/RXI_N.M.MA"),
  MEXICO....SPOT.EXCHANGE.RATE..PESOS.US.. = c("Currency:_Per_USD", "1", "MXN", "H10/H10/RXI_N.M.MX"),
  NORWAY....SPOT.EXCHANGE.RATE..KRONER.US.. = c("Currency:_Per_USD", "1", "NOK", "H10/H10/RXI_N.M.NO"),
  SWEDEN....SPOT.EXCHANGE.RATE..KRONOR.US.. = c("Currency:_Per_USD", "1", "SEK", "H10/H10/RXI_N.M.SD"),
  SOUTH.AFRICA....SPOT.EXCHANGE.RATE..RAND.US.. = c("Currency:_Per_USD", "1", "ZAR", "H10/H10/RXI_N.M.SF"),
  Singapore...SPOT.EXCHANGE.RATE..SINGAPORE...US.. = c("Currency:_Per_USD", "1", "SGD", "H10/H10/RXI_N.M.SI"),
  SRI.LANKA....SPOT.EXCHANGE.RATE..RUPEES.US.. = c("Currency:_Per_USD", "1", "LKR", "H10/H10/RXI_N.M.SL"),
  SWITZERLAND....SPOT.EXCHANGE.RATE..FRANCS.US.. = c("Currency:_Per_USD", "1", "CHF", "H10/H10/RXI_N.M.SZ"),
  TAIWAN....SPOT.EXCHANGE.RATE..NT..US.. = c("Currency:_Per_USD", "1", "TWD", "H10/H10/RXI_N.M.TA"),
  THAILAND....SPOT.EXCHANGE.RATE....THAILAND. = c("Currency:_Per_USD", "1", "THB", "H10/H10/RXI_N.M.TH"),
  VENEZUELA....SPOT.EXCHANGE.RATE..BOLIVARES.US.. = c("Currency:_Per_USD", "1", "VEB", "H10/H10/RXI_N.M.VE")
)

- ExperimenteR

Answer 1

使用tidyr，你可以gather除第一列外的所有列，然后spread已经聚合的列。

尝试：

library(dplyr)
library(tidyr)
data %>%
  gather(var, val, 2:ncol(data)) %>%
  spread(Series.Description, val)

- A5C1D2H2I1M1N2O1R2T1

---

非常漂亮，@AnandaMahto。非常感谢。我从来没有理解过 tidyr 的内部工作原理。这是我周末的学习材料。 - ExperimenteR

我很喜欢这个解决方案。稍微通用一点的方法是，将 spread(Series.Description, val) 替换为 spread_(names(data)[1], "val")。 - jbkunst

非常优雅。我已经进入了一项密集的R学习体验，从基本上零的知识开始已经约有3个月了。（而我并非开发人员出身或职业）能否有人解释一下为什么在这种情况下gather/spread比melt/dcast更好？这对我的学习非常有帮助。我知道这更适合在blog.rstudio上提问，但这个问题在这里，而不是那里！ - Steve

@Steve gather/spread并不比melt/dcast更好；它只是一种不同的解决方案，适用于不同的数据操作语法。例如，您会注意到dcast在重塑时也允许聚合，而spread则需要已经聚合的数据。如果您只是重塑数据，则我认为这取决于个人偏好。 - A5C1D2H2I1M1N2O1R2T1

gather 和 spread 已经被弃用。请使用 pivot_longer 和 pivot_wider。 - Werner

Answer 2

library(dplyr)
# Omitted data <- structure part ...

下面是一个更通用的示例，可以在 Series.Description 不是结果的第一列时使用，并使用更新的 pivot_wider/pivot_longer 动词

df_transpose <- function(df) {
  
  df %>% 
    tidyr::pivot_longer(-1) %>%
    tidyr::pivot_wider(names_from = 1, values_from = value)

}

df_transpose(data)
#> # A tibble: 26 x 5
#>    name                   `Unit:`    `Multiplier:` `Currency:` `Unique Identifi…
#>    <chr>                  <chr>      <chr>         <chr>       <chr>            
#>  1 Nominal.Broad.Dollar.… Index:_19… 1             <NA>        H10/H10/JRXWTFB_…
#>  2 Nominal.Major.Currenc… Index:_19… 1             <NA>        H10/H10/JRXWTFN_…
#>  3 Nominal.Other.Importa… Index:_19… 1             <NA>        H10/H10/JRXWTFO_…
#>  4 AUSTRALIA....SPOT.EXC… Currency:… 1             USD         H10/H10/RXI$US_N…
#>  5 SPOT.EXCHANGE.RATE...… Currency:… 1             USD         H10/H10/RXI$US_N…
#>  6 NEW.ZEALAND....SPOT.E… Currency:… 1             USD         H10/H10/RXI$US_N…
#>  7 United.Kingdom....Spo… Currency:… 0.01          USD         H10/H10/RXI$US_N…
#>  8 BRAZIL....SPOT.EXCHAN… Currency:… 1             BRL         H10/H10/RXI_N.M.…
#>  9 CANADA....SPOT.EXCHAN… Currency:… 1             CAD         H10/H10/RXI_N.M.…
#> 10 CHINA....SPOT.EXCHANG… Currency:… 1             CNY         H10/H10/RXI_N.M.…
#> # … with 16 more rows

但是请注意（与上面的回答一样），第一列的名称将丢失。以下方法保留了它（我猜@jbkunst提出的spread_(names(data)[1], "val")方法也是如此）。

df_transpose <- function(df) {
  
  first_name <- colnames(df)[1]
  
  temp <-
    df %>% 
    tidyr::pivot_longer(-1) %>%
    tidyr::pivot_wider(names_from = 1, values_from = value)
  
  colnames(temp)[1] <- first_name
  temp
}

df_transpose(data)
#> # A tibble: 26 x 5
#>    Series.Description       `Unit:`   `Multiplier:` `Currency:` `Unique Identif…
#>    <chr>                    <chr>     <chr>         <chr>       <chr>           
#>  1 Nominal.Broad.Dollar.In… Index:_1… 1             <NA>        H10/H10/JRXWTFB…
#>  2 Nominal.Major.Currencie… Index:_1… 1             <NA>        H10/H10/JRXWTFN…
#>  3 Nominal.Other.Important… Index:_1… 1             <NA>        H10/H10/JRXWTFO…
#>  4 AUSTRALIA....SPOT.EXCHA… Currency… 1             USD         H10/H10/RXI$US_…
#>  5 SPOT.EXCHANGE.RATE...EU… Currency… 1             USD         H10/H10/RXI$US_…
#>  6 NEW.ZEALAND....SPOT.EXC… Currency… 1             USD         H10/H10/RXI$US_…
#>  7 United.Kingdom....Spot.… Currency… 0.01          USD         H10/H10/RXI$US_…
#>  8 BRAZIL....SPOT.EXCHANGE… Currency… 1             BRL         H10/H10/RXI_N.M…
#>  9 CANADA....SPOT.EXCHANGE… Currency… 1             CAD         H10/H10/RXI_N.M…
#> 10 CHINA....SPOT.EXCHANGE.… Currency… 1             CNY         H10/H10/RXI_N.M…
#> # … with 16 more rows

^{此内容由 reprex 软件包 (v2.0.0) 于2021年05月30日创建。}

- Ian Gow