Dataframe add new rows from existing ones

Question

My current data:

Type             Country        Score
University       Australia       10
University       Brazil          10
University       Hong Kong       10
College          Australia       10
College          Brazil          10
College          Hong Kong       10

Now, I want to have a summary of new rows created from the above data as shown below. The new rows are a sum of the scores for each university and college in a country.

Type             Country         Score
University       Australia       10
University       Brazil          10
University       Hong Kong       10
College          Australia       10
College          Brazil          10
College          Hong Kong       10
All              Australia       20
All              Brazil          20
All              Honk Kong       20

I know I can write a loop to iterate over the data and check the countries but maybe I can use packages like dplyr to achieve what I want.

Based on the description, may be you need df1 %>% group_by(Col1, Col2) %>% summarise(col3 = sum(col3)) %>% ungroup %>% mutate(Col1 = 'All') %>% bind_rows(df1, .) — akrun
– akrun, Commented Feb 9, 2019 at 12:40
Hi, please see the edit. The original question was not very clear. — fanbondi
– fanbondi, Commented Feb 9, 2019 at 12:41

akrun · Accepted Answer · 2019-02-09 12:45:45Z

4

We need to group_by 'Country' get the sum of 'Score', create a new column 'Type' with "All" and bind the rows with original data

library(dplyr)
df1  %>% 
  group_by(Country) %>%
  summarise(Score = sum(Score)) %>%
  mutate(Type = "All") %>% 
  bind_rows(df1, .)
#        Type   Country Score
#1 University Australia    10
#2 University    Brazil    10
#3 University Hong Kong    10
#4    College Australia    10
#5    College    Brazil    10
#6    College Hong Kong    10
#7        All Australia    20
#8        All    Brazil    20
#9        All Hong Kong    20

answered Feb 9, 2019 at 12:45

akrun

891k38 gold badges590 silver badges700 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fanbondi Over a year ago

your solution works, but did you have any warning like "binding factor and character vector, coercing into character vector"

Darren Tsai Over a year ago

@fanbondi the warning is just to remind you that bind_rows() will convert the combined variable to character automatically when binding factor and character ones. It doesn't matter in this case.

G5W · Accepted Answer · 2019-02-09 12:50:24Z

3

Or in base R you can do it with aggregate

Extra = cbind("All", aggregate(df$Score, list(df$Country), sum))
names(Extra) = names(df)
rbind(df, Extra)
        Type   Country Score
1 University Australia    10
2 University    Brazil    10
3 University Hong Kong    10
4    College Australia    10
5    College    Brazil    10
6    College Hong Kong    10
7        All Australia    20
8        All    Brazil    20
9        All Hong Kong    20

answered Feb 9, 2019 at 12:50

G5W

37.8k10 gold badges57 silver badges85 bronze badges

Comments

Darren Tsai · Accepted Answer · 2019-02-09 13:02:52Z

1

Another method with ave():

df2 <- within(df1, {Type <- "All" ; Score <- ave(Score, Country, FUN = sum)})
rbind(df1, unique(df2))

#         Type   Country Score
# 1 University Australia    10
# 2 University    Brazil    10
# 3 University Hong Kong    10
# 4    College Australia    10
# 5    College    Brazil    10
# 6    College Hong Kong    10
# 7        All Australia    20
# 8        All    Brazil    20
# 9        All Hong Kong    20

answered Feb 9, 2019 at 13:02

Darren Tsai

36.6k6 gold badges27 silver badges58 bronze badges

Collectives™ on Stack Overflow

Dataframe add new rows from existing ones

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related