I want to convert row names to values to a column with keeping the corresponding values. My data has country names in the first column and years for the column names for the remaining of the columns with values in the cells. I want to convert this to a proper table formate. See below for an example.
Example of the table formate:
Country | 2002 | 2003| ...
Canada | 2.2 | 2.4 | ...
US | 4.2 | 7.4 | ...
.
.
.
I would like to have the table in the format of: Country | Year | Value
Canada | 2002 | 2.2
Canada | 2.2 | 2.4
...
I believe the tidy data package should work by the country is being dropped. See my example below.
Data:
ElectricCarStock_BEVandPHEV<- structure(list(Country = structure(c(1L, 2L, 3L, 4L, 5L, 6L,
7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L, 15L, 17L, 18L, 19L, 20L,
22L, 23L, 16L, 21L), .Label = c("Australia", "Brazil", "Canada",
"Chile", "China", "Finland", "France", "Germany", "India", "Japan",
"Korea", "Mexico", "Netherlands", "New Zealand", "Norway", "Others",
"Portugal", "South Africa", "Sweden", "Thailand", "Total", "United Kingdom",
"United States"), class = "factor"), `2005` = c(NA, NA, NA, NA,
NA, NA, 0.01, 0.02, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
0.22, 1.12, 0.53, 1.89), `2006` = c(NA, NA, NA, NA, NA, NA, 0.01,
0.02, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 0.01, 0.55, 1.12,
0.53, 2.23), `2007` = c(NA, NA, NA, NA, NA, NA, 0.01, 0.02, NA,
NA, NA, NA, NA, NA, 0.01, NA, NA, NA, 0.01, 1, 1.12, 0.53, 2.69
), `2008` = c(NA, NA, NA, NA, NA, NA, 0.01, 0.09, 0.37, NA, NA,
NA, 0.01, NA, 0.26, NA, NA, NA, 0.01, 1.22, 2.58, 0.61, 5.15),
`2009` = c(NA, NA, NA, NA, 0.48, NA, 0.12, 0.1, 0.53, 1.08,
NA, NA, 0.15, NA, 0.4, NA, NA, NA, 0.01, 1.4, 2.58, 0.64,
7.48), `2010` = c(NA, NA, NA, NA, 1.91, NA, 0.3, 0.25, 0.88,
3.52, 0.06, NA, 0.27, 0.01, 0.79, NA, NA, NA, 0.01, 1.68,
3.77, 0.81, 14.26), `2011` = c(50, NA, 0.52, 0.01, 6.98,
0.06, 3.03, 1.89, 1.33, 16.14, 0.34, NA, 1.14, 0.03, 2.63,
NA, NA, 0.18, 0.01, 2.89, 21.5, 2.6, 61.33), `2012` = c(300,
NA, 2.54, 0.01, 16.88, 0.24, 9.29, 5.26, 2.76, 40.58, 0.85,
0.09, 6.26, 0.06, 7.15, NA, NA, 1.11, 0.02, 5.59, 74.74,
5.31, 179.03), `2013` = c(600, NA, 5.66, 0.02, 32.22, 0.47,
18.91, 12.19, 2.95, 69.46, 1.45, 0.1, 28.67, 0.09, 15.67,
NA, 0.03, 2.66, 0.03, 9.34, 171.44, 9.35, 381.3), `2014` = c(1920,
0.06, 10.73, 0.03, 105.39, 0.93, 31.54, 24.93, 3.35, 101.74,
2.76, 0.15, 43.76, 0.41, 35.44, NA, 0.05, 7.32, 0.1, 24.08,
290.22, 18.73, 703.65), `2015` = c(3690, 0.15, 17.69, 0.07,
312.77, 1.59, 54.49, 48.12, 4.35, 126.4, 5.95, 0.25, 87.53,
0.91, 69.17, NA, 0.29, 15.91, 0.37, 48.51, 404.09, 37.17,
1239.45), `2016` = c(5060, 0.32, 29.27, 0.1, 648.77, 3.29,
84, 72.73, 4.8, 151.25, 11.21, 0.66, 112.01, 2.41, 114.05,
NA, 0.67, 29.33, 0.38, 86.42, 563.71, 61.63, 1982.04), `2017` = c(7340,
0.68, 45.95, 0.25, 1227.77, 6.34, 118.77, 109.56, 6.8, 205.35,
25.92, 0.92, 119.33, 5.88, 176.31, 1.78, 0.86, 49.6, 0.4,
133.67, 762.06, 103.44, 3109.05)), .Names = c("Country",
"2005", "2006", "2007", "2008", "2009", "2010", "2011", "2012",
"2013", "2014", "2015", "2016", "2017"), class = "data.frame", row.names = c(NA,
-23L))
Code:
library(tidyr)
library(dplyr)
temp<-gather(ElectricCarStock_BEVandPHEV, Country, 2:13)
head(temp)
