CODEWITHSUNDEEP
rdataframemelt
LoveMYMAth
LoveMYMAth

Reputation: 111

Changing a portion of the table from wide to long

I apologize ahead of time if this has been answered. I just can not find anything for this specific situation.

Anyways, I have a table that looks like so:

 Country       code  Year_1960 Year_1961 Year_1962 Year_1963 Year_1964
  <chr>         <chr>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>
1 Afghanistan   AFG     8996351   9166764   9345868   9533954   9731361
2 Albania       ALB     1608800   1659800   1711319   1762621   1814135
3 Algeria       DZA    11124888  11404859  11690153  11985136  12295970
4 American Sam~ ASM       20013     20486     21117     21882     22698
5 Andorra       AND       13411     14375     15370     16412     17469
6 Angola        AGO     5643182   5753024   5866061   5980417   6093321

Notice how each distinct year is a header. I want to make this table into a long format. The Country and code features are already in that format. How do I go about doing this?

I have tried melt(), reshape(), etc. I am struggling. Thank you ahead of time for helping.

Upvotes: 0

Views: 20

Answers (1)

Anoushiravan R
Anoushiravan R

Reputation: 21938

I hope this is what you are looking for. You could also set names_prefix = "Year_" as an alternative to names_pattern as specified by dear Martin in the comments.

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(!c(Country, code), 
               names_to = 'Year', 
               values_to = 'Value',
               names_pattern = "\\w+_(\\d+)")

# A tibble: 30 x 4
   Country     code  Year    Value
   <chr>       <chr> <chr>   <int>
 1 Afghanistan AFG   1960  8996351
 2 Afghanistan AFG   1961  9166764
 3 Afghanistan AFG   1962  9345868
 4 Afghanistan AFG   1963  9533954
 5 Afghanistan AFG   1964  9731361
 6 Albania     ALB   1960  1608800
 7 Albania     ALB   1961  1659800
 8 Albania     ALB   1962  1711319
 9 Albania     ALB   1963  1762621
10 Albania     ALB   1964  1814135
# ... with 20 more rows

Upvotes: 1

Related Questions