0

Objective: Create a data frame where:

  1. each row is corresponding with a unique id (site_no) and time stamp (startDateTime)
  2. each column is identified by a parameter id (parm_cd)

This is what my data currently looks like:

myData <- structure(list(site_no = c(1362370, 1362370, 1362370, 1362370, 1362370, 1362370, 1362370, 1362370, 1362370, 1362370), parm_cd = c(602L, 610L, 613L, 618L, 71845L, 71856L, 602L, 610L, 618L, 71845L), result_va = c(0.601, 0.028, 0.028, 0.728, 0.036, 0.092, 0.68, 0.028, 0.705, 0.036), startDateTime = c("2006-11-01 10:46:00", "2006-11-01 10:46:00", "2006-11-01 10:46:00", "2006-11-01 10:46:00", "2006-11-01 10:46:00", "2006-11-01 10:46:00", "2006-11-01 10:47:00", "2006-11-01 10:47:00", "2006-11-01 10:47:00", "2006-11-01 10:47:00")), row.names = c(NA, 10L), class = "data.frame")

This is what I would like the data to look like:

enter image description here

Thomas Adler
  • 47
  • 6

2 Answers2

2

A base R option is using reshape, which transforms the data frame from long to wide

reshape(
  myData,
  direction = "wide",
  idvar = c("site_no","startDateTime"),
  timevar = "parm_cd"
)

giving

  site_no       startDateTime result_va.602 result_va.610 result_va.613
1 1362370 2006-11-01 10:46:00         0.601         0.028         0.028
7 1362370 2006-11-01 10:47:00         0.680         0.028            NA
  result_va.618 result_va.71845 result_va.71856
1         0.728           0.036           0.092
7         0.705           0.036              NA
ThomasIsCoding
  • 96,636
  • 9
  • 24
  • 81
1

An option would be to use pivot_wider after creating a rowid column

library(dplyr)
library(tidyr)
library(data.table)
 myData %>% 
    mutate(rid = rowid(site_no, parm_cd)) %>% 
    pivot_wider(names_from = parm_cd, values_from = result_va) %>%
    select(-rid)

-output

# A tibble: 2 x 8
#  site_no startDateTime       `602` `610`  `613` `618` `71845` `71856`
#    <dbl> <chr>               <dbl> <dbl>  <dbl> <dbl>   <dbl>   <dbl>
#1 1362370 2006-11-01 10:46:00 0.601 0.028  0.028 0.728   0.036   0.092
#2 1362370 2006-11-01 10:47:00 0.68  0.028 NA     0.705   0.036  NA
akrun
  • 874,273
  • 37
  • 540
  • 662