Hoping someone can help me.
I have this dataset:
I want to produce a dataframe exactly like this:
I've tried so many different ways using tidyverse, baseR to do this, but I can't seem to find a solution.
Can anyone please help?
CodePudding user response:
Maybe something like
df %>% pivot_longer(cols = -Subject, names_to = c("Gender", "Age"), names_sep = "_")
But without any pasteable sample data, it's hard to check.
CodePudding user response:
Please read about how to ask a good question and how to give a reproducible example. Sharing data as screenshot is not the correct way to get help.
For reproducibility I have created a sample dataset by hand which is similar to your data.
df <- data.frame(Subject = c('ID986', 'ID407'), M_22_25 = c(4, 5),
M_31_35 = NA, F_31_35 = NA, M_26_30 = c(2, 3))
df
# Subject M_22_25 M_31_35 F_31_35 M_26_30
#1 ID986 4 NA NA 2
#2 ID407 5 NA NA 3
You can use tidyr::pivot_longer
to get data in required structure.
tidyr::pivot_longer(df,
cols = -Subject,
names_to = c('Gender', 'Age'),
names_pattern = '(M|F)_(.*)',
values_drop_na = TRUE)
# Subject Gender Age value
# <chr> <chr> <chr> <dbl>
#1 ID986 M 22_25 4
#2 ID986 M 26_30 2
#3 ID407 M 22_25 5
#4 ID407 M 26_30 3