Transform Data Frame for Panel Regression

Question

My Data Frame looks like this (only first 6 columns visible):

There is one "Year" Column and for every Country two values which have a prefix "R_" and "Ineq_" respectively. For clarification: Every country has two values with the names "R_'Country Name'" and "Infl_'Country Name'".

I want to reshape my Data frame for a Panel regression like this:

Country Year R Infl
Canada  1971 x1 x2
Canada  1972 x1 x2
Canada  1973 x1 x2
...
Columbia 1971 x1 x2
Columbia 1972 x1 x2
Columbia 1973 x1 x2
...
USA 1971 x1 x2
...

How can I do it? I found this post Data Transformation in R for Panel Regression but it did not help me.

I would appreciate any help, thanks!

Answer 1

You can use pivot_longer :

df1 <- tidyr::pivot_longer(df, 
                           cols = -Year, 
                           names_to = c('.value', 'Country'), 
                           names_sep = '_')
df1

# A tibble: 10 x 4
#    Year Country       R   Infl
#   <int> <chr>     <dbl>  <dbl>
# 1  1971 Canada  -0.560   1.22 
# 2  1971 USA      1.72    1.79 
# 3  1972 Canada  -0.230   0.360
# 4  1972 USA      0.461   0.498
# 5  1973 Canada   1.56    0.401
# 6  1973 USA     -1.27   -1.97 
# 7  1974 Canada   0.0705  0.111
# 8  1974 USA     -0.687   0.701
# 9  1975 Canada   0.129  -0.556
#10  1975 USA     -0.446  -0.473

data

set.seed(123)
df <- data.frame(Year = 1971:1975, R_Canada = rnorm(5),R_USA = rnorm(5), 
           Infl_Canada = rnorm(5), Infl_USA = rnorm(5))