CODEWITHSUNDEEP
r
Nic
Nic

Reputation: 79

Adding new column in dataframe counting rows from another dataframe

Have searched on the forums for a solution but couldn't find one.

I have data on companies' financials in one dataframe (df1) and data on acquisitions made in another dataframe (df2). The data is in the same format as below.

df1 <- data.frame(ID=c('111111','111111', '222222', '333333', '444444'),
              year=c(2010, 2011, 2010, 2011, 2011))
df2 <- data.frame(ID=c('111111', '111111', '111111', '111111', '333333'),
              year=c(2010,2010,2010,2011,2011))´

My goal is to create a new column in df1 with values that count each observation in df2 that matches both the ID and the year of the row in df1. I need a variable that counts the number of acquisitions made by each company each year. Below is the desired output. 

#output should look like following in df1
# ID      year  count of observations in df2 per year
# 111111  2010  3
# 111111  2011  1
# 222222  2010  0
# 333333  2011  1
# 444444  2011  0

I have really tried to come up with a solution but haven't got close enough. Hope that somebody would have a solution for this problem.

Thank you in advance!

Upvotes: 1

Views: 251

Answers (2)

amrrs
amrrs

Reputation: 6325

A non-tidyverse solution. I understand this seems more complex than the tidyverse one, just shared it for variety of options.

df1 <- data.frame(ID=c('111111','111111', '222222', '333333', '444444'),
                  year=c(2010, 2011, 2010, 2011, 2011))

df2 <- data.frame(ID=c('111111', '111111', '111111', '111111', '333333'),
                  year=c(2010,2010,2010,2011,2011))


df1$key <- paste(df1$ID,df1$year,sep = "_")

df2$key <- paste(df2$ID,df2$year,sep = "_")


df1$count_of_year <- unlist(lapply(df1$key,function(x) {sum(df2$key %in% x)}))

df1 <- df1[,c(1,2,4)]

df1
#>       ID year count_of_year
#> 1 111111 2010             3
#> 2 111111 2011             1
#> 3 222222 2010             0
#> 4 333333 2011             1
#> 5 444444 2011             0

Created on 2019-01-29 by the reprex package (v0.2.1)

Upvotes: 0

Tito Sanz
Tito Sanz

Reputation: 1362

Probably the best way is to use left_join, you only need to change NA by 0:

df1 <- data.frame(ID=c('111111','111111', '222222', '333333', '444444'),
                  year=c(2010, 2011, 2010, 2011, 2011))
df2 <- data.frame(ID=c('111111', '111111', '111111', '111111', '333333'),
                  year=c(2010,2010,2010,2011,2011))

library(tidyverse)

df2 %>% count(ID, year) -> summ_df2

df1 %>% left_join(summ_df2)
#> Joining, by = c("ID", "year")
#> Warning: Column `ID` joining factors with different levels, coercing to
#> character vector
#>       ID year  n
#> 1 111111 2010  3
#> 2 111111 2011  1
#> 3 222222 2010 NA
#> 4 333333 2011  1
#> 5 444444 2011 NA

Created on 2019-01-29 by the reprex package (v0.2.1)

One chain operation Added following comment by @Ronak Shah

df1 <- data.frame(ID=c('111111','111111', '222222', '333333', '444444'),
                  year=c(2010, 2011, 2010, 2011, 2011))
df2 <- data.frame(ID=c('111111', '111111', '111111', '111111', '333333'),
                  year=c(2010,2010,2010,2011,2011))

library(tidyverse)

df2 %>% 
 count(ID, year) %>% 
 right_join(df1) %>% 
 replace_na(list(n = 0))

#> Joining, by = c("ID", "year")
#> Warning: Column `ID` joining factors with different levels, coercing to
#> character vector
#> # A tibble: 5 x 3
#>   ID      year     n
#>   <chr>  <dbl> <dbl>
#> 1 111111  2010     3
#> 2 111111  2011     1
#> 3 222222  2010     0
#> 4 333333  2011     1
#> 5 444444  2011     0

Created on 2019-01-29 by the reprex package (v0.2.1)

Upvotes: 3

Related Questions