可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效,请关闭广告屏蔽插件后再试):
问题:
I'm having trouble rearranging the following data frame:
set.seed(45)
dat1 <- data.frame(
name = rep(c("firstName", "secondName"), each=4),
numbers = rep(1:4, 2),
value = rnorm(8)
)
dat1
name numbers value
1 firstName 1 0.3407997
2 firstName 2 -0.7033403
3 firstName 3 -0.3795377
4 firstName 4 -0.7460474
5 secondName 1 -0.8981073
6 secondName 2 -0.3347941
7 secondName 3 -0.5013782
8 secondName 4 -0.1745357
I want to reshape it so that each unique "name" variable is a rowname, with the "values" as observations along that row and the "numbers" as colnames. Sort of like this:
name 1 2 3 4
1 firstName 0.3407997 -0.7033403 -0.3795377 -0.7460474
5 secondName -0.8981073 -0.3347941 -0.5013782 -0.1745357
I've looked at melt
and cast
and a few other things, but none seem to do the job.
回答1:
Using reshape
function:
reshape(dat1, idvar = "name", timevar = "numbers", direction = "wide")
回答2:
The new (in 2014) tidyr
package also does this simply, with gather()
/spread()
being the terms for melt
/cast
.
library(tidyr)
spread(dat1, key = numbers, value = value)
From github,
tidyr
is a reframing of reshape2
designed to accompany the tidy data framework, and to work hand-in-hand with magrittr
and dplyr
to build a solid pipeline for data analysis.
Just as reshape2
did less than reshape, tidyr
does less than reshape2
. It's designed specifically for tidying data, not the general reshaping that reshape2
does, or the general aggregation that reshape did. In particular, built-in methods only work for data frames, and tidyr
provides no margins or aggregation.
回答3:
You can do this with the reshape()
function, or with the melt()
/ cast()
functions in the reshape package. For the second option, example code is
library(reshape)
cast(dat1, name ~ numbers)
Or using reshape2
library(reshape2)
dcast(dat1, name ~ numbers)
回答4:
Another option if performance is a concern is to use data.table
's extension of reshape2
's melt & dcast functions
(Reference: Efficient reshaping using data.tables)
library(data.table)
setDT(dat1)
dcast(dat1, name ~ numbers, value.var = "value")
# name 1 2 3 4
# 1: firstName 0.1836433 -0.8356286 1.5952808 0.3295078
# 2: secondName -0.8204684 0.4874291 0.7383247 0.5757814
And, as of data.table v1.9.6 we can cast on multiple columns
## add an extra column
dat1[, value2 := value * 2]
## cast multiple value columns
dcast(dat1, name ~ numbers, value.var = c("value", "value2"))
# name value_1 value_2 value_3 value_4 value2_1 value2_2 value2_3 value2_4
# 1: firstName 0.1836433 -0.8356286 1.5952808 0.3295078 0.3672866 -1.6712572 3.190562 0.6590155
# 2: secondName -0.8204684 0.4874291 0.7383247 0.5757814 -1.6409368 0.9748581 1.476649 1.1515627
回答5:
Using your example dataframe, we could:
xtabs(value ~ name + numbers, data = dat1)
回答6:
Other two options:
Base package:
df <- unstack(dat1, form = value ~ numbers)
rownames(df) <- unique(dat1$name)
df
sqldf
package:
library(sqldf)
sqldf('SELECT name,
MAX(CASE WHEN numbers = 1 THEN value ELSE NULL END) x1,
MAX(CASE WHEN numbers = 2 THEN value ELSE NULL END) x2,
MAX(CASE WHEN numbers = 3 THEN value ELSE NULL END) x3,
MAX(CASE WHEN numbers = 4 THEN value ELSE NULL END) x4
FROM dat1
GROUP BY name')
回答7:
Using base R aggregate
function:
aggregate(value ~ name, dat1, I)
# name value.1 value.2 value.3 value.4
#1 firstName 0.4145 -0.4747 0.0659 -0.5024
#2 secondName -0.8259 0.1669 -0.8962 0.1681
回答8:
There's very powerful new package from genius data scientists at Win-Vector (folks that made vtreat
, seplyr
and replyr
) called cdata
. It implements "coordinated data" principles described in this document and also in this blog post. The idea is that regardless how you organize your data, it should be possible to identify individual data points using a system of "data coordinates". Here's a excerpt from the recent blog post by John Mount:
The whole system is based on two primitives or operators
cdata::moveValuesToRowsD() and cdata::moveValuesToColumnsD(). These
operators have pivot, un-pivot, one-hot encode, transpose, moving
multiple rows and columns, and many other transforms as simple special
cases.
It is easy to write many different operations in terms of the
cdata primitives. These operators can work-in memory or at big data
scale (with databases and Apache Spark; for big data use the
cdata::moveValuesToRowsN() and cdata::moveValuesToColumnsN()
variants). The transforms are controlled by a control table that
itself is a diagram of (or picture of) the transform.
We will first build the control table (see blog post for details) and then perform the move of data from rows to columns.
library(cdata)
# first build the control table
pivotControlTable <- buildPivotControlTableD(table = dat1, # reference to dataset
columnToTakeKeysFrom = 'numbers', # this will become column headers
columnToTakeValuesFrom = 'value', # this contains data
sep="_") # optional for making column names
# perform the move of data to columns
dat_wide <- moveValuesToColumnsD(tallTable = dat1, # reference to dataset
keyColumns = c('name'), # this(these) column(s) should stay untouched
controlTable = pivotControlTable# control table above
)
dat_wide
#> name numbers_1 numbers_2 numbers_3 numbers_4
#> 1 firstName 0.3407997 -0.7033403 -0.3795377 -0.7460474
#> 2 secondName -0.8981073 -0.3347941 -0.5013782 -0.1745357
回答9:
The base reshape
function works perfectly fine:
df <- data.frame(
year = c(rep(2000, 12), rep(2001, 12)),
month = rep(1:12, 2),
values = rnorm(24)
)
df_wide <- reshape(df, idvar="year", timevar="month", v.names="values", direction="wide", sep="_")
df_wide
Where
idvar
is the column of classes that separates rows
timevar
is the column of classes to cast wide
v.names
is the column containing numeric values
direction
specifies wide or long format
- the optional
sep
argument is the separator used in between timevar
class names and v.names
in the output data.frame
.
If no idvar
exists, create one before using the reshape()
function:
df$id <- c(rep("year1", 12), rep("year2", 12))
df_wide <- reshape(df, idvar="id", timevar="month", v.names="values", direction="wide", sep="_")
df_wide
Just remember that idvar
is required! The timevar
and v.names
part is easy. The output of this function is more predictable than some of the others, as everything is explicitly defined.
回答10:
With the devel version of tidyr
‘0.8.3.9000’
, there is pivot_wider
and pivot_longer
which is generalized to do the reshaping (long -> wide, wide -> long, respectively) from 1 to multiple columns. Using the OP's data
-single column long -> wide
library(dplyr)
library(tidyr)
dat1 %>%
pivot_wider(names_from = numbers, values_from = value)
# A tibble: 2 x 5
# name `1` `2` `3` `4`
# <fct> <dbl> <dbl> <dbl> <dbl>
#1 firstName 0.341 -0.703 -0.380 -0.746
#2 secondName -0.898 -0.335 -0.501 -0.175
-> created another column for showing the functionality
dat1 %>%
mutate(value2 = value * 2) %>%
pivot_wider(names_from = numbers, values_from = c("value", "value2"))
# A tibble: 2 x 9
# name value_1 value_2 value_3 value_4 value2_1 value2_2 value2_3 value2_4
# <fct> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#1 firstName 0.341 -0.703 -0.380 -0.746 0.682 -1.41 -0.759 -1.49
#2 secondName -0.898 -0.335 -0.501 -0.175 -1.80 -0.670 -1.00 -0.349