How do I transpose this dataset in R? See below:
I downloaded a dataset that looks like this (the dates go backward from 2016 - 1975):
V1 V2 V3 V4 V5
1 2016 2016 2016 2015
4 Country Both-sexes Male Female Both-sexes
5 Afghanistan 23.4 [22.0-24.8] 22.6 [20.1-25.1] 24.1 [23.0-25.3] 23.3 [21.9-24.6]
6 Albania 26.7 [25.8-27.5] 27.0 [25.8-28.2] 26.3 [25.0-27.6] 26.6 [25.8-27.4]
7 Algeria 25.5 [24.5-26.5] 24.7 [23.4-26.1] 26.4 [24.9-27.8] 25.5 [24.5-26.4]
8 Andorra 26.7 [24.6-28.7] 27.3 [24.8-29.8] 26.1 [22.8-29.5] 26.7 [24.7-28.7]
I need to make the year and sex rows (currently numbered rows 1 & 4) into columns. Here's what I want:
1 Country Year Sex Rate
2 Afghanistan 2016 Both-sexes 23.4
3 Afghanistan 2016 Male 22.6
3 Afghanistan 2016 Female 24.1
4 Afghanistan 2015 Both-sexes 23.3
...and the rows continue on through all of the years for all of the countries in the dataset.
Here's what I have done trying to get there:
cfile <- read.csv(file= "countries-BMI.csv", header = F)
#removed second two rows that have unnecessary info
countries_data <- cfile[-c(2,3), ]
molten_countries_data <- melt(countries_data, id=c("V1"))
.and here's my result - head(molten_countries_data):
V1 variable value
1 V2 2016
2 Country V2 Both-sexes
3 Afghanistan V2 23.4 [22.0-24.8]
4 Albania V2 26.7 [25.8-27.5]
5 Algeria V2 25.5 [24.5-26.5]
6 Andorra V2 26.7 [24.6-28.7]
Not what I wanted! Please help.
I figured it out thanks to the tip from @Dave2e to merge the first 2 rows first. Here's what I ended up doing:
library(reshape2)
library(tidyr)
#load data frame without first two rows
cdata <- read.csv("countries-BMI.csv", skip = 2, header = F)
#create header by combining top two rows
headers <- read.csv("countries-BMI.csv", nrows=2, header=FALSE)
headers_names <- sapply(headers,paste,collapse="_")
#add the new header to data frame
names(cdata) <- headers_names
#transpose the "wide data" to make it tidy/long
longdata <- melt(cdata, id.vars = c("_Country"))
#separate the year and sex columns
countriesBMI2 <- separate(data = longdata, col = variable, into = c("Year", "Sex"), sep = "_")
My result: head(countriesBMI2)
_Country Year Sex value
1 Afghanistan 2016 Both-sexes 23.4 [22.0-24.8]
2 Albania 2016 Both-sexes 26.7 [25.8-27.5]
3 Algeria 2016 Both-sexes 25.5 [24.5-26.5]
4 Andorra 2016 Both-sexes 26.7 [24.6-28.7]
5 Angola 2016 Both-sexes 23.3 [21.2-25.6]
6 Antigua and Barbuda 2016 Both-sexes 26.7 [24.6-28.8]