CODEWITHSUNDEEP
rxtsstockscbind
Jason
Jason

Reputation: 311

How to bind multiple data sets with different number of rows by the first row using R

I am trying to bind multiple stocks by "TimeStamp" which is the 1st column on the dataset. The rest of the columns are Open, High, Low, Close, Volume. One of the problems is that they all contain different rows.The second problem is that the "TimeStamp" is numerical & I cannot figure out how to convert it to an appropriate "TIME". All I know is that they are all 1-minute intervals, & am given ranges for each day:

These ranges are all the same for all the stocks I want to bind. 

DATE            BEGINNING   END
range: 20131220 1387515600  1387601940
range:20131223  1387774800  1387861140
range:20131224  1387861200  1387947540
range:20131226  1388034000  1388120340

Here is an example of one stock:

Time        Open    High    Low     Close   Volume
1387519189  1.3635  1.3635  1.3632  1.3634  16300
1387519476  1.3633  1.3636  1.3632  1.3635  200
1387519798  1.3635  1.3635  1.3634  1.3634  200
1387520045  1.3635  1.3636  1.3635  1.3635  100
1387520392  1.3635  1.3636  1.3634  1.3635  100
1387520637  1.3636  1.3636  1.3635  1.3635  100
1387520977  1.3635  1.3636  1.3635  1.3636  100
1387521292  1.3637  1.3637  1.3635  1.3635  400

I have tried using cbind to bind all the stocks but an error occurs since they all contain different rows. I have tried converting each stock to an xts object but since I do not know how to break down the TIME I cannot do it. any suggestions? Thanks in advance!

Desired output:

`Time   Open   High    Low   Close  Volume`     `Time   Open High Low Close Volume`

1387519189 1.3635 1.3635 1.3632 1.3634 16300 1387519189 35.5 35.90 35.4 35.5 100

1387519476 1.3633 1.3636 1.3632 1.3635 200 1387519476 35.6 35.6 35.40 35.5 100

1387519798 1.3635 1.3635 1.3634 1.3634 200 1387519798 35.8 35.95 35.4 35.5 100

Upvotes: 2

Views: 674

Answers (2)

jlhoward
jlhoward

Reputation: 59395

Regarding your question about converting timestamps to time, it looks like these might be in POSIX format (seconds since 1970-01-01). Calling your example df,

date.time <- as.POSIXct(df$Time, origin="1970-01-01")
time.only <- format(date.time,"%H:%M:%S")
df.times  <- data.frame(Time=df$Time, date.time, time.only)
df.times
#         Time           date.time time.only
# 1 1387519189 2013-12-20 00:59:49  00:59:49
# 2 1387519476 2013-12-20 01:04:36  01:04:36
# 3 1387519798 2013-12-20 01:09:58  01:09:58
# 4 1387520045 2013-12-20 01:14:05  01:14:05
# 5 1387520392 2013-12-20 01:19:52  01:19:52
# 6 1387520637 2013-12-20 01:23:57  01:23:57
# 7 1387520977 2013-12-20 01:29:37  01:29:37
# 8 1387521292 2013-12-20 01:34:52  01:34:52

These are not 1 minute intervals, though.

Upvotes: 1

americo
americo

Reputation: 1063

What you want to use is plyr's join command.

join(x, y, by = "Time", type = "full")

Upvotes: 1

Related Questions