How to automatically tidy data frames with n pairs of date (key) / value columns

user_8689527 0
Viewed 36

I have some date/value-pairs in large csv files which I like to clean up automatically. The number of pairs is unknown. Below is an example and a way to manually clean up that data. How can I automate it? Please let me know if more information are needed.

# I have a data frame with multiple date/value pairs
# the number of pairs is varying
# in a simple example 2 date/value pairs like this

library(magrittr)
suppressMessages(library(dplyr))

date1 <- Sys.Date() + seq(1, 10)
type1 <- as.integer(runif(10) * 1000)
date2 <- c(Sys.Date() + seq(1, 8, 2), rep(NA, 6))
type2 <- c(as.integer(runif(4) * 1000), rep(NA, 6))

df_raw <- data.frame(date1, type1, date2, type2)

df_raw
#>         date1 type1      date2 type2
#> 1  2021-01-23   908 2021-01-23   344
#> 2  2021-01-24   853 2021-01-25   849
#> 3  2021-01-25   627 2021-01-27   952
#> 4  2021-01-26   491 2021-01-29   597
#> 5  2021-01-27   237       <NA>    NA
#> 6  2021-01-28    50       <NA>    NA
#> 7  2021-01-29   101       <NA>    NA
#> 8  2021-01-30   135       <NA>    NA
#> 9  2021-01-31   759       <NA>    NA
#> 10 2021-02-01   943       <NA>    NA


# I could manually clean up
# to get the expected result
df1 <- df_raw[, 1:2] %>%
  mutate(type = "type1") %>%
  filter(!is.na(date1)) %>%
  rename(date = date1, value = type1)

df2 <- df_raw[, 3:4] %>%
  mutate(type = "type2") %>%
  filter(!is.na(date2)) %>%
  rename(date = date2, value = type2)

rbind(df1, df2)
#>          date value  type
#> 1  2021-01-23   908 type1
#> 2  2021-01-24   853 type1
#> 3  2021-01-25   627 type1
#> 4  2021-01-26   491 type1
#> 5  2021-01-27   237 type1
#> 6  2021-01-28    50 type1
#> 7  2021-01-29   101 type1
#> 8  2021-01-30   135 type1
#> 9  2021-01-31   759 type1
#> 10 2021-02-01   943 type1
#> 11 2021-01-23   344 type2
#> 12 2021-01-25   849 type2
#> 13 2021-01-27   952 type2
#> 14 2021-01-29   597 type2

Created on 2021-01-22 by the reprex package (v0.3.0)

Edit

A bit more complicated example

structure(list(date1 = structure(c(18650, 18651, 18652, 18653, 
18654, 18655, 18656, 18657, 18658, 18659), class = "Date"), type1 = c(922L, 
795L, 646L, 363L, 692L, 843L, 799L, 168L, 758L, 888L), date2 = structure(c(18650, 
18652, 18654, 18656, NA, NA, NA, NA, NA, NA), class = "Date"), 
    type2 = c(158L, 146L, 550L, 830L, NA, NA, NA, NA, NA, NA), 
    date3 = structure(c(18650, 18652, 18654, 18656, NA, NA, NA, 
    NA, NA, NA), class = "Date"), type3 = c(659L, 930L, 688L, 
    749L, NA, NA, NA, NA, NA, NA), date4 = structure(c(18650, 
    18651, 18652, 18653, 18654, 18655, NA, NA, NA, NA), class = "Date"), 
    type4 = c(743L, 497L, 558L, 174L, 187L, 937L, NA, NA, NA, 
    NA), date5 = structure(c(18650, 18652, 18654, 18656, NA, 
    NA, NA, NA, NA, NA), class = "Date"), type5 = c(484L, 845L, 
    784L, 640L, NA, NA, NA, NA, NA, NA), date6 = structure(c(18650, 
    18652, 18654, 18656, NA, NA, NA, NA, NA, NA), class = "Date"), 
    type6 = c(688L, 587L, 113L, 747L, NA, NA, NA, NA, NA, NA), 
    daten = structure(c(18650, 18653, 18656, NA, NA, NA, NA, 
    NA, NA, NA), class = "Date"), typen = c(110L, 876L, 809L, 
    NA, NA, NA, NA, NA, NA, NA)), class = "data.frame", row.names = c(NA, 
-10L))
tidyverse r
1 Answers

I think your solution seems fine to me. Another option would be to join both data frame parts by date, and then pivot longer.

For more than one pair, I'd split the data frame into n chunks of 2 columns, and then rename all date columns, and then use purrr::reduce

library(tidyverse)

pairs <- split(seq_along(mydat), ceiling(seq_along(mydat)/2))
ls_dat <- lapply(pairs, function(x) mydat[x] )
ls_dat <- lapply(ls_dat, function(x) rename(x, date = starts_with("date")))

purrr::reduce(ls_dat, full_join, by = "date") %>%
  pivot_longer(names_to = "type", values_to = "value", cols = starts_with("type")) %>%
  drop_na("value")
#> # A tibble: 35 x 3
#>    date       type  value
#>    <date>     <chr> <int>
#>  1 2021-01-23 type1   922
#>  2 2021-01-23 type2   158
#>  3 2021-01-23 type3   659
#>  4 2021-01-23 type4   743
#>  5 2021-01-23 type5   484
#>  6 2021-01-23 type6   688
#>  7 2021-01-23 typen   110
#>  8 2021-01-24 type1   795
#>  9 2021-01-24 type4   497
#> 10 2021-01-25 type1   646
#> # … with 25 more rows

data

mydat <- structure(list(date1 = structure(c(18650, 18651, 18652, 18653, 18654, 18655, 18656, 18657, 18658, 18659), class = "Date"), type1 = c(922L, 795L, 646L, 363L, 692L, 843L, 799L, 168L, 758L, 888L), date2 = structure(c(18650, 18652, 18654, 18656, NA, NA, NA, NA, NA, NA), class = "Date"), type2 = c(158L, 146L, 550L, 830L, NA, NA, NA, NA, NA, NA), date3 = structure(c(18650, 18652, 18654, 18656, NA, NA, NA, NA, NA, NA), class = "Date"), type3 = c(659L, 930L, 688L, 749L, NA, NA, NA, NA, NA, NA), date4 = structure(c(18650, 18651, 18652, 18653, 18654, 18655, NA, NA, NA, NA), class = "Date"), type4 = c(743L, 497L, 558L, 174L, 187L, 937L, NA, NA, NA, NA), date5 = structure(c(18650, 18652, 18654, 18656, NA, NA, NA, NA, NA, NA), class = "Date"), type5 = c(484L, 845L, 784L, 640L, NA, NA, NA, NA, NA, NA), date6 = structure(c(18650, 18652, 18654, 18656, NA, NA, NA, NA, NA, NA), class = "Date"), type6 = c(688L, 587L, 113L, 747L, NA, NA, NA, NA, NA, NA), daten = structure(c(18650, 18653, 18656, NA, NA, NA, NA, NA, NA, NA), class = "Date"), typen = c(110L, 876L, 809L, NA, NA, NA, NA, NA, NA, NA)), class = "data.frame", row.names = c(NA, -10L))
user_79411880
Related