Reshape wide data in R: Converting two rows into columns

Question

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.

It seems like the issue isn't about reshaping the data, but removing the text that's in brackets — camille, Oct 05 '18 at 18:02
Also note that the years and sexes get lost because they aren't actually column names. It would be easier to help with a [reproducible question](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) — camille, Oct 05 '18 at 18:04
If you use melt from data.table, the patterns() helper function might be useful, eg https://stackoverflow.com/q/12466493 (You'll need to sort out issues with skipping rows in read.csv so that row 4 are your headers first, I guess.) — Frank, Oct 05 '18 at 18:14
The key is to merge the first 2 rows together to create unique column names then use the `spread` function from tidyr, If you can post a sample of your data using the `dput` function, it will made it easier to receive help. — Dave2e, Oct 05 '18 at 20:25

score 1 · Accepted Answer · answered Oct 06 '18 at 15:30

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]

Reshape wide data in R: Converting two rows into columns

1 Answers1