Create columns in a data frame from values of a variable

Question

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)

What are '1,2,3,...'??? And I can't see how the data set relates to the result set. — Strawberry
– Strawberry, Commented Nov 12, 2015 at 14:06
i guess id's? they appeared when i imported the data from the mysql database into R — Dennis Nedry
– Dennis Nedry, Commented Nov 12, 2015 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
– Strawberry, Commented Nov 12, 2015 at 14:11

jogo · Accepted Answer · 2015-11-12 14:37:25Z

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)

answered Nov 12, 2015 at 14:37

jogo

12.6k11 gold badges41 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dennis Nedry Over a year ago

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)

jogo Over a year ago

It seems that for your data data$Value is not numeric. Explore str(data) and try to convert.

Dennis Nedry Over a year ago

That seems to be the case! I'll try to edit it! thanks!

Collectives™ on Stack Overflow

Create columns in a data frame from values of a variable

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related