This question is a spin-off from my previous post.
I have a big data frame (900k rows) about mergers and acquisitions (M&As).
The df has four columns: date (when the M&A was completed), target_nation (a company of which country was merged/acquired), acquiror_nation (corporation of which country was the acquiror), and big_corp (whether the acquiror was a big corporation or not, where TRUE means that corporation is big).
Here is a sample of my df:
df <- structure(list(date = c(2000L, 2000L, 2001L, 2001L, 2001L, 2003L,
2003L, 1999L, 2001L, 2002L, 2002L, 2002L), target_nation = c("Uganda",
"Uganda", "Uganda", "Uganda", "Uganda", "Uganda", "Mozambique",
"Mozambique", "Mozambique", "Mozambique", "Mozambique", "Mozambique"
), acquiror_nation = c("France", "Germany", "France", "France",
"Germany", "Germany", "Germany", "Germany", "France", "France",
"Germany", "Japan"), big_corp_TF = c(TRUE, FALSE, TRUE, FALSE,
FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, TRUE, TRUE)), row.names = c(NA,
-12L), class = c("data.table", "data.frame"))
> df
date target_nation acquiror_nation big_corp_TF
1: 2000 Uganda France TRUE
2: 2000 Uganda Germany FALSE
3: 2001 Uganda France TRUE
4: 2001 Uganda France FALSE
5: 2001 Uganda Germany FALSE
6: 2003 Uganda Germany TRUE
7: 2003 Mozambique Germany FALSE
8: 1999 Mozambique Germany FALSE
9: 2001 Mozambique France TRUE
10: 2002 Mozambique France FALSE
11: 2002 Mozambique Germany TRUE
12: 2002 Mozambique Japan TRUE
From these data, I want to create a new column that denotes the share of M&As done in specific target nations by big corporations of specific acquiror nations, counting the average for 2 years. (For my actual exercise, I will count the averages for 5 years, but let's keep things simpler here).
There is a group of acquiror nations that I am particularly interested in (for this example, let it be France, Germany, and Japan). I want there to be a column that would denote the abovementioned share for these countries.
@AnilGoyal previously helped me with a code. Here is the code:
df_calc <- df %>%
mutate(d = 1) %>%
group_by(target_nation) %>%
complete(date = seq(min(date), max(date), 1), nesting(acquiror_nation),
fill = list(d = 0, big_corp_TF = FALSE)) %>%
group_by(date, target_nation) %>%
mutate(total_MAs = sum(d)) %>%
group_by(date, target_nation, acquiror_nation) %>%
summarise(total_MAs = mean(total_MAs),
total_MAs_bigcorp = sum(big_corp_TF), .groups = 'drop') %>%
group_by(target_nation, acquiror_nation) %>%
mutate(share = sum_run(total_MAs_bigcorp, k=2)/sum_run(total_MAs, k=2))
This is the output:
date targ_nat acq_nat tot_MA big_MA share
1 1999 Mozambique France 1 0 0.0000000
2 1999 Mozambique Germany 1 0 0.0000000
3 1999 Mozambique Japan 1 0 0.0000000
4 2000 Mozambique France 0 0 0.0000000
5 2000 Mozambique Germany 0 0 0.0000000
6 2000 Mozambique Japan 0 0 0.0000000
7 2001 Mozambique France 1 1 1.0000000
8 2001 Mozambique Germany 1 0 0.0000000
9 2001 Mozambique Japan 1 0 0.0000000
10 2002 Mozambique France 3 0 0.2500000
11 2002 Mozambique Germany 3 1 0.2500000
12 2002 Mozambique Japan 3 1 0.2500000
13 2003 Mozambique France 1 0 0.0000000
14 2003 Mozambique Germany 1 0 0.2500000
15 2003 Mozambique Japan 1 0 0.2500000
16 2000 Uganda France 2 1 0.5000000
17 2000 Uganda Germany 2 0 0.0000000
18 2001 Uganda France 3 1 0.4000000
19 2001 Uganda Germany 3 0 0.0000000
20 2002 Uganda France 0 0 0.3333333
21 2002 Uganda Germany 0 0 0.0000000
22 2003 Uganda France 1 0 0.0000000
23 2003 Uganda Germany 1 1 1.0000000
All the numbers are as desired. However, I would like there to be the results for Japan's investments in Uganda but cannot succeed in achieving that. How can I achieve this? I understand that the reason for there being no results for Japan in Uganda is that Japan has not done any investment in Uganda in any year (as shown in the data sample above); but this lack of investment is a meaningful result for me, and I would like there to be rows for Japan as acquiror nation as well. Just like so (I exclude Mozambique as targ_nat for space reasons):
date targ_nat acq_nat tot_MA big_MA share
16 2000 Uganda France 2 1 0.5000000
17 2000 Uganda Germany 2 0 0.0000000
18 2000 Uganda Japan 2 0 0.0000000
19 2001 Uganda France 3 1 0.4000000
20 2001 Uganda Germany 3 0 0.0000000
21 2001 Uganda Japan 3 0 0.0000000
22 2002 Uganda France 0 0 0.3333333
22 2002 Uganda Germany 0 0 0.0000000
23 2002 Uganda Japan 0 0 0.0000000
24 2003 Uganda France 1 0 0.0000000
25 2003 Uganda Germany 1 1 1.0000000
26 2003 Uganda Japan 1 0 0.0000000
Any ideas on how to achieve that? For my actual purposes, I have a group of 13 countries for which I want to see the results as acquiror nations (so not just France, Germany, and Japan). These countries appear in the data set as acquiror nations (but not for all target_nations (!) --- just like in the example of Uganda and Japan here).
Any help much appreciated.