How to turn monadic into dyadic data in R?

Question

NOTE: This is a modified version of How do I turn monadic data into dyadic data in R (country-year into pair-year)?

I have data organized by country-year, with a ID for a dyadic relationship. I want to organize this by dyad-year.

Here is how my data is organized:

   dyadic_id country_codes year
1          1           200 1990
2          1            20 1990
3          1           200 1991
4          1            20 1991
5          1           200 1991
6          1           300 1991
7          1           300 1991
8          1            20 1991
9          2           300 1990
10         2            10 1990
11         3           100 1990
12         3            10 1990
13         4           500 1991
14         4           200 1991

Here is how I want the data to be:

  dyadic_id_want country_codes_1 country_codes_2 year_want
1              1             200              20      1990
2              1             200              20      1991
3              1             200             300      1991
4              1             300              20      1991
5              2             300              10      1990
6              3             100              10      1990
7              4             500             200      1991

Here is reproducible code:

dyadic_id<-c(1,1,1,1,1,1,1,1,2,2,3,3,4,4)
country_codes<-c(200,20,200,20,200,300,300,20,300,10,100,10,500,200)
year<-c(1990,1990,1991,1991,1991,1991,1991,1991,1990,1990,1990,1990,1991,1991)
mydf<-as.data.frame(cbind(dyadic_id,country_codes,year))


dyadic_id_want<-c(1,1,1,1,2,3,4)
country_codes_1<-c(200,200,200,300,300,100,500)
country_codes_2<-c(20,20,300,20,10,10,200)
year_want<-c(1990,1991,1991,1991,1990,1990,1991)
my_df_i_want<-as.data.frame(cbind(dyadic_id_want,country_codes_1,country_codes_2,year_want))

This is a unique problem since there are more than one country that participate in each event (noted by a dyadic_id).

Answer 1

You can actually do it very similar to akrun's solution for dplyr. Unfortunately I'm not well versed enough in data.table to help you with that part, and I'm sure others may have better solution to this one.

Basically for the mutate(ind=...) portion you need to be a little more clever on how you construct this indicator so that it is unique and will lead to the same result that you're looking for. For my solution, I notice that since you have groups of two, then your indicator should just have modulus operator attached to it.

ind=paste0('country_codes', ((row_number()+1) %% 2+1))

Then you need an indentifier for each group of two which again can be constructed using the similar idea.

ind_row = ceiling(row_number()/2)

Then you can proceed as normal in the code.

The full code is as follows:

mydf %>% 
  group_by(dyadic_id, year) %>%
  mutate(ind=paste0('country_codes', ((row_number()+1) %% 2+1)), 
         ind_row = ceiling(row_number()/2)) %>%
  spread(ind, country_codes) %>% 
  select(-ind_row)
#  dyadic_id year country_codes1 country_codes2
#1         1 1990            200             20
#2         1 1991            200             20
#3         1 1991            200            300
#4         1 1991            300             20
#5         2 1990            300             10
#6         3 1990            100             10
#7         4 1991            500            200

All credit to akrun's solution though.