Arrange and Grouping data in R

Question

I have the following sample data which I have provided here using dput() . It includes values for countries over few years:

structure(list(ï..Country = c("Kyrgyzstan", "North Vietnam", 
    "Slovakia", "Belgian Congo", "Barbados", "Netherlands Antilles", 
    "Bosnia and Herzegovina", "Federated States of Micronesia", "Kuwait", 
    "Russian Federation"), X1949 = c(NA, NA, 1L, 1L, NA, NA, NA, 
    NA, NA, NA), X1950 = c(NA_integer_, NA_integer_, NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
    NA_integer_, NA_integer_), X1951 = c(NA_integer_, NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_), X1952 = c(NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_, NA_integer_), X1953 = c(NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_, NA_integer_), X1954 = c(NA, 
    NA, 1L, NA, NA, NA, NA, NA, NA, NA), X1955 = c(NA, NA, 1L, NA, 
    NA, 1L, NA, NA, NA, NA), X1956 = c(NA, NA, 4L, NA, NA, NA, NA, 
    NA, NA, NA), X1957 = c(NA, NA, 2L, NA, NA, NA, NA, NA, NA, NA
    ), X1958 = c(NA, NA, 3L, NA, NA, NA, NA, NA, NA, NA), X1959 = c(NA, 
    NA, NA, NA, 1L, 1L, NA, NA, NA, NA), X1960 = c(NA, NA, 3L, NA, 
    NA, 1L, NA, NA, NA, NA), X1961 = c(NA, NA, 1L, NA, NA, 2L, NA, 
    NA, NA, NA), X1962 = c(NA, NA, 1L, NA, NA, 1L, NA, NA, 1L, NA
    ), X1963 = c(NA, NA, 1L, NA, NA, 3L, NA, NA, NA, NA), X1964 = c(NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_, NA_integer_), X1965 = c(NA, 
    1L, 1L, NA, NA, 2L, NA, NA, NA, NA), X1966 = c(NA, 1L, NA, NA, 
    NA, 1L, NA, NA, NA, NA), X1967 = c(NA, NA, NA, NA, 9L, NA, NA, 
    NA, NA, NA), X1968 = c(NA, NA, 1L, NA, 1L, 1L, NA, NA, NA, NA
    ), X1969 = c(NA, NA, NA, NA, 1L, 1L, NA, NA, NA, NA), X1970 = c(NA, 
    1L, NA, NA, 8L, 2L, NA, NA, NA, NA), X1971 = c(NA, NA, NA, NA, 
    6L, 2L, NA, NA, NA, NA), X1972 = c(NA, 1L, NA, NA, 3L, 2L, NA, 
    NA, NA, NA), X1973 = c(NA, NA, NA, NA, 14L, 1L, NA, 1L, NA, NA
    ), X1974 = c(NA, 1L, NA, NA, 8L, 1L, NA, NA, NA, NA), X1975 = c(NA, 
    NA, NA, NA, 6L, 1L, NA, NA, NA, NA), X1976 = c(NA, NA, NA, NA, 
    7L, 1L, NA, NA, NA, NA), X1977 = c(NA, NA, NA, NA, 27L, 1L, NA, 
    NA, 2L, NA), X1978 = c(NA, NA, NA, NA, 31L, 2L, NA, NA, 1L, NA
    ), X1979 = c(NA, 1L, NA, NA, 12L, NA, NA, NA, NA, NA), X1980 = c(NA, 
    NA, NA, NA, 12L, 1L, NA, NA, 2L, NA), X1981 = c(NA, 3L, NA, 1L, 
    15L, NA, NA, NA, 1L, NA), X1982 = c(NA, NA, NA, 1L, 21L, 1L, 
    NA, NA, 2L, NA), X1983 = c(NA, NA, NA, NA, 19L, NA, NA, NA, NA, 
    NA), X1984 = c(NA, NA, NA, NA, 14L, NA, NA, NA, 1L, NA), X1985 = c(NA, 
    NA, NA, NA, 19L, NA, NA, NA, NA, NA), X1986 = c(NA, NA, NA, NA, 
    10L, 1L, NA, NA, 1L, NA), X1987 = c(NA, NA, NA, NA, 8L, 2L, NA, 
    NA, NA, NA), X1988 = c(NA, NA, NA, NA, 7L, 1L, NA, NA, NA, NA
    ), X1989 = c(NA, NA, NA, NA, 3L, 2L, NA, NA, 1L, NA), X1990 = c(NA, 
    NA, NA, 1L, 1L, 1L, NA, NA, 1L, NA), X1991 = c(NA, NA, NA, 1L, 
    2L, NA, NA, NA, 1L, NA), X1992 = c(NA, NA, NA, NA, 4L, NA, NA, 
    NA, 2L, NA), X1993 = c(NA, 1L, NA, 2L, 1L, NA, NA, 1L, 8L, NA
    ), X1994 = c(NA, NA, NA, NA, 2L, NA, NA, NA, 9L, NA), X1995 = c(NA, 
    10L, NA, NA, NA, NA, NA, NA, 7L, NA), X1996 = c(NA, 14L, NA, 
    1L, 2L, NA, NA, NA, 10L, NA), X1997 = c(NA, 6L, NA, 1L, 1L, 3L, 
    NA, NA, 28L, NA), X1998 = c(NA, 3L, 1L, NA, 1L, NA, NA, NA, 44L, 
    NA), X1999 = c(2L, 16L, 2L, 3L, 1L, NA, NA, NA, 102L, NA), X2000 = c(NA, 
    23L, 2L, NA, NA, 1L, NA, NA, 55L, NA), X2001 = c(NA, 31L, NA, 
    1L, NA, NA, NA, NA, 92L, NA), X2002 = c(2L, 5L, 1L, NA, NA, NA, 
    NA, NA, 63L, NA), X2003 = c(1L, NA, 9L, NA, 1L, NA, NA, NA, 48L, 
    NA), X2004 = c(7L, NA, 25L, 1L, 1L, 1L, NA, NA, 69L, NA), X2005 = c(7L, 
    NA, 16L, NA, 4L, 1L, NA, NA, 57L, NA), X2006 = c(3L, NA, 12L, 
    1L, 1L, NA, NA, NA, 74L, NA), X2007 = c(3L, NA, 17L, NA, 1L, 
    4L, NA, NA, 51L, NA), X2008 = c(NA, NA, 5L, NA, NA, NA, NA, NA, 
    21L, NA), X2009 = c(1L, NA, 3L, NA, 2L, 4L, NA, NA, 17L, NA), 
        X2010 = c(NA, NA, 3L, NA, 1L, NA, NA, NA, 22L, NA), X2011 = c(6L, 
        NA, 8L, NA, 2L, NA, NA, NA, 20L, NA), X2012 = c(1L, NA, 5L, 
        NA, 3L, 1L, NA, NA, 22L, NA), X2013 = c(2L, NA, 9L, NA, NA, 
        1L, NA, NA, 18L, NA), X2014 = c(4L, NA, 14L, NA, 5L, NA, 
        3L, NA, 11L, 9L), X2015 = c(2L, NA, 17L, NA, 2L, NA, 3L, 
        NA, 16L, 10L), X2016 = c(4L, NA, 19L, NA, 1L, 5L, 2L, 2L, 
        18L, 29L), X2017 = c(4L, NA, 12L, 1L, 6L, NA, 5L, NA, 28L, 
        27L), X2018 = c(1L, NA, 16L, 1L, 2L, NA, 1L, NA, 27L, 34L
        ), X2019 = c(7L, NA, 14L, NA, 4L, NA, 2L, NA, 28L, 36L), 
        X2020 = c(2L, NA, 8L, NA, 2L, NA, 4L, NA, 14L, 43L)), row.names = c(155L, 
    204L, 261L, 27L, 22L, 190L, 36L, 94L, 153L, 244L), class = "data.frame")

The current format as you can see in the above data is something like this :

            1949 1950 1951
Country A    1    2     0
Country B    0    1     3    
Country C    1    0     2

and so on.

I want the data in the following format:

Year Country value 
1949   A       1   
1950   A       2
1951   A       0

Need to arrange and then group by year? Any help appreciated.

`tidyr::pivot_longer(df, -1)` should do this for you – Allan Cameron Sep 02 '22 at 10:55 — Allan Cameron, Sep 02 '22 at 10:55

score 1 · Answer 1 · answered Sep 02 '22 at 10:58

You can pivot to long format with tidyr::pivot_longer

library(tidyverse)

df2 <- pivot_longer(df, -1, names_to = 'Year') %>%
  rename(Country = ï..Country) %>%
  mutate(Year = as.numeric(substr(Year, 2, 5)))

df2
#> # A tibble: 720 x 3
#>    Country     Year value
#>    <chr>      <dbl> <int>
#>  1 Kyrgyzstan  1949    NA
#>  2 Kyrgyzstan  1950    NA
#>  3 Kyrgyzstan  1951    NA
#>  4 Kyrgyzstan  1952    NA
#>  5 Kyrgyzstan  1953    NA
#>  6 Kyrgyzstan  1954    NA
#>  7 Kyrgyzstan  1955    NA
#>  8 Kyrgyzstan  1956    NA
#>  9 Kyrgyzstan  1957    NA
#> 10 Kyrgyzstan  1958    NA
#> # ... with 710 more rows
#> # i Use `print(n = ...)` to see more rows


ggplot(df2, aes(Year, value, color = Country)) + geom_line()

thanks, I wasn't sure how to phrase the question in English to look for possible available answers. — Hank, Sep 02 '22 at 12:14

Arrange and Grouping data in R

1 Answers1