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

Question

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..

There are multiple steps involved in this, so which ones do you need help with exactly? Do you know [how to pivot](/q/47152691/how-can-i-pivot-a-dataframe)? Do you know how to [convert a datetime to a month name](/q/37625334/python-pandas-convert-month-int-to-month-name)? Please [edit] to add what you've already tried. — wjandrea, Jan 09 '23 at 20:19

score 1 · Answer 1 · edited Jan 09 '23 at 20:39

1

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

edited Jan 09 '23 at 20:39

wjandrea

28,235
9
60
81

answered Jan 09 '23 at 20:20

Tom Ron

5,906
3
22
38

Is there a reason to use `pd.pivot_table(data=df, ...)` instead of [`df.pivot_table(...)`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.pivot_table.html)? – wjandrea Jan 09 '23 at 20:39
No, you are correct – Tom Ron Jan 10 '23 at 11:17

score 0 · Answer 2 · answered Jan 09 '23 at 20:23

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

bayes2021 · Answer 3 · 2023-01-09T20:34:40.700

0

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

edited Jan 09 '23 at 20:34

answered Jan 09 '23 at 20:33

bayes2021

202
1
7

There's no need to write your own list of month names. [Pandas can do that for you](/a/53238039/4518341). – wjandrea Jan 09 '23 at 20:36
But this would require an additional package. – bayes2021 Jan 09 '23 at 20:51
You're already using Pandas... – wjandrea Jan 10 '23 at 00:12
@wjandrea I'm not talking about no imports but `calendar` is not necessary. – bayes2021 Jan 10 '23 at 06:24
The answer I linked to doesn't use `calendar` – wjandrea Jan 10 '23 at 19:19

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

3 Answers3