1
I have this dataframe (with dummy values)

Country lifeExp_1952 lifeExp_1962 pop_1952 pop_1962 gdp_1952 gdp_1962
A       1            4            7        10       13       16
B       2            5            8        11       14       17
C       3            6            9        12       15       18

I would like to convert it to a long format, but have separate columns for lifeExp, pop and gdp such that it looks like this:

Country Year lifeExp pop gdp
A       1952 1       7   13
A       1962 4       10  16
B       1952 2       8   14
B       1962 5       11  17
C       1952 3       9   15 
C       1962 6       12  18

So far, I have been able to extract the year with lifeExp, pop and gdp in the same column using reshape2, but I have no idea how to give them their own column.

aishya3000
  • 55
  • 4

1 Answers1

2

We can use pivot_longer

library(dplyr)
library(tidyr)
df1 %>%
   pivot_longer(cols = -Country, names_to = c(".value", 'Year'), names_sep = "_")

-output

# A tibble: 6 x 5
#  Country Year  lifeExp   pop   gdp
#  <chr>   <chr>   <int> <int> <int>
#1 A       1952        1     7    13
#2 A       1962        4    10    16
#3 B       1952        2     8    14
#4 B       1962        5    11    17
#5 C       1952        3     9    15
#6 C       1962        6    12    18

data

df1 <- structure(list(Country = c("A", "B", "C"), lifeExp_1952 = 1:3, 
    lifeExp_1962 = 4:6, pop_1952 = 7:9, pop_1962 = 10:12, gdp_1952 = 13:15, 
    gdp_1962 = 16:18), class = "data.frame", row.names = c(NA, 
-3L))
akrun
  • 874,273
  • 37
  • 540
  • 662
  • Thank you! When I do this, I get the error message: can't combine `lifeExp_1952` and `country.1` . Is there any way to solve this? – aishya3000 Feb 07 '21 at 19:18
  • @aishya3000 is it based on the same `data` I used in my post try `df1 <- type.convert(df1, as.is = TRUE)` and then apply on that data – akrun Feb 07 '21 at 19:20
  • @aishya3000 from your data showed, the column name is `Country` and not `country.1` – akrun Feb 07 '21 at 19:22