Pandas & NumPy Time Series Cleaning

pd.read_csv(path, parse_dates=['date_col'], index_col='date_col')
pd.read_excel(path, sheet_name=0, parse_dates=['date_col'])
df.info()                  # dtypes, nulls, memory
df.describe()               # summary stats
df.head() / df.tail()
df.shape
df.dtypes
df.columns.tolist()

pd.to_datetime(df['date'], format='%Y-%m-%d', errors='coerce')
df.set_index('date', inplace=True)
df.index = pd.to_datetime(df.index)

df.sort_index(inplace=True)          # CRITICAL before any TS op
df.index.is_monotonic_increasing     # sanity check

# Extract components
df['year']    = df.index.year
df['month']   = df.index.month
df['dow']     = df.index.dayofweek
df['quarter'] = df.index.quarter
df['is_month_end'] = df.index.is_month_end

df.isna().sum()                      # nulls per column
df.isna().mean() * 100               # % missing
df[df.isna().any(axis=1)]            # rows with any null

# Fill strategies (order matters for TS)
df.ffill()                           # forward fill — most common for TS
df.bfill()                           # backward fill
df.interpolate(method='linear')      # linear interpolation
df.interpolate(method='time')        # time-aware interpolation (uneven spacing)
df.fillna(df.mean())                 # mean imputation (use cautiously on TS)
df.dropna(subset=['target_col'])     # drop only if target is missing

df.duplicated().sum()
df.drop_duplicates(subset=['date', 'sku'], keep='last')
df.index.duplicated().sum()          # duplicate timestamps — common TS bug
df[df.index.duplicated(keep=False)]  # inspect duplicate timestamp rows

df.resample('D').sum()               # daily aggregation
df.resample('W').mean()
df.resample('M').agg({'sales':'sum','price':'mean'})
df.asfreq('D')                       # enforce frequency, introduces NaN for gaps
df.resample('D').asfreq()            # combine: regularize + expose gaps

pd.date_range(start, end, freq='D')  # build a complete calendar
df = df.reindex(pd.date_range(df.index.min(), df.index.max(), freq='D'))

# Z-score method
from scipy import stats
z = np.abs(stats.zscore(df['value'].dropna()))
df[z > 3]

# IQR method
Q1, Q3 = df['value'].quantile([0.25, 0.75])
IQR = Q3 - Q1
mask = (df['value'] < Q1 - 1.5*IQR) | (df['value'] > Q3 + 1.5*IQR)
df[mask]

# Rolling window z-score (better for TS with trend/seasonality)
roll_mean = df['value'].rolling(30).mean()
roll_std  = df['value'].rolling(30).std()
df['is_outlier'] = np.abs(df['value'] - roll_mean) > 3 * roll_std

df['value_capped'] = df['value'].clip(lower=Q1-1.5*IQR, upper=Q3+1.5*IQR)

df['col'] = df['col'].astype('float64')
pd.to_numeric(df['col'], errors='coerce')   # coerce bad strings to NaN
df['col'] = df['col'].astype('category')    # for repeated string cols (SKU, region)
df['col'].str.strip().str.lower()           # whitespace/case normalization
df['col'].replace({'N/A': np.nan, '': np.nan})

# Lag features
df['lag_1']  = df['target'].shift(1)
df['lag_7']  = df['target'].shift(7)

# Rolling statistics
df['roll_mean_7']  = df['target'].rolling(7).mean()
df['roll_std_7']   = df['target'].rolling(7).std()
df['ewm_mean']     = df['target'].ewm(span=7).mean()

# Differencing (stationarity)
df['diff_1']  = df['target'].diff(1)
df['diff_7']  = df['target'].diff(7)          # seasonal diff
np.log1p(df['target'])                        # log transform (handles zeros)

# Pct change / growth
df['pct_change'] = df['target'].pct_change()

df.groupby('sku')['qty'].transform('sum')
df.groupby(['sku', pd.Grouper(freq='M')])['qty'].sum()
df.pivot_table(index='date', columns='sku', values='qty', aggfunc='sum')
df.groupby('sku').apply(lambda g: g.set_index('date').resample('D').sum())

np.where(cond, val_if_true, val_if_false)
np.select([cond1, cond2], [val1, val2], default=val3)
np.nan
np.isnan(arr)
np.clip(arr, lower, upper)
np.log1p(arr) / np.expm1(arr)         # safe log/inverse for zero-inclusive data
np.array_split(arr, n)                # for CV folds
np.percentile(arr, [25, 75])
np.polyfit(x, y, deg=1)               # quick trend line

assert df.index.is_monotonic_increasing
assert not df.index.duplicated().any()
assert df.index.freq is not None or pd.infer_freq(df.index) is not None
print(df.isna().sum())
print(df.describe())
print(f"Date range: {df.index.min()} to {df.index.max()}")
print(f"Expected periods: {len(pd.date_range(df.index.min(), df.index.max(), freq='D'))}, Actual: {len(df)}")