主页 / 问题 / 1236659
Accepted
VEL
Asked:2022-01-27 21:27:15 +0000 UTC2022-01-27 21:27:15 +0000 UTC

如何对数据框进行排序和结构化？

  • 772

有一个 DataFrame。

如何排序和构造它？

import pandas as pd

# загрузка XLSX 
df = pd.read_excel('test.xlsx', 'Лист1', parse_dates=['Date'], index_col='Date')

df
Out[50]: 
             data  Year      month
Date                              
2018-01-31  28523  2018    January
2018-02-28  24395  2018   February
2018-03-31   2103  2018      March
2018-04-30  14450  2018      April
2018-05-31  -1903  2018        May
2018-06-30  -3112  2018       June
2018-07-31  13752  2018       July
2018-08-31   3892  2018     August
2018-09-30  26652  2018  September
2018-10-31 -12245  2018    October
2018-11-30   7437  2018   November
2018-12-31 -10296  2018   December
2019-01-31  32621  2019    January
2019-02-28   4176  2019   February
2019-03-31   7339  2019      March
2019-04-30   6582  2019      April
2019-05-31   8254  2019        May
2019-06-30  21825  2019       June
2019-07-31 -17253  2019       July
2019-08-31  -2725  2019     August
2019-09-30  35000  2019  September
2019-10-31  21760  2019    October
2019-11-30  -2458  2019   November
2019-12-31  17852  2019   December
2020-01-31  12260  2020    January
2020-02-29   2349  2020   February
2020-03-31  58246  2020      March
2020-04-30  43770  2020      April
2020-05-31  21801  2020        May
2020-06-30   7011  2020       June
2020-07-31   5521  2020       July
2020-08-31  13734  2020     August
2020-09-30   8150  2020  September
2020-10-31  -3778  2020    October
2020-11-30  39035  2020   November
2020-12-31  17447  2020   December

您需要得到以下结果：

在此处输入图像描述

python

2 个回答

  1. Best Answer
    res = df.assign(m=df["Date"].dt.month).pivot_table(index="Year", columns="m", 
                values="data", fill_value=0, aggfunc="sum")
res.columns = pd.to_datetime(res.columns, format="%m").month_name()

    结果：

    In [62]: res
Out[62]: 
m     January  February  March  April    May   June   July  August  September  October  November  December
Year                                                                                                      
2018    28523     24395   2103  14450  -1903  -3112  13752    3892      26652   -12245      7437    -10296
2019    32621      4176   7339   6582   8254  21825 -17253   -2725      35000    21760     -2458     17852
2020    12260      2349  58246  43770  21801   7011   5521   13734       8150    -3778     39035     17447
    • 4

  2. 这是另一种选择：

    import calendar
res = pd.pivot_table(df, index="Year", values="data", columns=df.index.month)
res.columns = [calendar.month_name[x] for x in res.columns]

    资源：

          January  February  March  April  ...  September  October  November  December
Year                                   ...                                        
2018    28523     24395   2103  14450  ...      26652   -12245      7437    -10296
2019    32621      4176   7339   6582  ...      35000    21760     -2458     17852
2020    12260      2349  58246  43770  ...       8150    -3778     39035     17447
    • 3

相关问题