R: Reshaping Multiple Columns from Long to Wide

Convert data from long format to wide format with multiple measure columns

In order to handle multiple variables like you want, you need to melt the data you have before casting it.

library("reshape2")

dcast(melt(my.df, id.vars=c("ID", "TIME")), ID~variable+TIME)

which gives

  ID X_1 X_2 X_3 X_4 X_5 Y_1 Y_2 Y_3 Y_4 Y_5
1  A   1   4   7  10  13  16  19  22  25  28
2  B   2   5   8  11  14  17  20  23  26  29
3  C   3   6   9  12  15  18  21  24  27  30

---

EDIT based on comment:

The data frame

num.id = 10 
num.time=10 
my.df <- data.frame(ID=rep(LETTERS[1:num.id], num.time), 
                    TIME=rep(1:num.time, each=num.id), 
                    X=1:(num.id*num.time), 
                    Y=(num.id*num.time)+1:(2*length(1:(num.id*num.time))))

gives a different result (all entries are 2) because the ID/TIME combination does not indicate a unique row. In fact, there are two rows with each ID/TIME combinations. reshape2 assumes a single value for each possible combination of the variables and will apply a summary function to create a single variable is there are multiple entries. That is why there is the warning

Aggregation function missing: defaulting to length

You can get something that works if you add another variable which breaks that redundancy.

my.df$cycle <- rep(1:2, each=num.id*num.time)
dcast(melt(my.df, id.vars=c("cycle", "ID", "TIME")), cycle+ID~variable+TIME)

This works because cycle/ID/time now uniquely defines a row in my.df.

Reshaping from long to wide with multiple columns

pivot_wider may be easier

library(dplyr)
library(stringr)
library(tidyr)
df %>% 
   mutate(time = str_c('t', time)) %>%
   pivot_wider(names_from = time, values_from = c(age, height))

-output

# A tibble: 2 × 5
    PIN age_t1 age_t2 height_t1 height_t2
  <dbl>  <dbl>  <dbl>     <dbl>     <dbl>
1  1001     84     86        58        58
2  1002     22     24        60        62

---

With reshape from base R, it may need a sequence column

out <- reshape(transform(df, rn = ave(seq_along(PIN), PIN,
   FUN = seq_along)), idvar = "PIN", 
    direction = "wide", timevar = "time", sep = "_")
out[!startsWith(names(out), 'rn_')]
   PIN age_1 height_1 age_2 height_2
1 1001    84       58    86       58
3 1002    22       60    24       62

From long to wide data with multiple columns

Here's another alternative using tidyr:

library(tidyr)
foo %>%
  gather(key, value, -group, -times, -num_users) %>%
  unite(col, key, times) %>%
  spread(col, value)

Which gives:

#  group num_users action_rate_after action_rate_before action_rate_c95_after
#1     a       100              0.15                0.1            0.06962893
#2     b       200              0.18                0.2            0.05297400
#3     c       300              0.35                0.3            0.05369881
#  action_rate_c95_before
#1             0.05850000
#2             0.05515433
#3             0.05159215

Reshaping multiple long columns into wide column format in R

One option is the function pivot_wider() from the tidyr package:

df.wide <- tidyr::pivot_wider(df2,
                              names_from = c("Type", "Dept", "Year"),
                              values_from = "Salary",
                              values_fn = {mean})

This should get you the desired result.

Reshape long to wide with two columns to expand in R data.table [R]

You may use dcast -

library(data.table)

setDT(data_sample)
dcast(data_sample, code~rowid(code), value.var = c('name', 'numberdata'))

#   code name_1 name_2 numberdata_1 numberdata_2
#1:    1   bill    bob          100          400
#2:    2    rob   john          300         -500
#3:    3    max    joe         -200         -400
#4:    4  mitch   bart          300          100

R- How to reshape Long to Wide with multiple variables/columns

Some variables are can be better to together

df %>%
  pivot_wider(id_cols = c(UserID, Full.Name, DOB, EncounterID), names_from = c(QuestionID, QName, labelnospaces), values_from = responses)

  UserID Full.Name  DOB    EncounterID `505_Intro_Were you given any info?` `506_Care_By using this service..`
   <int> <chr>      <chr>        <int> <chr>                                <chr>                             
1      1 John Smith 1-1-90          13 yes                                  yes                               
2      2 Jane Doe   2-2-80          14 no                                   no                                
  `507_Out_How satisfied are you?`
  <chr>                           
1 vsat                            
2 unsat

---

Related Topics