How to transform hierarchical data (from Beyond 20/20) from a partially wide format to long format in R? [duplicate]
I have some census data which is partially in long and partially in wide format that was exported from Beyond 20/20. I am struggling with how to reformat the data, since it is not totally in wide format as is. In the following data set, there is the number of male and female people for each education level and state. For example:
> have
State Education Male Female
1 CA 1 3 4
2 CA 2 4 6
3 NV 1 7 8
4 NV 2 9 19
However, I want the data to be fully long format. This would mean having the number of individuals that are in each unique state, education, and sex category. For example:
> want
State Education Sex Number
1 CA 1 Male 4
2 CA 1 Female 3
3 CA 2 Male 4
4 CA 2 Female 6
5 NV 1 Male 7
6 NV 1 Female 8
7 NV 2 Male 9
8 NV 2 Female 19
Thanks in advance for any thoughts or advice.
We could use pivot_longer
from tidyr
package:
library(tidyr)
library(dplyr)
df %>%
pivot_longer(
cols=c(Male, Female),
names_to = "Sex",
values_to = "Number"
)
State Education Sex Number
<chr> <int> <chr> <int>
1 CA 1 Male 3
2 CA 1 Female 4
3 CA 2 Male 4
4 CA 2 Female 6
5 NV 1 Male 7
6 NV 1 Female 8
7 NV 2 Male 9
8 NV 2 Female 19