0

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.

Konrad Rudolph
  • 530,221
  • 131
  • 937
  • 1,214
LoveMYMAth
  • 111
  • 5

1 Answers1

1

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
Anoushiravan R
  • 21,622
  • 3
  • 18
  • 41
  • 1
    As an alternative to `names_pattern` you could use in this case `names_prefix = "Year_"`. – Martin Gal Apr 03 '22 at 20:46
  • 1
    Really nice Martin, I didn't know it would drop it. – Anoushiravan R Apr 03 '22 at 20:48
  • Can I ask what "\\w+_(\\d+)" is and where it came from? – LoveMYMAth Apr 03 '22 at 21:29
  • 1
    @AlexanderMartinez It is a language called regex or regular expressions which is used for string manipulation. Since your names are like `Year_1960` I used a pattern that matches first a one or more characters with `\\w+` plus and underscore `_` followed by a set of numbers with `\\d+`. In fact regex defines a number of pattern that each matches a particular character string like numbers, alphabets, alphanumerics etc. But I put the captured numbers in parenthesis to emphasize that I only want them and not the word Year. – Anoushiravan R Apr 03 '22 at 23:20
  • You can also learn more about them here: https://stringr.tidyverse.org/articles/regular-expressions.html – Anoushiravan R Apr 03 '22 at 23:21