Start with a messy file
Real files look like this: dates as text in day/month/year, two missing days and one blank value.
import numpy as np
import pandas as pd
rng = np.random.default_rng(42)
days = pd.date_range("2024-01-01", periods=365, freq="D")
trend = np.linspace(100, 140, 365) # sales slowly grow
week = np.array([-8, -6, -4, 0, 6, 16, 12])[days.dayofweek] # busy weekends
s = pd.Series(trend + week + rng.normal(0, 4, 365), index=days, name="sales")
raw = s.reset_index()
raw.columns = ["date", "sales"]
raw["date"] = raw["date"].dt.strftime("%d/%m/%Y") # dates as text, like a CSV
raw = raw.drop(index=[10, 11]) # two missing days
raw.loc[40, "sales"] = np.nan # one blank value
raw.head()Fix the dates and the index
Turn the text into real dates, make the date the index, and tell pandas the frequency. asfreq("D") adds the missing days back as blanks, so every day is present once.
raw["date"] = pd.to_datetime(raw["date"], format="%d/%m/%Y")
clean = raw.set_index("date")["sales"].asfreq("D")
clean.isna().sum() # 3 blanks: 2 missing days + 1 blank valueFill the gaps
interpolate() draws a straight line across the gap. That is fine for a few missing points. For a long gap, ask why the data is missing instead of guessing.
clean = clean.interpolate()
clean.isna().sum() # 0Change the time step and smooth the noise
resample() changes the frequency. Daily sales summed by week are easier to read. rolling() averages the last few days and smooths the noise away.
weekly = clean.resample("W").sum() # one number per week
smooth = clean.rolling(7).mean() # average of the last 7 days
weekly.head(3)You should see
date 2024-01-07 709.6 2024-01-14 726.6 2024-01-21 727.7