Transform just one row to column

Hanz · December 2, 2019, 1:06pm

Hello, i've a problem with my data. Trying to work with timeseries and when i import my data it's like this
Geo/time 2000 2001 2002 ....
Belgique x1 x2 x3
Bulgarie xa1 xa2 xa3
Tchéquie xb1 xb2 xb3
...
And i need :
i t
Belgique 2000 x1
Belgique 2001 x2
Belgique 2002 x3
Bulgarie 2000 xa1
Bulgarie 2001 xa2
...

How can i do ? I'm trying with t() but it's not working

Thanks for helping a begginer ^^

valeri · December 2, 2019, 1:19pm

Hi @Hanz, could you please provide some data in format that is easy to copy/paste - you can see how to do this here: FAQ: What's a reproducible example (reprex) and how do I do one?

In the meantime, what you might need is the pivot_longer function from the tidyr package.

Maybe this example can clarify further:

library(tidyverse)
set.seed(1)
data <- tibble(country = c('A', 'B', 'C'), `2000` = rnorm(3), `2001` = rnorm(3), `2002` = rnorm(3))
pivot_longer(data, cols = -country, names_to = 'Year', values_to = 'some_metric')
#> # A tibble: 9 x 3
#>   country Year  some_metric
#>   <chr>   <chr>       <dbl>
#> 1 A       2000       -0.626
#> 2 A       2001        1.60 
#> 3 A       2002        0.487
#> 4 B       2000        0.184
#> 5 B       2001        0.330
#> 6 B       2002        0.738
#> 7 C       2000       -0.836
#> 8 C       2001       -0.820
#> 9 C       2002        0.576

Created on 2019-12-02 by the reprex package (v0.3.0)

Hanz · December 2, 2019, 1:58pm

I used dput to reproduce my data :

structure(list(`geo\time` = c("UE (28 pays)", "Zone euro (19 pays)", 
"Zone euro (18 pays)", "Belgique", "Bulgarie", "Tchéquie", "Danemark", 
"Allemagne", "Estonie", "Irlande", "Grèce", "Espagne", "France", 
"France (métropolitaine)", "Croatie", "Italie", "Chypre", "Lettonie", 
"Lituanie", "Luxembourg", "Hongrie", "Malte", "Pays-Bas", "Autriche", 
"Pologne", "Portugal", "Roumanie", "Slovénie", "Slovaquie", "Finlande", 
"Suède", "Royaume-Uni", "Islande", "Liechtenstein", "Norvège", 
"Suisse", "Monténégro", "Macédoine du Nord", "Albanie", "Serbie", 
"Turquie"), `2000` = c(":", "21.399999999999999", "21.600000000000001", 
"19.5", "9.5", "18.300000000000001", "10", "15.699999999999999", 
"8", "25.199999999999999", "33.299999999999997", "32.399999999999999", 
":", "15.5", ":", "30.600000000000001", "27", "9.0999999999999996", 
"3.3999999999999999", "27.399999999999999", "15", "48.399999999999999", 
"20.199999999999999", "18.300000000000001", "13.800000000000001", 
"17.199999999999999", "12.4", "9.5999999999999996", "12.800000000000001", 
"6.7000000000000002", "4.4000000000000004", "14.6", ":", ":", 
"8.4000000000000004", ":", ":", ":", ":", ":", ":"), ...3 = c(NA, 
NA, NA, NA, NA, NA, NA, "(e)", "(b)", NA, NA, NA, NA, "(e)", 
NA, NA, "(e)", "(e)", "(e)", "(e)", NA, "(e)", NA, NA, "(b)", 
NA, NA, NA, NA, "(b)", "(e)", NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA), `2001` = c("18.100000000000001", "20.600000000000001", 
"20.699999999999999", "19.199999999999999", "6.7999999999999998", 
"18.100000000000001", "9.0999999999999996", "14.699999999999999", 
"8", "23.899999999999999", "33", "31.5", ":", "15.1", ":", "29.600000000000001", 
"23.899999999999999", "8.3000000000000007", "3.2999999999999998", 
"26.300000000000001", "14.800000000000001", "48.799999999999997", 
"19.199999999999999", "17.199999999999999", "13", "16.699999999999999", 
"12.300000000000001", "10.5", "12.199999999999999", "6.2000000000000002", 
"4.5", "14.300000000000001", ":", ":", "7.7999999999999998", 
":", ":", ":", ":", ":", ":"), ...5 = c(NA, NA, NA, "(b)", "(b)", 
NA, NA, "(e)", NA, NA, "(b)", "(b)", NA, "(e)", NA, NA, "(e)", 
"(e)", "(e)", "(e)", "(b)", "(e)", NA, NA, "(b)", NA, NA, NA, 
NA, NA, "(b)", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), `2002` = c("17.300000000000001", 
"19.5", "19.699999999999999", "18.199999999999999", "7.0999999999999996", 
"18.600000000000001", "9.1999999999999993", "13.699999999999999", 
"7.7000000000000002", "22.100000000000001", "31.600000000000001", 
"29.5", ":", "13.6", ":", "29.100000000000001", "21.199999999999999", 
"8.9000000000000004", "6.9000000000000004", "25.300000000000001", 
"14.699999999999999", "46.600000000000001", "17.800000000000001", 
"15.5", "12.199999999999999", "16.199999999999999", "13.300000000000001", 
"10.300000000000001", "13", "4.4000000000000004", "3.7000000000000002", 
"14.1", ":", ":", "7.2999999999999998", ":", ":", ":", ":", ":", 
":"), ...7 = c(NA, NA, NA, NA, NA, NA, NA, "(e)", NA, NA, NA, 
NA, NA, "(e)", NA, NA, "(e)", "(b)", "(b)", "(e)", NA, NA, NA, 
NA, NA, NA, "(b)", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA), `2003` = c("16.699999999999999", "18.699999999999999", 
"18.800000000000001", "16.899999999999999", "8.1999999999999993", 
"18.699999999999999", "9.8000000000000007", "12.800000000000001", 
"7.5", "21.699999999999999", "31.100000000000001", "28.100000000000001", 
":", "12.6", "15", "29", "20.199999999999999", "9.8000000000000007", 
"6.9000000000000004", "24", "14.1", "45.700000000000003", "16.5", 
"15.1", "11.9", "14.5", "13.5", "10.4", "13", "4.4000000000000004", 
"3.7999999999999998", "14.199999999999999", "7.0999999999999996", 
":", "6.7999999999999998", ":", ":", ":", ":", ":", ":"), ...9 = c(NA, 
NA, NA, NA, NA, NA, NA, "(e)", NA, NA, NA, NA, NA, "(b)", "(e)", 
NA, "(e)", NA, NA, "(b)", NA, NA, NA, NA, "(b)", NA, "(b)", NA, 
"(b)", NA, NA, NA, "(b)", NA, NA, NA, NA, NA, NA, NA, NA), `2004` = c("16.100000000000001", 
"17.800000000000001", "17.899999999999999", "16.600000000000001", 
"8.4000000000000004", "18.100000000000001", "9.0999999999999996", 
"12.4", "6.2000000000000002", "21.300000000000001", "30.100000000000001", 
"26.399999999999999", ":", "12.300000000000001", "15.300000000000001", 
"26.399999999999999", "21.699999999999999", "9.4000000000000004", 
"8.4000000000000004", "22.699999999999999", "13.9", "46.899999999999999", 
"15.800000000000001", "13.699999999999999", "12.300000000000001", 
"13.4", "12.300000000000001", "10", "14.199999999999999", "4.7999999999999998", 
"4.0999999999999996", "14.1", "8.9000000000000004", ":", "6.7000000000000002", 
":", ":", ":", ":", ":", ":"), ...11 = c(NA, NA, NA, NA, NA, 
NA, NA, "(e)", NA, NA, "(b)", NA, NA, NA, "(e)", "(b)", "(e)", 
NA, NA, NA, NA, NA, NA, "(b)", "(b)", NA, NA, NA, NA, NA, NA, 
"(b)", NA, NA, NA, NA, NA, NA, NA, NA, NA), `2005` = c("16", 
"17.5", "17.5", "15.699999999999999", "9.6999999999999993", "18.800000000000001", 
"8.5999999999999996", "12.5", "4.9000000000000004", "20.5", "29.600000000000001", 
"24.699999999999999", ":", "11.699999999999999", "14.5", "26.300000000000001", 
"21.699999999999999", "9.8000000000000007", "8.4000000000000004", 
"21", "13.6", "44.899999999999999", "16.5", "12.9", "13.4", "12.699999999999999", 
"13.5", "9.5999999999999996", "15.800000000000001", "4.2999999999999998", 
"5.2999999999999998", "13.5", "8.4000000000000004", ":", "7", 
":", ":", ":", ":", ":", ":"), ...13 = c(NA, NA, NA, NA, NA, 
NA, NA, "(b)", NA, NA, NA, "(b)", NA, NA, "(e)", NA, NA, NA, 
NA, NA, NA, "(b)", "(b)", NA, NA, NA, NA, NA, NA, NA, "(b)", 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), `2006` = c("15.699999999999999", 
"16.899999999999999", "17.100000000000001", "15.199999999999999", 
"9.5", "18.600000000000001", "9", "12.199999999999999", "7", 
"19.899999999999999", "28.600000000000001", "23.600000000000001", 
":", "11.199999999999999", "14.1", "25.800000000000001", "20.300000000000001", 
"10", "6.9000000000000004", "19.5", "14.5", "43.899999999999999", 
"15.9", "12.9", "14.199999999999999", "12.9", "12.699999999999999", 
"9.8000000000000007", "17.100000000000001", "4.7999999999999998", 
"5.9000000000000004", "13.5", "8.8000000000000007", ":", "7.5", 
":", ":", "19.600000000000001", ":", ":", "49.200000000000003"
), ...15 = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, "(e)", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, "(b)", NA, NA, NA, NA, NA, NA), `2007` = c("15.6", 
"16.600000000000001", "16.699999999999999", "14.699999999999999", 
"9.9000000000000004", "19.100000000000001", "8.5", "12.4", "8.8000000000000007", 
"18.399999999999999", "28.399999999999999", "22", ":", "10.199999999999999", 
"16.199999999999999", "25.800000000000001", "18.699999999999999", 
"10.199999999999999", "7.5", "17.300000000000001", "14.6", "41.299999999999997", 
"15.5", "13.300000000000001", "14.699999999999999", "12.800000000000001", 
"13.1", "10.4", "17.300000000000001", "4.7000000000000002", "6", 
"13.800000000000001", "10.1", ":", "6.7999999999999998", ":", 
":", "18.199999999999999", ":", ":", "48.799999999999997"), ...17 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, "(b)", NA, NA, NA, NA, NA, NA, 
NA, NA, NA, "(b)", NA, NA, NA, "(b)", NA, NA, NA, NA, NA, NA, 
NA, "(b)", NA, NA, NA, NA, NA, NA, NA, NA, NA), `2008` = c("15.1", 
"15.699999999999999", "15.9", "13.4", "10.699999999999999", "19.5", 
"8.4000000000000004", "12.300000000000001", "8.5999999999999996", 
"16", "27.5", "19", ":", "10.1", "15.9", "24.699999999999999", 
"17", "7.4000000000000004", "6.9000000000000004", "17.100000000000001", 
"13.9", "39.100000000000001", "15", "12.5", "15.699999999999999", 
"12.300000000000001", "14.300000000000001", "8.9000000000000004", 
"17.100000000000001", "5.2999999999999998", "6.2999999999999998", 
"13.1", "9.5", ":", "6.2000000000000002", ":", ":", "20", ":", 
":", "47.799999999999997"), ...19 = c(NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, "(b)", NA, NA, NA, NA, NA, 
NA, NA, NA, NA), `2009` = c("13.5", "13.9", "13.9", "12.199999999999999", 
"9.8000000000000007", "18.800000000000001", "6", "10.9", "2", 
"10.199999999999999", "25.600000000000001", "14.199999999999999", 
":", "9.3000000000000007", "12.5", "24", "14.5", "0.29999999999999999", 
"-0.40000000000000002", "17.5", "12.5", "37.5", "13.699999999999999", 
"10.5", "15", "10.300000000000001", "14.4", "7.7000000000000002", 
"16.399999999999999", "2.2999999999999998", "5.2000000000000002", 
"11.5", "5.4000000000000004", ":", "5.2000000000000002", ":", 
":", "21.300000000000001", ":", ":", "44.600000000000001"), ...21 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, "(b)", NA, NA, NA, NA, NA, 
"(b)", NA, NA, "(b)", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), `2010` = c("13", 
"13.199999999999999", "13.4", "11.9", "7.7999999999999998", "18.699999999999999", 
"5.5999999999999996", "10.699999999999999", "1.8999999999999999", 
"8.8000000000000007", "24.199999999999999", "12.9", ":", "9.0999999999999996", 
"11.5", "23.199999999999999", "12.9", "-0.5", "-1.5", "17.199999999999999", 
"10.9", "36.600000000000001", "12.699999999999999", "10.199999999999999", 
"14", "9.8000000000000007", "16.600000000000001", "7.5", "14.5", 
"3", "6.0999999999999996", "11.4", "5.5", ":", "5.2000000000000002", 
"12.9", ":", "20.899999999999999", ":", ":", "44.700000000000003"
), ...23 = c(NA, NA, NA, NA, "(b)", NA, NA, "(b)", NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "(b)", NA, 
"(b)", NA, NA, NA, NA, NA, NA, NA, NA, "(b)", NA, NA, NA, NA, 
NA), `2011` = c("12.800000000000001", "12.9", "13", "11.5", "6.2000000000000002", 
"18.199999999999999", "6.5999999999999996", "10.4", "5.7000000000000002", 
"8.6999999999999993", "22.100000000000001", "11.6", ":", "9.3000000000000007", 
"12.5", "22.600000000000001", "11.9", "2.2000000000000002", "0.59999999999999998", 
"16.199999999999999", "11.699999999999999", "35.200000000000003", 
"12", "10", "14.699999999999999", "8.5999999999999996", "15.300000000000001", 
"7", "15.1", "3.7000000000000002", "5.5999999999999996", "11.5", 
"5.4000000000000004", ":", "5", "12.199999999999999", "12.6", 
"19", ":", ":", "45.399999999999999"), ...25 = c(NA, NA, NA, 
NA, "(b)", "(b)", NA, "(b)", NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, "(b)", NA, NA, "(b)", NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), `2012` = c("12.199999999999999", 
"12.1", "12.1", "11", "5.5999999999999996", "17.699999999999999", 
"6.4000000000000004", "10.5", "5.7000000000000002", "8.5999999999999996", 
"19.800000000000001", "10", ":", "8.8000000000000007", "11.1", 
"21", "11.300000000000001", "3.6000000000000001", "1.2", "14.4", 
"11.1", "31.399999999999999", "11.300000000000001", "9.6999999999999993", 
"14.5", "6.7999999999999998", "16.100000000000001", "7.2000000000000002", 
"15.5", "3", "5.0999999999999996", "11.6", "5.2999999999999998", 
":", "5.0999999999999996", "11.4", "11.699999999999999", "18.800000000000001", 
":", ":", "44.100000000000001"), ...27 = c(NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA), `2013` = c("11.699999999999999", "11.4", "11.5", 
"10.199999999999999", "5.7000000000000002", "17.199999999999999", 
"6.2999999999999998", "9.5999999999999996", "6.5999999999999996", 
"10.5", "19.399999999999999", "9.5999999999999996", ":", "8.0999999999999996", 
"8.8000000000000007", "19.800000000000001", "10.4", "4.2000000000000002", 
"2.6000000000000001", "14.1", "12.4", "28.600000000000001", "10.5", 
"9.0999999999999996", "14.5", "6.4000000000000004", "16.300000000000001", 
"8.1999999999999993", "14.4", "2.7999999999999998", "5", "11.1", 
"6.5", ":", "5", "10.9", "10.6", "19", ":", ":", "43.5"), ...29 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA), `2014` = c("11.5", "11.1", "11.199999999999999", 
"8.6999999999999993", "6.0999999999999996", "17.5", "7.2999999999999998", 
"9.0999999999999996", "7.7000000000000002", "11.800000000000001", 
"18.300000000000001", "10.199999999999999", "7.5", "7.4000000000000004", 
"10", "19.399999999999999", "7.7000000000000002", "4.5999999999999996", 
"2.5", "12.9", "13.300000000000001", "26.800000000000001", "11.4", 
"8.1999999999999993", "14.199999999999999", "7.0999999999999996", 
"16.699999999999999", "8", "14.6", "1.8999999999999999", "4.5999999999999996", 
"11.300000000000001", "6.2999999999999998", ":", "4.7999999999999998", 
"9.8000000000000007", "11.699999999999999", "20.800000000000001", 
":", "15.199999999999999", "43.399999999999999"), ...31 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, "(b)"), `2015` = c("11.6", "11.199999999999999", 
"11.300000000000001", "8.3000000000000007", "6.5999999999999996", 
"16.600000000000001", "7.5999999999999996", "8.6999999999999993", 
"7.9000000000000004", "12.300000000000001", "18", "11.199999999999999", 
"7.2000000000000002", "7.0999999999999996", "9.5", "20", "8.3000000000000007", 
"4.0999999999999996", "2.3999999999999999", "11.699999999999999", 
"13.699999999999999", "26.800000000000001", "11.1", "8.1999999999999993", 
"13.800000000000001", "6.7000000000000002", "17.5", "8.5999999999999996", 
"14.699999999999999", "2.1000000000000001", "4.2000000000000002", 
"11.199999999999999", "6.2999999999999998", ":", "4.5999999999999996", 
"9.6999999999999993", "10.300000000000001", "19.399999999999999", 
":", "15.300000000000001", "42.799999999999997"), ...33 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, "(b)", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA), `2016` = c("11.6", "11.199999999999999", 
"11.300000000000001", "9.3000000000000007", "7.2999999999999998", 
"16", "6.7000000000000002", "8.1999999999999993", "8.1999999999999993", 
"12.1", "19", "11.5", "7.5", "7.4000000000000004", "9.5999999999999996", 
"20.100000000000001", "9.6999999999999993", "2.8999999999999999", 
"1.8999999999999999", "11", "14", "25.5", "11", "7.7999999999999998", 
"14.199999999999999", "6.7999999999999998", "17.600000000000001", 
"6.5999999999999996", "14.199999999999999", "3.2999999999999998", 
"3.7999999999999998", "11", "6.7000000000000002", ":", "3.7000000000000002", 
"9", "11.699999999999999", "21.199999999999999", ":", "14.4", 
"42.299999999999997"), ...35 = c(NA, NA, NA, NA, NA, NA, "(b)", 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA), `2017` = c("11.5", "11.199999999999999", "11.300000000000001", 
"9.8000000000000007", "8", "15.800000000000001", "6.5", "7.9000000000000004", 
"7.2999999999999998", "12.1", "19.699999999999999", "11.9", "7.9000000000000004", 
"7.7999999999999998", "10.6", "19.800000000000001", "9.5", "4.2999999999999998", 
"1", "7.9000000000000004", "15.300000000000001", "24.100000000000001", 
"10.5", "8", "14.6", "7.5", "17.100000000000001", "7.2000000000000002", 
"12.800000000000001", "3.5", "4", "10.199999999999999", "6", 
":", "4", "9.4000000000000004", "13.800000000000001", "21.899999999999999", 
":", "14", "41.600000000000001"), ...37 = c(NA, NA, NA, "(b)", 
NA, NA, "(b)", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA), `2018` = c("11.6", "11.199999999999999", 
"11.300000000000001", "8.4000000000000004", "8.1999999999999993", 
"15.199999999999999", "6.7000000000000002", "8.0999999999999996", 
"7.7999999999999998", "12.199999999999999", "21", "12.1", "7.5999999999999996", 
"7.5999999999999996", "10.199999999999999", "19.800000000000001", 
"10.4", "4.2000000000000002", "2.2999999999999998", "8", "15.300000000000001", 
"21.899999999999999", "10.1", "9", "14.4", "6.7999999999999998", 
"18.300000000000001", "7.2999999999999998", "13.699999999999999", 
"3.7000000000000002", "4.2999999999999998", "9.9000000000000004", 
"6.2999999999999998", ":", "5.2000000000000002", "9.0999999999999996", 
"13.800000000000001", "21.399999999999999", ":", "14.699999999999999", 
"40.799999999999997")), row.names = c(NA, -41L), class = c("tbl_df", 
"tbl", "data.frame"))

I've try to change years as vector but i failed, i'm trying with your answer (thanks ! )

nwerth · December 2, 2019, 2:09pm

I've formatted your code. This prevents the chance the forum system will misinterpret parts as formatting, but it's also easier to read for everyone.

You can format code blocks in posts by putting a line with three accent marks before and after the code:

```
a <- 1 + 1
```

Hanz · December 2, 2019, 3:36pm

It's work, thanks a lot

system · December 23, 2019, 3:36pm

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.