Adding values from 2 different dataframes to make a new one?

Question

Sorry, couldn't think of a better way to phrase the question.

Basically, how would I go about merging something similar to the following dataframes

2014:

Team  Goals.Scored   Goals.Conceded
MUFC      2              4
MCFC      3              0
LFC       1              6

2015:

Team  Goals.Scored   Goals.Conceded
MUFC    4                   3
MCFC    3                   2
AVFC    1                   5

into something like this, that would automatically add the values for matching Teams

Overall

Team  Goals.Scored  Goals.Conceded
MUFC      6             7
MCFC      6             2
LFC       1             6
AVFC      1             5

I was originally using a simple command along the lines of

new.df = 2014$Goals.Scored + 2015$Goals.Scored

but I think that only works if the teams are in the same order in both data frames, and if they're the same teams across the years? Was thinking of doing a for loop but have been unsuccessful thus far.

Would appreciate any help, thanks.

akrun · Accepted Answer · 2015-10-17 18:12:07Z

4

If the two datasets are 'df1', 'df2', we place it in a list, rbind it with rbindlist from data.table, grouped by 'Team', we get the sum of the Subset of Data.table (.SD)

library(data.table)
rbindlist(list(df1, df2))[,lapply(.SD, sum) ,Team]
#   Team Goals.Scored Goals.Conceded
#1: MUFC            6              7
#2: MCFC            6              2
#3:  LFC            1              6
#4: AVFC            1              5

Or using dplyr, we rbind the dataset (bind_rows), group by 'Team', and get the sum of the columns using summarise_each.

library(dplyr)
bind_rows(df1, df2) %>%
           group_by(Team) %>% 
           summarise_each(funs(sum))

Or a bit more compact code will be aggregate from base R (would be slower compared to the other methods described above for big datasets). We can use the formula with . suggesting all the other variables on the lhs of ~.

aggregate(.~Team, rbind(df1, df2), sum)
#  Team Goals.Scored Goals.Conceded
#1 AVFC            1              5
#2  LFC            1              6
#3 MCFC            6              2
#4 MUFC            6              7

It can be otherwise written as aggregate(cbind(Goals.Scored, Gaols.Conceded)~Team,....)

edited Oct 17, 2015 at 18:12

answered Oct 11, 2015 at 17:03

akrun

891k38 gold badges590 silver badges700 bronze badges

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

5 Comments

user3424575 Over a year ago

Wow, thank you so much! Had no idea about the data.table package, than you!

Saksham Over a year ago

can you add a bit more light on what the first argument of aggregate means

akrun Over a year ago

@Saksham Added some more info.

Saksham Over a year ago

@akrun i have always seen examples of aggregate in a different way. can you point me to any article where extensive usage is done in the manner you have used it? I would be grateful.

akrun Over a year ago

@Saksham I don't have any link at the moment. Hopefully, you can get something from google.

Collectives™ on Stack Overflow

Adding values from 2 different dataframes to make a new one?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related