0

I got a data frame from an mysql database that looks like this:

        value date   
sensor1   3   8:25
sensor2   5   8:25
sensor1   4   9:01
sensor2   8   9:01

Is there any way to merge the rows as columns? to look like this:

date  sensor1  sensor2
8:25     3        5
9:01     4        8

the original data looks like this:

    Sensor                   Value      Date
 1  iphone/uRn0CzNe/lighting  3         2015-10-21 08:25:09
 2  iphone/uRn0CzNe/lightS    0.360748  2015-10-21 08:25:11
 3  iphone/uRn0CzNe/lighting  3         2015-10-21 08:57:23
 4  iphone/uRn0CzNe/lightS    0.732653  2015-10-21 08:57:23
 5  iphone/uRn0CzNe/lighting  3         2015-10-21 11:32:00
 6  iphone/uRn0CzNe/lightS    0.943316  2015-10-21 11:32:00
 7  iphone/uRn0CzNe/lighting  3         2015-10-21 13:27:28
 8  iphone/uRn0CzNe/lightS    0.869136  2015-10-21 13:27:28
 9  iphone/uRn0CzNe/lighting  3         2015-10-21 13:56:10
10  iphone/uRn0CzNe/lightS    0.982124  2015-10-21 13:56:10

and what are the steps I need to take? like to round the date to the minute?

I've tried to use: 

    > data %>%
    +   group_by(Sensor) %>%
    +   spread(Date, Value)

but this resulted in a table where many dates weren't shown anymore:

                    Sensor 2015-10-21 08:25:09 2015-10-21 08:25:11 2015-10-21 08:57:23 2015-10-21 11:32:00 2015-10-21 13:27:28 2015-10-21 13:56:10 2015-10-21 14:07:27 2015-10-21 14:52:31
                     (chr)               (chr)               (chr)               (chr)               (chr)               (chr)               (chr)               (chr)               (chr)
1 iphone/uRn0CzNe/lighting                   3                  NA                   3                   3                   3                   3                   4                   3
2   iphone/uRn0CzNe/lightS                  NA            0.360748            0.732653            0.943316            0.869136            0.982124            1.000000            0.754135
Variables not shown: 2015-10-21 15:58:32 (chr), 2015-10-21 17:31:21 (chr), 2015-10-21 17:56:37 (chr), 2015-10-21 18:47:33 (chr), 2015-10-21 18:47:34 (chr), 2015-10-22 10:06:27 (chr), 2015-10-22
  11:11:25 (chr), 2015-10-22 12:06:23 (chr), 2015-10-22 16:43:36 (chr), 2015-10-22 19:34:50 (chr), 2015-10-23 19:18:50 (chr), 2015-10-23 19:18:51 (chr), 2015-10-25 16:37:31 (chr), 2015-10-26
  09:28:36 (chr), 2015-11-10 16:47:54 (chr), 2015-11-10 16:47:55 (chr)
Bergrebell
  • 4,263
  • 4
  • 40
  • 53
  • What are '1,2,3,...'??? And I can't see how the data set relates to the result set. – Strawberry Nov 12 '15 at 14:06
  • i guess id's? they appeared when i imported the data from the mysql database into R – Bergrebell Nov 12 '15 at 14:11
  • I'm pretty new to R so forgive me if I'm wrong! – Bergrebell Nov 12 '15 at 14:11
  • You guess? How do you know the 'sensors' are sensors, and the values are 'values'? (I should point out that I know nothing about r - so if it's something internal to r then sorry for the interrogation!) – Strawberry Nov 12 '15 at 14:11
  • See `reshape2::dcast` or `tidyr::spread`. This is a duplicate of many other questions. – C8H10N4O2 Nov 12 '15 at 14:15
  • 1
    Possible duplicate of [R: Pivoting using 'spread' function](http://stackoverflow.com/questions/29952517/r-pivoting-using-spread-function) – C8H10N4O2 Nov 12 '15 at 14:17
  • Thank you for your possible duplicate! I try to figure out if that's the case! Maybe it isn't working because the variables were imported as characters? – Bergrebell Nov 12 '15 at 14:54

1 Answers1

1
d <- read.table(header=TRUE, text=
's value date
sensor1   3   8:25
sensor2   5   8:25
sensor1   4   9:01
sensor2   8   9:01')
xtabs(value~date+s, data=d)
jogo
  • 12,469
  • 11
  • 37
  • 42
  • thanks for your answer! but i get the error: "Error in FUN(X[[i]], ...) : invalid 'type' (character) of argument" when I do: > xtabs(Value~Date+Sensor, data=data) – Bergrebell Nov 12 '15 at 14:44
  • It seems that for your data `data$Value` is not `numeric`. Explore `str(data)` and try to convert. – jogo Nov 12 '15 at 14:47
  • That seems to be the case! I'll try to edit it! thanks! – Bergrebell Nov 12 '15 at 14:50