将日期索引的数据帧拆分为包含相关值的每月列的数据帧

Question

我有一个包含每月降雨量值的数据框，按日期索引。这是一个简短的片段：

      date  ppt

      ...

2016-11-30  253.379993

2016-12-31  52.709998

2017-01-31  9.030000

2017-02-28  10.050000

2017-03-31  16.560000

2017-04-30  45.509998

2017-05-31  103.829997

      ...

我需要为每个月生成一个包含列的新数据框，其中包含 syudy 期间（20 年）内每个相关月份的值。

例如..

    Jan   Feb    Mar   Apr   May  ...  

0   

1

2

3

4

5

...

任何人都可以帮忙吗？

谢谢！

编辑：

到目前为止我已经尝试过这个：

df.pivot_table(index=df.date.dt.year, columns=df.date.dt.month, values='ppt')

但它返回这个错误，尽管我明确地将索引设置为日期时间......

---------------------------------------------------------------------------

AttributeError                            Traceback (most recent call last)

<ipython-input-49-9cc1ff01344d> in <module>()

     29 Trmm_1M.index = pd.to_datetime(Trmm_1M.index)

     30 

---> 31 TRMM_MONTHS = Trmm_1M.pivot_table(index=Trmm_1M.date.dt.year, columns=Trmm_1M.date.dt.month, values='ppt3')

 32 

 33 # Trmm_1M

~/anaconda3/lib/python3.6/site-packages/pandas/core/generic.py in __getattr__(self, name)

   3079             if name in self._info_axis:

   3080                 return self[name]

-> 3081             return object.__getattribute__(self, name)

   3082 

   3083     def __setattr__(self, name, value):

AttributeError: 'DataFrame' object has no attribute 'date'

Answer 1

为月份和年份分配一列，并使用pivot：df.assign(month=df.date.dt.month,year=df.date.dt.year).pivot('year','month','ppt')在您的情况下，这给出：month&nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12year&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2016&nbsp; &nbsp; NaN&nbsp; &nbsp; NaN&nbsp; &nbsp; NaN&nbsp; &nbsp; &nbsp; &nbsp; NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; 253.379993&nbsp; 52.7099982017&nbsp; &nbsp;9.03&nbsp; 10.05&nbsp; 16.56&nbsp; 45.509998&nbsp; 103.829997&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; NaN

Answer 2

使用pivot_table：df.pivot_table(index=df.date.dt.year, columns=df.date.dt.month, values='ppt')date&nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12date2016&nbsp; &nbsp;NaN&nbsp; &nbsp; NaN&nbsp; &nbsp; NaN&nbsp; &nbsp; &nbsp; &nbsp; NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; 253.379993&nbsp; 52.7099982017&nbsp; 9.03&nbsp; 10.05&nbsp; 16.56&nbsp; 45.509998&nbsp; 103.829997&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; NaN

Answer 3

在无法执行上述建议后，我设法找到了解决方法。虽然它涉及更多步骤，但无法找出问题所在的挫败感远远超过了额外代码行的努力......df['M'] = df.index.month&nbsp; #extracting month and years and creating new columnsdf['Y'] = df.index.yeardf_pivot = df.pivot_table(index=df.Y, columns=df.M, values='ppt') #pivot with these columns, rather than the index不过还是谢谢大家的建议：）