How can I rearrange loaded time series data?

Question

What I've got is data in a txt file (mydata.txt) something like this:

Variable, DateTime, Value, Quality
A, 01-01-1970 00:00:00, 0, 0
A, 01-01-1970 00:02:00, 2, 2
A, 01-01-1970 00:04:00, 4, 1
A, 01-01-1970 00:06:00, 6, 0
B, 01-01-1970 00:02:00, 0.2, 0
B, 01-01-1970 00:04:00, 0.4, 1
B, 01-01-1970 00:06:00, 0.6, 1
B, 01-01-1970 00:10:00, 1.0, 0
C, 01-01-1970 00:00:00, 20.0, 0
C, 01-01-1970 00:04:00, 16.0, 0
C, 01-01-1970 00:08:00, 12.0, 3

And I can load it into R without problems with

read.csv("mydata.txt", header = TRUE, sep = ",")

or

read.table("mydata.txt", header = TRUE, sep = ",")

But what I'd like to work with in r would be something like this:

DateTime, A_Value, A_Quality, B_Value, B_Quality, C_Value, C_Quality
01-01-1970 00:00:00, 0, 0, NA, NA, 20.0, 0
01-01-1970 00:02:00, 2, 2, 0.2, 0, NA, NA
01-01-1970 00:04:00, 4, 1, 0.4, 1, 16.0, 0
01-01-1970 00:06:00, 6, 0, 0.6, 1, NA, NA
01-01-1970 00:08:00, NA, NA, NA, NA, 12.0, 3
01-01-1970 00:10:00, NA, NA, 1.0, 0, NA, NA

(where the first column is a date/time type).

I don't know which or how many different variables there are in my file (i.e. A, B, ... Z) and I don't know their names - all I know is their column.

How do I get from the data set I have in the text file to to the data set I'd like to work with in R?

Thanks in advance!

Answer 1

Read your data in as normal:

mydata <- read.table("mydata.txt", header = TRUE, sep = ",")

Then "reshape" it from what is called a "long" format to a "wide" format using one of several methods.

This is just 1 line in base R using reshape:

reshape(mydata, direction = "wide", idvar = "DateTime", timevar = "Variable")
#                DateTime Value.A Quality.A Value.B Quality.B Value.C Quality.C
# 1   01-01-1970 00:00:00       0         0      NA        NA      20         0
# 2   01-01-1970 00:02:00       2         2     0.2         0      NA        NA
# 3   01-01-1970 00:04:00       4         1     0.4         1      16         0
# 4   01-01-1970 00:06:00       6         0     0.6         1      NA        NA
# 8   01-01-1970 00:10:00      NA        NA     1.0         0      NA        NA
# 11  01-01-1970 00:08:00      NA        NA      NA        NA      12         3

Answer 2

You can do this using the reshape2 package:

First step: melt your data.frame

require(reshape2)
df.m <- melt(df, id.var = 1:2) # changed names(df)[1:2] to 1:2 (following @Anandamahto's comment)

Second step: cast to the result:

dcast(df.m, DateTime ~ Variable + variable, fill=NA)

#               DateTime A_Value A_Quality B_Value B_Quality C_Value C_Quality
# 1  01-01-1970 00:00:00       0         0      NA        NA      20         0
# 2  01-01-1970 00:02:00       2         2     0.2         0      NA        NA
# 3  01-01-1970 00:04:00       4         1     0.4         1      16         0
# 4  01-01-1970 00:06:00       6         0     0.6         1      NA        NA
# 5  01-01-1970 00:08:00      NA        NA      NA        NA      12         3
# 6  01-01-1970 00:10:00      NA        NA     1.0         0      NA        NA