Convert Data from Long Format to Wide Format With Multiple Measure 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.

Reshape data from long to wide with multiple measure columns using spread() or other reshape functions

A tidyr solution below. You need to gather the region into a single column to be able to spread it.

library(tidyr)
data %>% gather(region,val,-age) %>% spread(age,val)  

#   region age 0 age 1 age 10 age 11 age 12 age 2 age 3 age 4 age 5 age 6 age 7 age 8 age 9
# 1     X1     2     2      6      3      3     2     4     7    12    19    22    18    11
# 2     X2     2     2      7      4      3     3     4     8    14    21    24    20    12

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

Converting to wide format from long with multiple id and value columns

Here's the way to do this using tidyr. The trick is that you need to do a gather first:

library(tidyr)
df_wide <- df %>%
  gather(key, value, V1:V5) %>%
  unite("key", key, Week, sep = ".") %>%
  spread(key, value)

df_wide
#>   Route         Address V1.Week1 V1.Week2 V2.Week1 V2.Week2 V3.Week1
#> 1     A  12345_SE_Court        0        0        1        0        0
#> 2     A 33333_NE_Street        0        1        1        0        1
#> 3     B  98765_NW_Drive        1        0        1        1        0
#> 4     C   10293_SW_Road        0        1        0        0        0
#>   V3.Week2 V4.Week1 V4.Week2 V5.Week1 V5.Week2
#> 1        1        0        1        0        1
#> 2        1        0        0        0        0
#> 3        0        0        1        1        0
#> 4        0        0        0        1        1

Created on 2018-06-27 by the reprex package (v0.2.0).

Convert from long to wide format with multiple unique variables to other unique variables in R

We can use pivot_wider after creating a sequence column by group

library(dplyr)
library(tidyr)
df %>% 
    group_by(block, species, den) %>% 
    mutate(rn = row_number()) %>% 
    ungroup %>% 
    pivot_wider(names_from = rn, values_from = c(area, size), names_sep = ".")
# A tibble: 8 x 9
#  block species den   area.1 area.2 area.3 size.1 size.2 size.3
#  <fct> <fct>   <fct>  <int>  <int>  <int>  <int>  <int>  <int>
#1 1     A       20         1      2     NA     17     18     NA
#2 1     A       50         3      4     NA     19     20     NA
#3 1     B       20         5      6     NA     21     22     NA
#4 1     B       50         7      8     NA     23     24     NA
#5 2     A       20         9     10     NA     25     26     NA
#6 2     A       50        11     12     NA     27     28     NA
#7 2     B       20        13     14     NA     29     30     NA
#8 2     B       50        15     16     17     31     32     33

long to wide - converting one column (with multiple measures) into a pair of columns

You're looking for a self-join:

dt1 %>% left_join(dt1, by = "fam") %>%
  filter(id.x < id.y)
#    id.x fam    value.x id.y    value.y
# 1     1   a  0.1231950    2  0.1090744
# 2     1   a  0.1231950    6  0.6753560
# 3     2   a  0.1090744    6  0.6753560
# 4     5   b  1.2385780    7  0.7440739
# 5     5   b  1.2385780   10  2.4763114
# 6     7   b  0.7440739   10  2.4763114
# 7     3   d -2.3296467    8  0.1370525
# 8     3   d -2.3296467    4 -0.5790046
# 9     3   d -2.3296467    9  0.3267871
# 10    8   d  0.1370525    9  0.3267871
# 11    4   d -0.5790046    8  0.1370525
# 12    4   d -0.5790046    9  0.3267871

Long to wide data

df %>%
  pivot_wider(id, c(category, name, type), 
            values_from = c(date, size),
            names_glue = '{category}_{name}_{type}_{.value}')

---

Related Topics