pandas get closing day price from time series data

Question

I have some time series data in a pandas data frame:

 prices.head()
                 Time   A        B      C      D
0 2012-01-02 08:00:30 NaN  47.1650  31.51  58.16
1 2012-01-02 08:01:00 NaN  47.2400  31.48  58.19
2 2012-01-02 08:01:30 NaN  47.2750  31.46  58.21
3 2012-01-02 08:02:00 NaN  47.3250  31.40  58.17
4 2012-01-02 08:02:30 NaN  47.3325  31.42  58.07

I would like to create 4 new columns containing the closing price for each day. How can I do that?

The samples associated to day 1 should have the closing price of day 1 and so on ...

Please post desired data set (DF). [How to make good reproducible pandas examples](http://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) — MaxU - stand with Ukraine, Aug 19 '16 at 20:29

score 0 · Answer 1 · answered Aug 20 '16 at 02:20

You can groupby date, and take last one of each group then join back.

df['date'] = df.Time.dt.date
print df.join(df.groupby('date')[['A','B','C','D']].last(), rsuffix='_close', on='date')

                 Time   A        B      C      D        date  A_close  \
0 2012-01-02 08:00:30 NaN  47.1650  31.51  58.16  2012-01-02      NaN   
1 2012-01-02 08:01:00 NaN  47.2400  31.48  58.19  2012-01-02      NaN   
2 2012-01-02 08:01:30 NaN  47.2750  31.46  58.21  2012-01-02      NaN   
3 2012-01-02 08:02:00 NaN  47.3250  31.40  58.17  2012-01-02      NaN   
4 2012-01-02 08:02:30 NaN  47.3325  31.42  58.07  2012-01-02      NaN   

   B_close  C_close  D_close  
0  47.3325    31.42    58.07  
1  47.3325    31.42    58.07  
2  47.3325    31.42    58.07  
3  47.3325    31.42    58.07  
4  47.3325    31.42    58.07

pandas get closing day price from time series data

1 Answers1