Categories
dataframe r r-faq reshape

Reshaping data.frame from wide to long format

232

I have some trouble to convert my data.frame from a wide table to a long table.
At the moment it looks like this:

Code Country        1950    1951    1952    1953    1954
AFG  Afghanistan    20,249  21,352  22,532  23,557  24,555
ALB  Albania        8,097   8,986   10,058  11,123  12,246

Now I would like to transform this data.frame into a long data.frame.
Something like this:

Code Country        Year    Value
AFG  Afghanistan    1950    20,249
AFG  Afghanistan    1951    21,352
AFG  Afghanistan    1952    22,532
AFG  Afghanistan    1953    23,557
AFG  Afghanistan    1954    24,555
ALB  Albania        1950    8,097
ALB  Albania        1951    8,986
ALB  Albania        1952    10,058
ALB  Albania        1953    11,123
ALB  Albania        1954    12,246

I have looked at and already tried using the melt() and the reshape() functions
as some people were suggesting in similar questions.
However, so far I only get messy results.

If it is possible I would like to do it with the reshape() function since
it looks a little bit nicer to handle.

4

  • 3

    Don’t know if that was the problem, but the functions in the reshape package are melt and cast (and recast.)

    Feb 2, 2010 at 17:51

  • 3

    And the reshape package has been superseded by reshape2.

    – IRTFM

    Sep 16, 2014 at 0:10

  • 7

    And now reshape2 has been superseded by tidyr.

    – drhagen

    Feb 15, 2016 at 13:37

  • 3

    And now tidyr‘s gather and spread have been replaced by pivot_* functions.

    – NelsonGon

    Jan 12 at 21:26

129

reshape() takes a while to get used to, just as melt/cast. Here is a solution with reshape, assuming your data frame is called d:

reshape(d, 
        direction = "long",
        varying = list(names(d)[3:7]),
        v.names = "Value",
        idvar = c("Code", "Country"),
        timevar = "Year",
        times = 1950:1954)

0

    129

    reshape() takes a while to get used to, just as melt/cast. Here is a solution with reshape, assuming your data frame is called d:

    reshape(d, 
            direction = "long",
            varying = list(names(d)[3:7]),
            v.names = "Value",
            idvar = c("Code", "Country"),
            timevar = "Year",
            times = 1950:1954)
    

    0

      46

      With tidyr_1.0.0, another option is pivot_longer

      library(tidyr)
      pivot_longer(df1, -c(Code, Country), values_to = "Value", names_to = "Year")
      # A tibble: 10 x 4
      #   Code  Country     Year  Value 
      #   <fct> <fct>       <chr> <fct> 
      # 1 AFG   Afghanistan 1950  20,249
      # 2 AFG   Afghanistan 1951  21,352
      # 3 AFG   Afghanistan 1952  22,532
      # 4 AFG   Afghanistan 1953  23,557
      # 5 AFG   Afghanistan 1954  24,555
      # 6 ALB   Albania     1950  8,097 
      # 7 ALB   Albania     1951  8,986 
      # 8 ALB   Albania     1952  10,058
      # 9 ALB   Albania     1953  11,123
      #10 ALB   Albania     1954  12,246
      

      data

      df1 <- structure(list(Code = structure(1:2, .Label = c("AFG", "ALB"), class = "factor"), 
          Country = structure(1:2, .Label = c("Afghanistan", "Albania"
          ), class = "factor"), `1950` = structure(1:2, .Label = c("20,249", 
          "8,097"), class = "factor"), `1951` = structure(1:2, .Label = c("21,352", 
          "8,986"), class = "factor"), `1952` = structure(2:1, .Label = c("10,058", 
          "22,532"), class = "factor"), `1953` = structure(2:1, .Label = c("11,123", 
          "23,557"), class = "factor"), `1954` = structure(2:1, .Label = c("12,246", 
          "24,555"), class = "factor")), class = "data.frame", row.names = c(NA, 
      -2L))
      

      2

      • 10

        This needs more upvotes. According to the Tidyverse Blog gather is being retired and pivot_longer is now the correct way to accomplish this.

        Apr 22, 2020 at 7:45


      • 6

        @EvanRosica only until they decide to change the function again :p

        – andschar

        Jun 14, 2021 at 13:53