Combine Multiple Columns Into Tidy Data [duplicate]

by

Almost every data tidying problem can be solved in three steps:

  1. Gather all non-variable columns
  2. Separate “colname” column into multiple variables
  3. Re-spread the data

(often you’ll only need one or two of these, but I think they’re almost always in this order).

For your data:

  1. The only column that’s already a variable is unique.id
  2. You need to split current column names into variable and number
  3. Then you need to put the “variable” variable back into columns

This looks like:

library(tidyr)
library(dplyr)

df3 %>%
  gather(col, value, -unique.id, -intervention) %>%
  separate(col, c("variable", "number")) %>%
  spread(variable, value, convert = TRUE) %>%
  mutate(start = as.Date(start, "1970-01-01"), stop = as.Date(stop, "1970-01-01"))

Your case is a bit more complicated because you have two types of variables, so you need to restore the types at the end.

More Related Contents:

  1. Gather multiple sets of columns
  2. R spreading multiple columns with tidyr [duplicate]
  3. dplyr summarise: Equivalent of “.drop=FALSE” to keep groups with zero length in output
  4. Using spread with duplicate identifiers for rows
  5. Proper idiom for adding zero count rows in tidyr/dplyr
  6. Add NAs to make all list elements equal length
  7. Reshape from long to wide and create columns with binary value
  8. adding default values to item x group pairs that don’t have a value (df %>% spread %>% gather seems strange)
  9. combine two data frames with all posible combinations
  10. Spread with duplicate identifiers (using tidyverse and %>%) [duplicate]
  11. Spreading a two column data frame with tidyr
  12. How to replace all NA in a dataframe using tidyr::replace_na? [duplicate]
  13. tidyverse – prefered way to turn a named vector into a data.frame/tibble
  14. Unlisting columns by groups
  15. How to get summary statistics by group
  16. Is there a dplyr equivalent to data.table::rleid?
  17. Replace NA with previous or next value, by group, using dplyr
  18. Filter rows which contain a certain string
  19. Select groups which have at least one of a certain value
  20. How to strsplit different number of strings in certain column by do function
  21. Finding Overlaps between interval sets / Efficient Overlap Joins
  22. R dplyr rolling sum
  23. How to remove rows with any zero value
  24. Select columns based on string match – dplyr::select
  25. Delete columns/rows with more than x% missing
  26. How to aggregate a dataframe by week?
  27. dplyr: lead() and lag() wrong when used with group_by()
  28. Reorder rows using custom order
  29. R dplyr rowwise mean or min and other methods?
  30. Override column types when importing data using readr::read_csv() when there are many columns

Leave a Comment