combine multiple rows with same field in R

Question

I have a dataset as following

V1 <- c(5,5,5,45,45,77)  
V2 <- c("low", "low", "medium", "low", "low", "high")  
V3 <- c(10,3,6,10,3,1)  
df <- cbind.data.frame(V1,V3,V2)  

  v1    v2    v3   
  5    10     low  
  5    3      low  
  5    6      medium  
  45   10     low  
  45   3      low  
  77   1      high

I want it to be

   v1  low  medium  high  
   5         13    6      0  
  45        13     0     0  
  77         0     0      1

I have tried with cast/melt with little success.

@PierreLafortune, I'm sure there's a duplicate, but we would need one that aggregates as well as reshapes.... — A5C1D2H2I1M1N2O1R2T1, Nov 10 '15 at 18:16
@AnandaMahto the `xtabs` answer in the linked question doesn't do that? — Frank, Nov 10 '15 at 18:18
@Frank, would you know that `xtabs` sums without either trying it out or trying to interpret the help page for `xtabs`? — A5C1D2H2I1M1N2O1R2T1, Nov 10 '15 at 18:20
@AnandaMahto Fair enough. Feel free to undupe. Another variation: `dcast(DF, V1~V2, value.var="V3", fun=sum, fill=0)` — Frank, Nov 10 '15 at 18:21
@Frank That's the answer I was going to post. If we'd like to keep the column order: `dcast(df, V1 ~ factor(V2, levels = unique(V2)), value.var="V3", sum)` — mpalanco, Nov 10 '15 at 18:51
@mpalanco Ok cool. Go ahead; I've just unduped it since Ananda didn't have further input. — Frank, Nov 10 '15 at 18:59

score 2 · Answer 1 · answered Nov 10 '15 at 18:13

Since you are doing a sum + transform to wide, I would suggest using xtabs in base R:

df <- data.frame(V1, V3, V2) ## Keeps numeric data as numeric....
xtabs(V3 ~ V1 + V2, df)
#     V2
# V1   high low medium
#   5     0  13      6
#   45    0  13      0
#   77    1   0      0

Or, if you care about the column order, you can try:

xtabs(V3 ~ V1 + factor(V2, c("low", "medium", "high")), df)

score 1 · Accepted Answer · answered Nov 10 '15 at 19:17

Using rehape2 as Frank answered in the comments:

library(reshape2)    
dcast(df, V1 ~ V2, value.var = "V3", fun = sum, fill = 0)

Output:

  V1 high low medium
1  5    0  13      6
2 45    0  13      0
3 77    1   0      0

If we'd like to keep the column order:

dcast(df, V1  ~ factor(V2, levels = unique(V2)), value.var = "V3", sum)

Output:

  V1 low medium high
1  5  13      6    0
2 45  13      0    0
3 77   0      0    1

score 0 · Answer 3 · answered Nov 10 '15 at 18:12

V1 <- c(5, 5, 5, 45, 45, 77)  
V2 <- c("low", "low", "medium", "low", "low", "high")  
V3 <- c(10, 3, 6, 10, 3, 1)  
df <- data.frame(V1, V2, V3)

df$V2 <- factor(df$V2, levels = c("low", "medium", "high"))

library(tidyr)
library(dplyr)

df %>%
  group_by(V1, V2) %>%
  summarise(sum = sum(V3)) %>%
  spread(V2, sum, fill = 0)

# Source: local data frame [3 x 4]
# 
#      V1   low medium  high
#   (dbl) (dbl)  (dbl) (dbl)
# 1     5    13      6     0
# 2    45    13      0     0
# 3    77     0      0     1

combine multiple rows with same field in R

3 Answers3

Linked