在 R 中重新排列数据框

Question

我有 CSV 文件中不同变量的年度国家数据。数据有很多国家和地区。下面只是一个简单的例子，前五行的数据是什么样的。

region   LAM    LAM    LAM    LAM  LAM  LAM 
country  Brazil Brazil Brazil Peru Peru Peru
variable FC     FP     FCO    FC   FP   FCO 
1850     10     20     30     15   25   16  
1851     10     20     30     15   25   16  

一旦我将 CSV 文件作为 R 中的数据框读入，我想将其转换如下以使其易于使用。

region country  year   variable amount
LAM    Brazil   1850    FC     10  
LAM    Brazil   1851    FC     10
LAM    Brazil   1850    FP     20
LAM    Brazil   1850    FP     20
LAM    Brazil   1850    FCO    30 
LAM    Brazil   1850    FCO    30 
LAM    Peru     1850    FC     15 

有谁知道最简单的方法来做到这一点？

Answer 1

library(data.table)

(df <- fread("
region   LAM    LAM    LAM    LAM  LAM  LAM 
country  Brazil Brazil Brazil Peru Peru Peru
variable FC     FP     FCO    FC   FP   FCO 
1850     10     20     30     15   25   16  
1851     10     20     30     15   25   16",  header = FALSE))

df <- setnames(transpose(df), df[, V1]) # transpose df and set col names, where the first column of df is the var names. 

df <- df[-1, ] # then our df is df without the first row

df_long <- melt(df, id.vars = c("region", "country", "variable"), variable.name = "year", value.name = "amount")

df_long

    region  country variable year amount
1     LAM  Brazil       FC   1850     10
2     LAM  Brazil       FP   1850     20
3     LAM  Brazil      FCO   1850     30
4     LAM    Peru       FC   1850     15
5     LAM    Peru       FP   1850     25
6     LAM    Peru      FCO   1850     16
7     LAM  Brazil       FC   1851     10
8     LAM  Brazil       FP   1851     20
9     LAM  Brazil      FCO   1851     30
10    LAM    Peru       FC   1851     15
11    LAM    Peru       FP   1851     25
12    LAM    Peru      FCO   1851     16