在R中使用从宽到长的重塑

I am trying to reshape Data from wide to long in R.

我试图在R中从宽到长重塑数据。

my data in wide format looks like this:

我的宽格式数据如下所示:

I have the following Data-Matrix: in the rows i have the different companies, and in the columns in have different variables from different years. (earnings_2012, earnings_2011, earnings_2010,...,tot_assets_2012,tot_assets_2011, and so on.

我有以下数据矩阵:在行中我有不同的公司,并且在列中有不同年份的不同变量。 (earnings_2012,earnings_2011,earnings_2010,...,tot_assets_2012,tot_assets_2011等。

I would like to rearrange this into a long format:

我想将其重新排列成长格式:

having only these columns: company, year, variables (earnings,tot_assets,..)

只有这些列:公司,年份,变量(收入,tot_assets,..)

I've been trying for days now. I also consulted http://www.ats.ucla.edu/stat/r/faq/reshape.htm for help but i could not grasp how they reshaped their data and how to detach the year.

我已经好几天了。我还咨询了http://www.ats.ucla.edu/stat/r/faq/reshape.htm寻求帮助,但我无法理解他们如何重塑他们的数据以及如何分离这一年。

Thank you in advance for your help.

预先感谢您的帮助。

regards

Gritti

PS: Since I am new to R and the provided help in R itself seems to be rather technical I appreciate any hints for a very simple introduction.

PS:因为我是R的新手并且R本身提供的帮助似乎相当技术性我欣赏任何有关非常简单的介绍的提示。

1 个解决方案

#1

Here are three examples (along with some sample data that I think is representative of what you described).

以下是三个示例(以及我认为代表您所描述内容的一些示例数据)。

Here's the sample data:

这是示例数据:

set.seed(1)
mydf <- data.frame(
  company = LETTERS[1:4],
  earnings_2012 = runif(4),
  earnings_2011 = runif(4),
  earnings_2010 = runif(4),
  assets_2012 = runif(4),
  assets_2011 = runif(4),
  assets_2010 = runif(4)
)

mydf
#   company earnings_2012 earnings_2011 earnings_2010 assets_2012 assets_2011 assets_2010
# 1       A     0.2655087     0.2016819    0.62911404   0.6870228   0.7176185   0.9347052
# 2       B     0.3721239     0.8983897    0.06178627   0.3841037   0.9919061   0.2121425
# 3       C     0.5728534     0.9446753    0.20597457   0.7698414   0.3800352   0.6516738
# 4       D     0.9082078     0.6607978    0.17655675   0.4976992   0.7774452   0.1255551

Option 1: reshape

One limitation is that it won't handle "unbalanced" datasets (for example, if you didn't have "assets_2010" as part of your data, this wouldn't work).

一个限制是它不会处理“不平衡”数据集(例如,如果您没有“assets_2010”作为数据的一部分,这将无法工作)。

reshape(mydf, direction = "long", idvar="company", 
        varying = 2:ncol(mydf), sep = "_")
#        company time   earnings    assets
# A.2012       A 2012 0.26550866 0.6870228
# B.2012       B 2012 0.37212390 0.3841037
# C.2012       C 2012 0.57285336 0.7698414
# D.2012       D 2012 0.90820779 0.4976992
# A.2011       A 2011 0.20168193 0.7176185
# B.2011       B 2011 0.89838968 0.9919061
# C.2011       C 2011 0.94467527 0.3800352
# D.2011       D 2011 0.66079779 0.7774452
# A.2010       A 2010 0.62911404 0.9347052
# B.2010       B 2010 0.06178627 0.2121425
# C.2010       C 2010 0.20597457 0.6516738
# D.2010       D 2010 0.17655675 0.1255551

Option 2: The "reshape2" package

Quite popular for its syntax. Needs a little bit of processing before it can work since the column names need to be split in order for us to get this "double-wide" type of data. Is able to handle unbalanced data, but won't be the best if your varying columns are of different column types (numeric, character, factor).

它的语法相当流行。需要进行一些处理才能工作,因为需要拆分列名以便我们获得这种“双宽”类型的数据。能够处理不平衡的数据,但如果您的变量列具有不同的列类型(数字,字符,因子),则不是最好的。

library(reshape2)
dfL <- melt(mydf, id.vars="company")
dfL <- cbind(dfL, colsplit(dfL$variable, "_", c("var", "year")))
dcast(dfL, company + year ~ var, value.var="value")
#    company year    assets   earnings
# 1        A 2010 0.9347052 0.62911404
# 2        A 2011 0.7176185 0.20168193
# 3        A 2012 0.6870228 0.26550866
# 4        B 2010 0.2121425 0.06178627
# 5        B 2011 0.9919061 0.89838968
# 6        B 2012 0.3841037 0.37212390
# 7        C 2010 0.6516738 0.20597457
# 8        C 2011 0.3800352 0.94467527
# 9        C 2012 0.7698414 0.57285336
# 10       D 2010 0.1255551 0.17655675
# 11       D 2011 0.7774452 0.66079779
# 12       D 2012 0.4976992 0.90820779

Option 3: merged.stack from "splitstackshape"

merged.stack from my "splitstackshape" package has pretty straightforward syntax and should be pretty fast if you need to end up with this "double-wide" type of structure. It was created to be able to handle unbalanced data and since it treats columns separately, won't have problems with converting column types.

我的“splitstackshape”包中的merged.stack具有相当简单的语法,如果你需要最终使用这种“双宽”类型的结构,它应该非常快。它的创建是为了能够处理不平衡的数据,因为它分别处理列,所以不会有转换列类型的问题。

library(splitstackshape)
merged.stack(mydf, id.vars="company", 
             var.stubs=c("earnings", "assets"), sep = "_")
#     company .time_1   earnings    assets
#  1:       A    2010 0.62911404 0.9347052
#  2:       A    2011 0.20168193 0.7176185
#  3:       A    2012 0.26550866 0.6870228
#  4:       B    2010 0.06178627 0.2121425
#  5:       B    2011 0.89838968 0.9919061
#  6:       B    2012 0.37212390 0.3841037
#  7:       C    2010 0.20597457 0.6516738
#  8:       C    2011 0.94467527 0.3800352
#  9:       C    2012 0.57285336 0.7698414
# 10:       D    2010 0.17655675 0.1255551
# 11:       D    2011 0.66079779 0.7774452
# 12:       D    2012 0.90820779 0.4976992