My CSV contains rows such as:
entryTime entryPrice exitTime exitPrice
06/01/2009 04:00 93.565 06/01/2009 06:00 93.825
I want to load them into a Dataframe that will have two rows per CSV row, in the following format:
datetime signal price
06/01/2009 04:00 entry 93.565
06/01/2009 06:00 exit 93.825
indexed by datetime column. What would be a fast way to do it?
Use numpy.tile with numpy.ravel:
print (df)
entryTime entryPrice exitTime exitPrice
0 01/01/2009 04:00 90.565 02/01/2009 06:00 91.825
1 03/01/2009 04:00 92.565 04/01/2009 06:00 93.825
2 05/01/2009 04:00 94.565 06/01/2009 06:00 95.825
3 07/01/2009 04:00 96.565 08/01/2009 07:00 97.825
4 09/01/2009 04:00 98.565 10/01/2009 06:00 99.825
a = np.tile(['entry','exit'], len(df))
b = df[['entryTime','exitTime']].values.ravel()
c = df[['entryPrice','exitPrice']].values.ravel()
df = pd.DataFrame({'price':c, 'signal':a},
index=pd.to_datetime(b),
columns=['signal','price'])
print (df)
signal price
2009-01-01 04:00:00 entry 90.565
2009-02-01 06:00:00 exit 91.825
2009-03-01 04:00:00 entry 92.565
2009-04-01 06:00:00 exit 93.825
2009-05-01 04:00:00 entry 94.565
2009-06-01 06:00:00 exit 95.825
2009-07-01 04:00:00 entry 96.565
2009-08-01 07:00:00 exit 97.825
2009-09-01 04:00:00 entry 98.565
2009-10-01 06:00:00 exit 99.825
