CODEWITHSUNDEEP
rdplyr
Grigorij Abramov
Grigorij Abramov

Reputation: 51

add a new colum that groups the data

I have trouble with the following data (df)

1                                                  TeamA     1
2                                                  TeamB     2
3                                                  TeamC     3
4                                                  TeamA     4
5                                                  TeamB     5
6                                                  TeamC     6
7                                                  TeamA     7
8                                                  TeamB     8
9                                                  TeamD     9
10                                                 TeamD    10

I want to add a column that pastes the results of the Team, so it looks like this. So the new Column look like this. Since my data is not small a for loop will not do it.

1                                                  TeamA     1     1-4-7 
2                                                  TeamB     2     2-5-8
3                                                  TeamC     3     3-6
4                                                  TeamA     4     1-4-7  
5                                                  TeamB     5     2-5-8
6                                                  TeamC     6     3-6
7                                                  TeamA     7     1-4-7 
8                                                  TeamB     8     2-5-8
9                                                  TeamD     9     9-10
10                                                 TeamD    10     9-10

In the original data there is not pattern of the Teams that I can use. I think it must work with the group_by from dplyr but I could not do it.

Upvotes: 0

Views: 60

Answers (2)

G. Grothendieck
G. Grothendieck

Reputation: 269481

Use ave like this:

transform(DF, new = ave(No, Team, FUN = function(x) paste(x, collapse = "-")))

giving:

    Team No   new
1  TeamA  1 1-4-7
2  TeamB  2 2-5-8
3  TeamC  3   3-6
4  TeamA  4 1-4-7
5  TeamB  5 2-5-8
6  TeamC  6   3-6
7  TeamA  7 1-4-7
8  TeamB  8 2-5-8
9  TeamD  9  9-10
10 TeamD 10  9-10

or using dplyr:

library(dplyr)

DF %>% 
   group_by(Team) %>% 
   mutate(new = paste(No, collapse = "-")) %>% 
   ungroup

Note

The input DF in reproducible form is:

Lines <- "
TeamA     1
TeamB     2
TeamC     3
TeamA     4
TeamB     5
TeamC     6
TeamA     7
TeamB     8
TeamD     9
TeamD    10"
DF <- read.table(text = Lines, as.is = TRUE, col.names = c("Team", "No"))

Upvotes: 3

moodymudskipper
moodymudskipper

Reputation: 47300

We can aggregate, then merge to the original data.frame and sort:

df <- read.table(text="1                                                  TeamA     1
                 2                                                  TeamB     2
                 3                                                  TeamC     3
                 4                                                  TeamA     4
                 5                                                  TeamB     5
                 6                                                  TeamC     6
                 7                                                  TeamA     7
                 8                                                  TeamB     8
                 9                                                  TeamD     9
                 10                                                 TeamD    10",h=F,strin=F)

aggregated_scores <- aggregate(V3 ~ V2,df,paste,collapse='-')    
new_df <- merge(df[-3],aggregated_scores)
new_df <- new_df[order(new_df$V1),]

#       V2 V1    V3
# 1  TeamA  1 1-4-7
# 4  TeamB  2 2-5-8
# 8  TeamC  3   3-6
# 3  TeamA  4 1-4-7
# 5  TeamB  5 2-5-8
# 7  TeamC  6   3-6
# 2  TeamA  7 1-4-7
# 6  TeamB  8 2-5-8
# 9  TeamD  9  9-10
# 10 TeamD 10  9-10

Upvotes: 0

Related Questions