Tidy data with variable in intermittent rows

user_3046653 0
Viewed 68

I have datalogger that inserts a row with a timestamp every time the logger is turned on. The timestamp string is always the same format, but there are an inconsistent number of readings per timestamp.

How do I tidy the timestamp rows into a time variable?

This previous question is close, except I want the data in the extra rows to be a variable, not a header (it's also four years old, and I suspect there's a more elegant tidyverse solution these days): Tidy and Cast Data With Headers Stuck in Rows

library(tidyverse)

df_have <- tribble(
  ~site, ~n,  ~val,
  NA,  "Start 11:22:33", NA,
  "A", "N=1", .1,
  "A", "N=2", .3,
  NA,  "Start 12:33:44", NA,
  "B", "N=1", .2,
  "B", "N=2", .4,
  "B", "N=3", .6
)

df_want <- tribble(
  ~site, ~time, ~n,  ~val,
  "A", "11:22:33", "N=1", .1,
  "A", "11:22:33", "N=2", .3,
  "B", "12:33:44", "N=1", .2,
  "B", "12:33:44", "N=2", .4,
  "B", "12:33:44", "N=3", .6
)
data-wrangling tidyr tidyverse r
3 Answers

We could use separate, fill

df_have %>%
  separate("n", c("n","time"), sep=" ") %>% 
  fill(time) %>% 
  drop_na() %>% 
  select(site, time, n, val)

Output:

  site  time     n       val
  <chr> <chr>    <chr> <dbl>
1 A     11:22:33 N=1     0.1
2 A     11:22:33 N=2     0.3
3 B     12:33:44 N=1     0.2
4 B     12:33:44 N=2     0.4
5 B     12:33:44 N=3     0.6
user_133216470

We could create a grouping column based on the 'Start' substring in 'n', create the 'time' by removing the 'Start' followed by one or more spaces (\\s+) in the 'first' element of 'n', remove the first row (slice) and select the columns of interest after ungrouping

library(dplyr)
library(stringr)
library(tidyr)
df_have %>% 
   group_by(grp = cumsum(str_detect(n, 'Start'))) %>%
   mutate(time = str_remove(first(n), 'Start\\s+')) %>%
   slice(-1) %>%
   ungroup %>%
   select(site, time, n, val)

-output

# A tibble: 5 x 4
#  site  time     n       val
#  <chr> <chr>    <chr> <dbl>
#1 A     11:22:33 N=1     0.1
#2 A     11:22:33 N=2     0.3
#3 B     12:33:44 N=1     0.2
#4 B     12:33:44 N=2     0.4
#5 B     12:33:44 N=3     0.6

Or without grouping, we can fill after extracting the time component that follows the 'Start' and a space

df_have %>%
   mutate(time = str_extract(n, '(?<=Start\\s)\\S+')) %>%
   fill(time) %>% 
   filter(complete.cases(val))
user_37322710

Here is another solution :

library(dplyr)
library(tidyr)

df_have %>%
  mutate(time = replace(sub('Start\\s', '', n), !grepl('Start', n), NA)) %>%
  fill(time) %>%
  filter(!grepl('Start', n))

#  site  time     n       val
#  <chr> <chr>    <chr> <dbl>
#1 A     11:22:33 N=1     0.1
#2 A     11:22:33 N=2     0.3
#3 B     12:33:44 N=1     0.2
#4 B     12:33:44 N=2     0.4
#5 B     12:33:44 N=3     0.6
user_39629140
Related