CODEWITHSUNDEEP
python-3.xpandascsv
IamTheWalrus
IamTheWalrus

Reputation: 604

Transform CSV structure with pandas dataframe

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?

Upvotes: 0

Views: 348

Answers (1)

jezrael
jezrael

Reputation: 862661

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

Upvotes: 1

Related Questions