-1

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
Anagha
  • 3,073
  • 8
  • 25
  • 43

1 Answers1

2

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
coffeinjunky
  • 11,254
  • 39
  • 57
  • I am getting values as 0 and 1, should I explicitly specify for object_Value? – Anagha Aug 16 '17 at 10:23
  • Warning : Aggregation function missing: defaulting to length. and all the values are 0 and 1 – Anagha Aug 16 '17 at 10:25
  • Could the downvoter explain the downvote, please? – coffeinjunky Aug 16 '17 at 10:26
  • I want each value for each object name with respective date and time. In my dataframe I have 218 unique values(Object name) in the interval of 15 mins. Hence i need object value for each of these 218 object name for every 15 mins dated from 6/6/2017 to 7/1/2017. – Anagha Aug 16 '17 at 10:30
  • Yes, but i do not want to take mean. For each of 218 unique object names, I have different values for each date and time. As I explained above. – Anagha Aug 16 '17 at 10:34
  • If you had no repetitions, you would not get that error message. Check your data again. For instance, try `duplicated(df[, c("Date", "Time", "Object_Name")])`. My guess is that there will be duplicates. – coffeinjunky Aug 16 '17 at 10:35
  • Yes, there might be duplicates for object value. for example: on 6/6/2017 for the object name A1 at time 8:00 the value would be 56.5 Similarly on 6/6/2017 for the object name A2 at time 10:15 the value would be 56.6 or it is also possible on 6/6/2017 for the object name A1 at time 8:15 the value would be 56.5 – Anagha Aug 16 '17 at 10:41
  • I'm getting NaN and NA. I understood NaN, since there is no object value at that particular time. But I'm not sure why NAs are coming – Anagha Aug 16 '17 at 10:46
  • In any case, my answer shows you how to reshape your data so that rows become columns, using the data you provided. You need to make up your mind how to deal with duplicate entries. Note that you can supply whatever aggregation function you wish. For instance, if you want to keep the first occurrence, `function(x) x[1]` works, etc. If you want to keep all, use a workaround such as adding a unique identifier for each row. – coffeinjunky Aug 16 '17 at 10:48
  • I need to deal with both numbers and characters in object value. Hence i'm getting NAs. If i convert the object_Value column to Numeric, i'm getting result, but for non-numeric i'm getting NAs If i keep the object_value column as character. I am getting all NAs. Please let me know how to deal with both numbers and characters – Anagha Aug 16 '17 at 10:57
  • I believe its related to the above question. You asked me to use aggregate function, and i'm not sure what function to use if we have both numeric and character. Anyway, thank you for the help, I will open a new question – Anagha Aug 16 '17 at 11:05