MediumPandas

Monthly sales summary

Given a DataFrame with columns date (string, dd-mm-yyyy) and amount, return total amount per month as a DataFrame with columns month and total.

This one comes straight from the kind of work I do every week. Someone exports sales from their billing software, the dates come out as text in Indian dd-mm-yyyy format, and they want a month-wise total.

Sample input:

df = pd.DataFrame({
    "date":   ["05-01-2026", "28-01-2026", "03-02-2026", "15-02-2026", "02-03-2026"],
    "amount": [1200, 800, 1500, 500, 2000],
})

Expected output:

month	total
2026-01	2000
2026-02	2000
2026-03	2000

Watch out: pandas assumes month-first dates by default. Without telling it otherwise, 05-01-2026 becomes 1 May instead of 5 January, and your report is quietly wrong.

Starter code

import pandas as pd

def monthly(df):
    pass

💡 Hint 1

pd.to_datetime(dayfirst=True)

💡 Hint 2

Use dt.to_period('M')

✅ Show solution

def monthly(df):
    d = df.copy()
    d['date'] = pd.to_datetime(d['date'], dayfirst=True)
    return (d.groupby(d['date'].dt.to_period('M'))['amount']
             .sum().rename('total').reset_index()
             .rename(columns={'date': 'month'}))

How it works, step by step

df.copy() so we don't change the caller's DataFrame. Small habit, saves real bugs.
pd.to_datetime(..., dayfirst=True) reads 05-01-2026 as 5 January. For mixed or messy inputs add errors="coerce", which turns anything unreadable into NaT so you can find it, instead of crashing.
.dt.to_period('M') turns each date into its month, like 2026-01. Grouping by that collapses every day in a month together.
.sum() adds up the amounts, then the renames give the exact column names asked for.

Another way: resample

If the date is the index, resample does the same job and fills in months with no sales as zero:

d = df.assign(date=pd.to_datetime(df["date"], dayfirst=True)).set_index("date")
d["amount"].resample("MS").sum()

That gap-filling matters for charts. A month with no sales should show as zero, not quietly disappear.

Things that go wrong on real exports

Amounts come in as text like "1,200.00". Fix with pd.to_numeric(df["amount"].str.replace(",", "")).
A "Grand Total" row at the bottom gets added in as if it were a sale. Drop it before grouping.
Some rows have the date in a different format. errors="coerce" plus df[df["date"].isna()] shows you exactly which ones.

Tip: run your code in a local Python environment, Jupyter or Google Colab.