0

This Data Frame DF: 

 Stock      Date      Time     Price     Open      
   AAA   2002-02-23  10:13     2.440     0.01    
   AAA   2002-02-27  17:17     2.460     0.02

Becomes :Transformed 

   Stock   Date      Time_0    Price_0   Open_0  Time_1  Price_1  Open_1     
   AAA   2002-02-23  10:13     2.440     0.01    17:17    2.460    0.02
   AAA   2002-02-27  17:17     2.460     0.02    NA       NA       NA

I want to apply the above operation for a larger data set is there an efficient way to do this? (The images have a more detailed representation)

EDIT : Solution How to create a lagged data structure using pandas dataframe This answers the question

Community
  • 1
  • 1
Nandu
  • 1
  • 1

1 Answers1

0

Data Setup:

df = pd.DataFrame({'Stock': {0: 'AAA', 1: 'AAA', 2: 'AAA'},
 'Date': {0: '2002-02-23', 1: '2002-02-27', 2: '2002-02-27'},
 'Time': {0: '10:13', 1: '17:17', 2: '17:17'},
 'Price': {0: 2.44, 1: 2.46, 2: 3.2},
 'Open': {0: 0.01, 1: 0.02, 2: 0.02} 
 })
#Reorder columns
df = df[['Stock','Date','Time','Price','Open']]
df
Out[1221]: 
  Stock        Date   Time  Price  Open
0   AAA  2002-02-23  10:13   2.44  0.01
1   AAA  2002-02-27  17:17   2.46  0.02
2   AAA  2002-02-27  17:17   3.20  0.02

Solution:

#get the 'Time', 'Price','Open' fileds from the next row and create a new dataframe
df_1 = df.apply(lambda x: df.ix[x.name+1][['Time', 'Price','Open']] if (x.name+1) < len(df) else np.nan , axis=1)

#join the original df and the new df
df.join(df_1,lsuffix='_0',rsuffix='_1')
Out[1223]: 
  Stock        Date Time_0  Price_0  Open_0 Time_1  Price_1  Open_1
0   AAA  2002-02-23  10:13     2.44    0.01  17:17     2.46    0.02
1   AAA  2002-02-27  17:17     2.46    0.02  17:17     3.20    0.02
2   AAA  2002-02-27  17:17     3.20    0.02    NaN      NaN     NaN

With the OP's original data, the output would be:

Out[1270]: 
  Stock        Date Time_0  Price_0  Open_0 Time_1  Price_1  Open_1
0   AAA  2002-02-23  10:13     2.44    0.01  17:17     2.46    0.02
1   AAA  2002-02-27  17:17     2.46    0.02    NaN      NaN     NaN
Allen Qin
  • 19,507
  • 8
  • 51
  • 67
  • You helped me spot a mistake in my transformed data frame picture I've updated it ...The answer you've given is kinda what i'm looking for but there should be even the third column added to the first row...So the first row should return Stock Date Time_0 Price_0 Open_0 Time_1 Price_1 Open_1 Time_2 Price_2 Open_2 ...... http://stackoverflow.com/questions/22798934/pandas-long-to-wide-reshape kinda like how this data frame is transformed without the aggregation...Unable to display the table here attached it the question..Thanks for the help – Nandu May 04 '17 at 21:07
  • The example data I use is different from yours. The data I use has 3 rows already. I just want to test if it works with more than 2 rows. If you test it using your data, the output will be exactly the same as your expected output. – Allen Qin May 04 '17 at 21:11
  • How can I Scale it to N rows and N columns? – Nandu May 05 '17 at 13:58
  • This solution already handles multiple rows. To handle multiple columns, it needs a different solution. Please raise a new question if you need that. – Allen Qin May 05 '17 at 22:44