How to reshape and add new variable to dataframe

Question

I have a dataframe shown below.

prefecture height_M weight_M height_F weight_F
1          110.7    19.1     109.8    18.7 
2          111.0    19.1     110.1    18.7
3          111.5    19.7     110.2    19.4

I'm new to R and not sure if "reshape" is the right word but I want to reshape the dataframe by making a new variable "sex" which will be M or F.

I also want to change the variable names of height_M, weight_M, height_F, weight_F to "height" and "weight".

The expected outcome will be something like this.

prefecture height   weight   sex
1          110.7    19.1     M
2          111.0    19.1     M
3          111.5    19.7     M
1          109.8    18.7     F
2          110.1    18.7     F
3          110.2    19.4     F

I tried doing this by mutate and rbind, but wanted to know if there is a better smarter way and need help.

Answer 1

Using dplyr and tidyr we can gather into long format, separate key into different columns and then spread to wide format.

library(dplyr)
library(tidyr)

df %>%
  gather(key, value, -prefecture) %>%
  separate(key, c("key", "sex"), sep = "_") %>%
  spread(key, value)

#  prefecture sex height weight
#1          1   F  109.8   18.7
#2          1   M  110.7   19.1
#3          2   F  110.1   18.7
#4          2   M  111.0   19.1
#5          3   F  110.2   19.4
#6          3   M  111.5   19.7