From Long to Wide Data with Multiple Columns

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

Convert long data to wide data with multiple columns and multiple values

Bit tricky with pivoting twice, but here you go:

library(tidyverse)

DF %>% 
  group_by(NAME, SUBJECT) %>% 
  mutate(ind = row_number()) %>% 
  ungroup() %>% 
  pivot_longer(c("YEAR", "MARKS", "MAXIMUM")) %>% 
  mutate(name = paste0(name, ind)) %>% 
  select(-ind) %>% 
  pivot_wider(names_from = c("SUBJECT", "name"), values_from = "value")

Transform long-to-wide database spreading data on multiple columns in R

You have to add a sequential ID for each group:

library(tidyverse)
df %>% 
  group_by(ID) %>% 
  mutate(count = row_number()) %>% 
  pivot_wider(ID, names_from = count, values_from = c(visit, var1, var2))

# A tibble: 3 x 13
# Groups:   ID [3]
#     ID visit_1 visit_2 visit_3 visit_4 var1_1 var1_2 var1_3 var1_4 var2_1 var2_2 var2_3 var2_4
#  <dbl>   <dbl>   <dbl>   <dbl>   <dbl> <chr>  <chr>  <chr>  <chr>  <chr>  <chr>  <chr>  <chr> 
#1     1       1       4       5       7 AF     no     no     no     good   good   good   bad   
#2     2       1       3       4      NA AG     AG     no     NA     good   good   bad    NA    
#3     3       2       5       6      NA BA     BA     BA     NA     good   good   good   NA    

---

Related Topics