-1

I'm collecting data from 4 sensors and I have a dataframe with timestamp and one variable for each sensor. The dataframe looks like this:

img1

I want to reorganize my data to look like this:

img2

My solution involves cutting out each variable and merging them back together, but there must be a better way to do this.

Sorry for the obvious question, but I cannot find an answer from other people posts.

Thanks!

r2evans
  • 141,215
  • 6
  • 77
  • 149
Gerry
  • 117
  • 9
  • 1
    the differences between those 2 dataframes aren't obvious to me, to be honest. – tagoma May 24 '17 at 17:40
  • 2
    Google "R melt data.frame". – Roland May 24 '17 at 17:44
  • 4
    Welcome to SO! When feasible, please provide data that can be copy/pasted from the page, instead of images of said data. In order to provide a reproducible solution, you are suggesting that the volunteer should transcribe your images into something usable. I suggest you skim through [reproducible examples](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) (as well as [SO help on minimal/verifiable questions](https://stackoverflow.com/help/mcve)), specifically the use of `dput` and/or `read.table()` in order to provide a more reproducible question. – r2evans May 24 '17 at 17:45

1 Answers1

0

I recreated a dummy dataset that matches the data structure you provided. In the future, use the suggestions @r2evans gave for providing reproducible data. That will make answering your question much easier.

For reference, look up tidyr and gather. This is used to convert data from wide (your data) to long (the data structure you want).

Package used:

library(tidyverse)

Example of your data:

data <- data.frame("WPT1" = sample(1:100,10),
              "WPT2" = sample(1:100,10),
              "WPT3" = sample(1:100,10),
              "WPT4" = sample(1:100,10),
              "dt" = sample(seq(as.Date('1999/01/01'), 
                              as.Date('2000/01/01'), by="day"), 10)
)

head(data)[1:5]

  WPT1 WPT2 WPT3 WPT4         dt
1   33   49   79   85 1999-06-30
2   62   13   75   45 1999-07-16
3   96   86   60   34 1999-03-10
4    4   21   66   64 1999-08-18
5   49   70   32   49 1999-09-04

Conversion to long format:

data.long <- data %>%
  gather(ID,WP,-dt) %>%
  mutate(ID = replace(ID,ID == "WPT1",1),
         ID = replace(ID,ID == "WPT2",2),
         ID = replace(ID,ID == "WPT3",3),
         ID = replace(ID,ID == "WPT4",4),
         ID = as.numeric(ID)
         )

Output:

head(data.long)[1:5,]

      dt ID WP
1 1999-06-30  1 33
2 1999-07-16  1 62
3 1999-03-10  1 96
4 1999-08-18  1  4
5 1999-09-04  1 49
Matt Dzievit
  • 527
  • 3
  • 10