dayum dayum - 8 days ago 5
Python Question

Converting and transposing a groupby object into dataframe

I have a pandas df:

Reading
2016-06-01 09:00:00+09:00 1190.958
2016-06-01 10:30:00+09:00 1189.886
2016-06-01 12:00:00+09:00 1194.089
2016-06-01 13:30:00+09:00 1193.464
2016-06-01 15:00:00+09:00 1193.050
2016-06-02 09:00:00+09:00 1190.879
2016-06-02 12:00:00+09:00 1190.025
2016-06-02 13:30:00+09:00 1187.057
2016-06-02 15:00:00+09:00 1186.600


I want to transform it like this:

09:00:00+09:00 10:30:00+09:00 12:00:00+09:00 13:30:00+09:00 15:00:00+09:00
2016-06-01 1190.958 1189.886 1194.089 1193.464 1193.050
2016-06-02 1190.879 NA 1190.025 1187.057 1186.600


I thought I can group the data by date like this:

df.groupby(df.index.values.astype('<M8[D]'))


But how do i convert this groupby object into a dataframe with transposed columns?

Answer

reassign your index with date and time of the current index and unstack

df.index = [df.index.date, df.index.time]
df.Reading.unstack()

enter image description here