Anagha Anagha - 2 years ago 62
R Question

Converting rows to columns for a dataframe in R

I have a dataframe as below.

Date Time Object_Name Object_Value
7/28/2017 8:00 A1 58.56
7/28/2017 8:00 A2 51.66
.
.
.
7/28/2017 8:30 A1 60.2
7/28/2017 8:30 A2 65.2


I need it to be transformed like this:

Date Time A1 A2
7/28/2017 8:00 58.5 51.6
7/28/2017 8:30 60.2 65.2

Answer Source

Try

library(reshape2)
df
       Date Time Object_Name Object_Value
1 7/28/2017 8:00          A1        58.56
2 7/28/2017 8:00          A2        51.66
3 7/28/2017 8:30          A1        60.20
4 7/28/2017 8:30          A2        65.20

dcast(df, Date + Time ~ Object_Name)

       Date Time    A1    A2
1 7/28/2017 8:00 58.56 51.66
2 7/28/2017 8:30 60.20 65.20

Alternatively,

library(tidyr)
spread(df, Object_Name, Object_Value)
       Date Time    A1    A2
1 7/28/2017 8:00 58.56 51.66
2 7/28/2017 8:30 60.20 65.20

To address the comment, the above works well if you have unique cases. Consider for instance the following:

df
       Date Time Object_Name Object_Value
1 7/28/2017 8:00          A1        58.56
2 7/28/2017 8:00          A1        50.00
3 7/28/2017 8:00          A2        51.66
4 7/28/2017 8:30          A1        60.20
5 7/28/2017 8:30          A2        65.20

Look at the first two rows, and you can see that for the same date, time and Object_Name, we have two values. This implies that dcast does not know what to do and gives the following warning: Aggregation function missing: defaulting to length. We can handle this by specifying the aggregation function. For instance, let's take the mean of these values:

dcast(df, Date + Time ~ Object_Name, fun.aggregate = mean)
       Date Time    A1    A2
1 7/28/2017 8:00 54.28 51.66
2 7/28/2017 8:30 60.20 65.20
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download