CODEWITHSUNDEEP
rdataframedata.table
Tyler D
Tyler D

Reputation: 333

Expand dataframe columns

Say I have a data.frame or data.table of the following form:

df <- data.frame("date"=c("2012-11-07", "2012-11-07", "2012-11-08", "2012-11-08", "2012-11-06", "2012-11-06"),
                 "id"=c(1,2, 1, 2, 2, 1),
                 "val"=c(100, 101, 102, 103, 104, 105))

I like to expand this so that each id gets its own column containing the val that goes with a given date, i.e. it should take the form

df.new <- data.frame("date"=c("2012-11-06", "2012-11-07", "2012-11-08"),
                     "1"=c(105, 100, 102),
                     "2"=c(104, 101, 103))

Does a method exist for data.frames or data.tables that can do this?

Upvotes: 0

Views: 57

Answers (4)

ThomasIsCoding
ThomasIsCoding

Reputation: 101034

A base R option using reshape

reshape(
  df[with(df, order(date, id)), ],
  direction = "wide",
  idvar = "date",
  timevar = "id"
)

which gives

        date val.1 val.2
6 2012-11-06   105   104
1 2012-11-07   100   101
3 2012-11-08   102   103

Upvotes: 1

Michieldo
Michieldo

Reputation: 189

With dcast:

dcast(df, date ~ id)

  date        1   2
1 2012-11-06 105 104
2 2012-11-07 100 101
3 2012-11-08 102 103

Upvotes: 1

Pierre Lapointe
Pierre Lapointe

Reputation: 16277

With data.table:

library(data.table)
setDT(df)
dcast.data.table(df,date~id, value.var="val")
         date   1   2
1: 2012-11-06 105 104
2: 2012-11-07 100 101
3: 2012-11-08 102 103

Upvotes: 1

Karthik S
Karthik S

Reputation: 11584

Does this work:

library(dplyr)
library(tidyr)
df %>% pivot_wider(names_from = id, values_from = val, names_prefix = 'X')
# A tibble: 3 x 3
  date          X1    X2
  <chr>      <dbl> <dbl>
1 2012-11-07   100   101
2 2012-11-08   102   103
3 2012-11-06   105   104

Upvotes: 1

Related Questions