CODEWITHSUNDEEP
r
beavis11111
beavis11111

Reputation: 574

R: Transfer one column into two column header and average the correct grouped data

Below is the input:

X1 = c("aaa", "aaa", "aaa", "qqq", "qqq", "qqq")
X2 = c("bbb", "bbb", "bbb", "rrr", "rrr", "rrr")
X3 = c("ccc", "ccc", "ccc", "ttt", "ttt", "ttt")
X4 = c("usa", "can", "usa", "ger", "rus", "ger")
X5 = c(400, 888, 500, 300, 456, 500)
df <- data.frame(X1,X2,X3,X4,X5)

I would like my output i and j with average of the same country:

X1   X2   X3    i    j
aaa, bbb, ccc, 450, 888
qqq, rrr, ttt, 400, 456

I have tried double-aggregate and grouping and hoping to avoid for loop but still can't manage it.

Upvotes: 0

Views: 55

Answers (2)

d.b
d.b

Reputation: 32548

df$averages = ave(df[,"X5"], df[c("X1", "X2", "X3", "X4")], FUN = mean)
aggregate(averages~., df[c("averages", "X1", "X2", "X3")], range)
#   X1  X2  X3 averages.1 averages.2
#1 aaa bbb ccc        450        888
#2 qqq rrr ttt        400        456

Upvotes: 1

Matt W.
Matt W.

Reputation: 3722

Not sure I understand why you want your output like you do. I would group by country..

library(dplyr)

> df %>% group_by(X4, X1, X2, X3) %>% summarise(i = sum(X5))
# A tibble: 4 x 5
# Groups:   X4, X1, X2 [?]
      X4     X1     X2     X3     i
  <fctr> <fctr> <fctr> <fctr> <dbl>
1    can    aaa    bbb    ccc   888
2    ger    qqq    rrr    ttt   800
3    rus    qqq    rrr    ttt   456
4    usa    aaa    bbb    ccc   900

Upvotes: 1

Related Questions