Search code examples
rdatasetexpand

Expand the data set to include all combinations of the values of the variables using R

I have the following snippet of my data set that consists of thousands of observations

raw_data

and I want to expand this data set using R so as to include all combiations of the first two columns

that is, I want this output.

output

I tried using the melt or the pivot_longer but with no success. Is there any alternative R way to do that?

Thank you in advance!

I tried the melt or the pivot function but without success.

Solution

  • An approach using merge on the expanded unique values

    merge(df, expand.grid(countries = unique(df$countries), 
                      year = unique(df$year)), all=T)
  countries year values
1        UK 2004    1.2
2        UK 2005     NA
3        UK 2006     NA
4        UK 2007     NA
5       USA 2004     NA
6       USA 2005    4.5
7       USA 2006    5.2
8       USA 2007    3.5
    Data
    df <- structure(list(countries = c("UK", "USA", "USA", "USA"), year = 
2004:2007, values = c(1.2, 4.5, 5.2, 3.5)), class = "data.frame", row.names =
c(NA, -4L))