大Pandas从datetime索引从长到宽

Question

我有一个带有日期索引和两列的数据框：

            val     week
2015-01-02  16729   1
2015-01-09  16225   2
2015-01-16  15250   3
2015-01-23  15690   4
2015-01-30  16025   5
...         ...     ...
2020-03-20  16417   12
2020-03-27  15481   13
2020-04-03  14216   14
2020-04-10  13113   15
2020-04-17  12825   16

我想做的是以年为轴心或分组，然后以月和周作为指数

            2015    ...    2020
01-1        16729   ...    ...
01-2        16225   ...    ...
01-3        15250   ...    ...
01-4        15690   ...    ...
01-5        16025   ...    ...
...         ...     ...    ...
03-12       ...     ...    16417
03-13       ...     ...    15481
04-14       ...     ...    14216
04-15       ...     ...    13113
04-16       ...     ...    12825

最好只保留月份和日期作为指数，但由于这是一个每周频率，因此一周的实际日期在不同年份有所不同。如果有办法把日期汇总起来，那么准确的日期就不那么重要了

            2015    ...    2020
01-02       16729   ...    ...
01-09       16225   ...    ...
01-16       15250   ...    ...
01-23       15690   ...    ...
01-30       16025   ...    ...
...         ...     ...    ...
03-20       ...     ...    16417
03-27       ...     ...    15481
04-03       ...     ...    14216
04-10       ...     ...    13113
04-17       ...     ...    12825

我已经尝试过使用pd.Grouper和groupby的变体，但似乎无法正确使用。对于如何安排这一点，我也愿意接受其他建议，因为我的想法是每年在同一直线图上绘制一条单独的直线

Answer 1

在所有的评论之后，似乎是时候编写一些代码了。有点不对劲，但也许这会对你有所帮助：

import numpy as np
import pandas as pd

# example df with some random values.
df = pd.DataFrame({'t': ['2015-01-02','2015-01-03','2015-01-16','2015-01-23','2015-01-30', '2020-01-01'],
                   'val': [16729, 16225, 15250, 15690, 16025, 999],
                   'week': [1, 2, 3, 4, 5, 1]})
df['t'] = pd.to_datetime(df['t'])

# pivot to get years as columns
df1 = pd.pivot_table(df, values='val', columns=df['t'].dt.year, index=df['t'])

# create a new column "date" for later on... cast to datetime object for now
df1['date'] = pd.to_datetime(df1.index.date)

# sum the values for every week and drop the original "t" (datetime) column
df2 = df1.groupby(df1.index.week).resample('W-Mon', on='date').sum().reset_index().sort_values(by='date').drop(columns=['t'])

# drop all rows that only hold zeros
df2 = df2.loc[~np.isclose(df2.loc[:, df2.columns != 'date'], 0)]

# finally, format the datetime column to string as desired
df2['month-week'] = df2['date'].dt.strftime('%m-%W')