how to get the sum of a dataframe by month/year-CodePudding

I have a data frame that consists of :

DATE	X.
1982-09-30 00:00:00	0
1982-10-31 00:00:00	-0.75
1982-11-30 00:00:00	-0.5
1982-12-31 00:00:00	-0.5
1983-01-31 00:00:00	0
...	0.8
2022-01-09 00:00:00	0.8

From this dataframe, I would like to have a table with this format :

	January	...	December
1982	1	0.5	-1.0
1983	1	0.5	-1.0
...	1	0.5	-1.0
2022	1	0.5	-1.0

where each number inside the table is the sum of line/column intersection ie for January/1982 : the sum of the data for January 1982, etc..

CodePudding user response：

I would try using pivot_table for example -

pd.pivot_table(
    data=df,
    index=df.date.dt.year,
    columns=df.date.dt.month,
    values="x",
    aggfunc=sum)

date   1    9     10   11   12
date                          
1982  NaN  0.0 -0.75 -0.5 -0.5
1983  0.0  NaN   NaN  NaN  NaN
2022  0.8  NaN   NaN  NaN  NaN

CodePudding user response：

Using a crosstab:

s = pd.to_datetime(df['DATE'])

out = pd.crosstab(s.dt.year, s.dt.month_name(), values=df['X'], aggfunc='sum').fillna(0)

Output:

DATE  December  January  November  October  September
DATE                                                 
1982      -0.5      0.0      -0.5    -0.75        0.0
1983       0.0      0.0       0.0     0.00        0.0
2022       0.0      0.8       0.0     0.00        0.0

CodePudding user response：

The following snippets outputs the month in the correct order.

cats = [
    "Jan",
    "Feb",
    "Mar",
    "Apr",
    "May",
    "Jun",
    "Jul",
    "Aug",
    "Sep",
    "Oct",
    "Nov",
    "Dec",
]
df = pd.DataFrame(
    {
        "date": pd.date_range("1986-01-01", freq="d", periods=365 * 4),
        "value": [i for i in range(365 * 4)],
    }
)
df = df.assign(month=lambda df: df.date.dt.month, year=lambda df: df.date.dt.year)
df = (
    df.groupby(["year", "month"])
    .sum()
    .unstack()
    .rename(columns={k: v for k, v in zip(range(1, 13), cats)})
)

Output:

       value                                                                             
month    Jan    Feb    Mar    Apr    May    Jun    Jul    Aug    Sep    Oct    Nov    Dec
year                                                                                     
1986     465   1246   2294   3135   4185   4965   6076   7037   7725   8928   9555  10819
1987   11780  11466  13609  14085  15500  15915  17391  18352  18675  20243  20505  22134
1988   23095  22475  24955  25065  26846  26895  28737  29698  29655  31589  31485  33480
1989   34441  31934  36270  36015  38161  37845  40052  41013  40605  42904  42435  43335