CODEWITHSUNDEEP
rdplyr
fanbondi
fanbondi

Reputation: 1047

Dataframe add new rows from existing ones

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.

Upvotes: 3

Views: 75

Answers (3)

Darren Tsai
Darren Tsai

Reputation: 35624

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

Upvotes: 1

G5W
G5W

Reputation: 37661

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

Upvotes: 3

akrun
akrun

Reputation: 887911

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

Upvotes: 4

Related Questions