Data Analysis with Python

Small jobs: the standard library first

Lucía Romero's KPI pack starts with a file from the billing system (KBS): the September consumption per meter, exported at 02:30 as a semicolon-separated file with decimal commas, the way Dutch software writes them. To add up kWh per tariff you do not need any package: the csv module reads the rows (see CSV files), a defaultdict collects the totals, and Decimal keeps the sums exact.

import csv
import io
from collections import defaultdict
from decimal import Decimal

# September totals per meter, as the nightly export delivers them
export = """meter_id;tariff;segment;kwh_sept
M-30001;fixed;household;334,990
M-30002;day_night;household;296,708
M-30004;dynamic;household;271,212
M-30005;variable;household;208,233
M-30006;dynamic;business;1554,960
M-30007;day_night;household;336,270
M-30008;fixed;business;463,290
M-30009;variable;household;168,004
M-30010;fixed;household;275,960
M-30011;day_night;business;1195,540
M-30012;dynamic;household;231,104
M-30014;variable;;221,324
"""

kwh = defaultdict(Decimal)
meters = defaultdict(int)
for row in csv.DictReader(io.StringIO(export), delimiter=";"):
    kwh[row["tariff"]] += Decimal(row["kwh_sept"].replace(",", "."))
    meters[row["tariff"]] += 1

for tariff in sorted(kwh):
    print(f"{tariff:<10} {meters[tariff]} meters {kwh[tariff]:>9} kWh")

day_night  3 meters  1828.518 kWh
dynamic    3 meters  2057.276 kWh
fixed      3 meters  1074.240 kWh
variable   3 meters   597.561 kWh

The examples on this page read their data from a string with io.StringIO, which behaves like an open file, so each example runs on its own; in real use you pass a file name. The totals match the SQL on Analytical SQL, which uses the same Kestrel sample data. Note the last row: meter M-30014 has no segment, because its contract points to a customer who does not exist. That row comes back several times below.

This approach stays readable for one calculation. Once you need a second grouping, a join with another file, a median, or a table with tariffs as columns, the loops multiply, and that is the point where pandas earns its place.

pandas and the DataFrame

pandas is the standard Python library for working with tables of data: reading them from files and databases, cleaning, filtering, grouping, joining and reshaping them, and writing them out again. It is open source and builds on NumPy, the numerical library. As of October 2026 the current version is pandas 3.0.6; pandas 3.0 (January 2026) needs Python 3.11 or newer. It is not part of the standard library: install it in a virtual environment with pip install pandas pyarrow (pyarrow is optional, but makes text columns faster and is the usual engine for Parquet files).

The central object is the DataFrame: a two-dimensional table with named columns and labeled rows, in which every column has a single data type. It is roughly a spreadsheet sheet or a database table that lives in memory, with methods for every common operation. read_csv builds one from the same export:

import io
import pandas as pd

export = """meter_id;tariff;segment;kwh_sept
M-30001;fixed;household;334,990
M-30002;day_night;household;296,708
M-30004;dynamic;household;271,212
M-30005;variable;household;208,233
M-30006;dynamic;business;1554,960
M-30007;day_night;household;336,270
M-30008;fixed;business;463,290
M-30009;variable;household;168,004
M-30010;fixed;household;275,960
M-30011;day_night;business;1195,540
M-30012;dynamic;household;231,104
M-30014;variable;;221,324
"""
df = pd.read_csv(io.StringIO(export), sep=";", decimal=",")
print(df.head(4))
print(df.shape)
print(df.dtypes)

  meter_id     tariff    segment  kwh_sept
0  M-30001      fixed  household   334.990
1  M-30002  day_night  household   296.708
2  M-30004    dynamic  household   271.212
3  M-30005   variable  household   208.233
(12, 4)
meter_id        str
tariff          str
segment         str
kwh_sept    float64
dtype: object

head(4) shows the first four rows, shape is (rows, columns). The numbers on the left are the row labels, here simply 0 to 11.

Series and the index

Each column of a DataFrame is a Series: a one-dimensional array of values of one type, with a label for every value. The labels form the index, which is how pandas finds rows and lines up values: when two Series are added, values with the same label are matched, not values in the same position. Making meter_id the index lets you look up a meter by its code:

import io
import pandas as pd

export = """meter_id;tariff;segment;kwh_sept
M-30001;fixed;household;334,990
M-30006;dynamic;business;1554,960
M-30008;fixed;business;463,290
M-30011;day_night;business;1195,540
"""
df = pd.read_csv(io.StringIO(export), sep=";", decimal=",")
kwh = df.set_index("meter_id")["kwh_sept"]
print(type(kwh).__name__)
print(kwh["M-30006"])
print(kwh.sort_values(ascending=False).head(3))

Series
1554.96
meter_id
M-30006    1554.96
M-30011    1195.54
M-30008     463.29
Name: kwh_sept, dtype: float64

dtypes: the type of each column

A dtype (data type) is the type that all values in a pandas column share: int64 and float64 for numbers, bool, datetime64[us] for timestamps, str for text, category for a small set of repeated values. The dtype decides what you can do with a column (you cannot average text) and how much memory it takes. In pandas 3 text columns get the dedicated str dtype, stored with pyarrow when it is installed; older versions and most tutorials show object instead. A missing value shows as NaN in number and text columns and as NaT ("not a time") in date columns.

Checking df.dtypes right after reading a file is the fastest data-quality check there is: a column of amounts that arrives as str means a decimal comma or a stray text value, and a code that arrives as int64 has lost any leading zeros.

Reading files without losing data

read_csv guesses everything it is not told, and with Dutch exports it guesses wrong in three ways at once. The real KBS readings export has an 18-digit EAN code per connection, a date in day-month-year order and a decimal comma:

import io
import pandas as pd

export = """ean;reading_date;kwh_import
871687400000000017;07-09-2026;7,290
871687400000000017;08-09-2026;91,300
"""

naive = pd.read_csv(io.StringIO(export))
print(naive.columns.tolist())

guessed = pd.read_csv(io.StringIO(export), sep=";", parse_dates=["reading_date"])
print(guessed)
print(guessed.dtypes)

['ean;reading_date;kwh_import']
                  ean reading_date kwh_import
0  871687400000000017   2026-07-09      7,290
1  871687400000000017   2026-08-09     91,300
ean                      int64
reading_date    datetime64[us]
kwh_import                 str
dtype: object

Without sep=";" the whole line is one column. With it, three problems remain, and none of them raises an error:

• The dates are read month-first: 7 September became 9 July. pandas infers the format from the first value, and "07-09-2026" fits month-day-year. Had the file contained the 13th, the column would have stayed text instead.
• kwh_import is text (7,290), because the comma is not a decimal point to pandas. Sums would fail or, worse, concatenate.
• ean became an int64. It fits, but an EAN is an identifier, not a quantity: codes with a leading zero lose it, and spreadsheets that receive this number later round it to 15 significant digits.

Telling read_csv what you know fixes all three:

import io
import pandas as pd

export = """ean;reading_date;kwh_import
871687400000000017;07-09-2026;7,290
871687400000000017;08-09-2026;91,300
"""

df = pd.read_csv(
    io.StringIO(export),
    sep=";",
    decimal=",",
    dtype={"ean": "str"},
    parse_dates=["reading_date"],
    date_format="%d-%m-%Y",
)
print(df)
print(df.dtypes)

                  ean reading_date  kwh_import
0  871687400000000017   2026-09-07        7.29
1  871687400000000017   2026-09-08       91.30
ean                        str
reading_date    datetime64[us]
kwh_import             float64
dtype: object

Once a file is clean, store it as Parquet with df.to_parquet("readings.parquet"): the dtypes are saved with the data, so pd.read_parquet returns the same table without any options, and the file is much smaller. The format itself, and the CSV dialects you will meet, are on CSV, JSON and Parquet.

Selecting, filtering and new columns

Three forms cover most selection. df["col"] takes a column; df[mask] keeps the rows where a Boolean Series is true; df.loc[rows, columns] does both at once, by label. Combine conditions with & (and) and | (or), each condition in parentheses, because Python's and and or do not work on whole columns.

import io
import pandas as pd

export = """meter_id;tariff;segment;kwh_sept
M-30001;fixed;household;334,990
M-30002;day_night;household;296,708
M-30004;dynamic;household;271,212
M-30005;variable;household;208,233
M-30006;dynamic;business;1554,960
M-30007;day_night;household;336,270
M-30008;fixed;business;463,290
M-30009;variable;household;168,004
M-30010;fixed;household;275,960
M-30011;day_night;business;1195,540
M-30012;dynamic;household;231,104
M-30014;variable;;221,324
"""
df = pd.read_csv(io.StringIO(export), sep=";", decimal=",")

df["kwh_per_day"] = (df["kwh_sept"] / 30).round(2)

business = df[df["segment"] == "business"]
print(business[["meter_id", "tariff", "kwh_per_day"]])

print(df.loc[df["tariff"] == "dynamic", "kwh_sept"].sum())
print(df.loc[(df["kwh_sept"] > 300) & (df["segment"] == "household"), "meter_id"].tolist())

  meter_id     tariff  kwh_per_day
4  M-30006    dynamic        51.83
6  M-30008      fixed        15.44
9  M-30011  day_night        39.85
2057.276
['M-30001', 'M-30007']

df["kwh_sept"] / 30 divides all twelve values at once. This is a vectorized operation: one expression that works on a whole column, carried out by compiled code inside pandas and NumPy rather than by a Python loop. It is shorter, and on a million rows it is often a hundred times faster or more than looping with iterrows(). If you find yourself writing a for loop over the rows of a DataFrame, look for the column method that does the same thing.

Grouping: split, apply, combine

Almost every summary follows the same three steps, a pattern called split-apply-combine: split the rows into groups by a key, apply a calculation to each group separately, and combine the results into a new table. It is what SQL's GROUP BY does, and in pandas it is groupby. Named aggregation gives every result column a clear name:

import io
import pandas as pd

export = """meter_id;tariff;segment;kwh_sept
M-30001;fixed;household;334,990
M-30002;day_night;household;296,708
M-30004;dynamic;household;271,212
M-30005;variable;household;208,233
M-30006;dynamic;business;1554,960
M-30007;day_night;household;336,270
M-30008;fixed;business;463,290
M-30009;variable;household;168,004
M-30010;fixed;household;275,960
M-30011;day_night;business;1195,540
M-30012;dynamic;household;231,104
M-30014;variable;;221,324
"""
df = pd.read_csv(io.StringIO(export), sep=";", decimal=",")

per_tariff = df.groupby("tariff").agg(
    meters=("meter_id", "count"),
    kwh=("kwh_sept", "sum"),
    median_kwh=("kwh_sept", "median"),
)
print(per_tariff.round(1))

print(df.groupby("segment")["kwh_sept"].sum())
print(df.groupby("segment", dropna=False)["kwh_sept"].sum())

           meters     kwh  median_kwh
tariff
day_night       3  1828.5       336.3
dynamic         3  2057.3       271.2
fixed           3  1074.2       335.0
variable        3   597.6       208.2
segment
business     3213.790
household    2122.481
Name: kwh_sept, dtype: float64
segment
business     3213.790
household    2122.481
NaN           221.324
Name: kwh_sept, dtype: float64

The first grouping has all twelve meters. The second has a problem you only see if you add it up: business plus household is 5336.271 kWh, not the 5557.595 in the file. groupby leaves out rows whose key is missing, by default and without a warning, so the orphan meter's 221.324 kWh vanished, exactly like the inner join on Analytical SQL. dropna=False keeps the missing key as its own group. Use the function names as strings ("sum", "median"): in pandas 3 they select the fast pandas implementation, while passing Python's or NumPy's functions no longer does.

Keeping every row: transform

agg returns one row per group. transform returns one value per original row, with the group's result repeated, which is what you need to compare each row with its group. It is the pandas counterpart of a SQL window function with PARTITION BY:

import io
import pandas as pd

export = """meter_id;tariff;kwh_sept
M-30002;day_night;296,708
M-30004;dynamic;271,212
M-30006;dynamic;1554,960
M-30007;day_night;336,270
M-30011;day_night;1195,540
M-30012;dynamic;231,104
"""
df = pd.read_csv(io.StringIO(export), sep=";", decimal=",")

tariff_total = df.groupby("tariff")["kwh_sept"].transform("sum")
df["pct_of_tariff"] = (100 * df["kwh_sept"] / tariff_total).round(1)
print(df.sort_values(["tariff", "kwh_sept"], ascending=[True, False]))

  meter_id     tariff  kwh_sept  pct_of_tariff
4  M-30011  day_night  1195.540           65.4
3  M-30007  day_night   336.270           18.4
0  M-30002  day_night   296.708           16.2
2  M-30006    dynamic  1554.960           75.6
1  M-30004    dynamic   271.212           13.2
5  M-30012    dynamic   231.104           11.2

Joining tables with merge

merge joins two DataFrames on a key column, like an SQL join. The default is an inner join, which drops rows without a match. Two arguments turn silent surprises into visible ones: validate checks the relationship you expect ("many_to_one": many contracts per customer, but each customer only once in the customer table) and raises a MergeError if the data breaks it, and indicator=True adds a _merge column saying where each row was found.

import pandas as pd

contracts = pd.DataFrame({
    "contract_id": ["CT-20004", "CT-20006", "CT-20012", "CT-20014"],
    "customer_id": ["C-10003", "C-10005", "C-10011", "C-10099"],
    "tariff": ["dynamic", "dynamic", "dynamic", "variable"],
})
customers = pd.DataFrame({
    "customer_id": ["C-10003", "C-10005", "C-10011"],
    "name": ["Lotte Kortenhoeve", "Bakkerij Korenschoof B.V.", "Marit Wolterbeek"],
    "segment": ["household", "business", "household"],
})

inner = contracts.merge(customers, on="customer_id")
print(len(contracts), "contracts,", len(inner), "after an inner merge")

checked = contracts.merge(
    customers, on="customer_id", how="left",
    validate="many_to_one", indicator=True,
)
print(checked[["contract_id", "customer_id", "segment", "_merge"]])

4 contracts, 3 after an inner merge
  contract_id customer_id    segment     _merge
0    CT-20004     C-10003  household       both
1    CT-20006     C-10005   business       both
2    CT-20012     C-10011  household       both
3    CT-20014     C-10099        NaN  left_only

left_only marks the contract whose customer is missing. Filtering on it (checked[checked["_merge"] == "left_only"]) is the pandas version of an anti-join, and a good line to keep in every script that joins data from two systems.

Reshaping: pivot, melt and a full calendar

Data for analysis is usually "long": one row per meter per day. Reports are often "wide": tariffs as columns. pivot_table goes from long to wide, aggregating on the way, and melt goes back:

import io
import pandas as pd

export = """meter_id;tariff;segment;kwh_sept
M-30001;fixed;household;334,990
M-30002;day_night;household;296,708
M-30004;dynamic;household;271,212
M-30005;variable;household;208,233
M-30006;dynamic;business;1554,960
M-30007;day_night;household;336,270
M-30008;fixed;business;463,290
M-30009;variable;household;168,004
M-30010;fixed;household;275,960
M-30011;day_night;business;1195,540
M-30012;dynamic;household;231,104
M-30014;variable;;221,324
"""
df = pd.read_csv(io.StringIO(export), sep=";", decimal=",")

table = df.pivot_table(
    index="segment", columns="tariff", values="kwh_sept",
    aggfunc="sum", fill_value=0,
)
print(table.round(1))

long = table.reset_index().melt(id_vars="segment", var_name="tariff", value_name="kwh")
print(long.head(4).round(1))

tariff     day_night  dynamic  fixed  variable
segment
business      1195.5   1555.0  463.3       0.0
household      633.0    502.3  611.0     376.2
     segment     tariff     kwh
0   business  day_night  1195.5
1  household  day_night   633.0
2   business    dynamic  1555.0
3  household    dynamic   502.3

Two details deserve a second look. The orphan meter is missing again: pivot_table also drops rows with a missing key. And fill_value=0 shows 0.0 kWh for businesses on a variable tariff, where the truth is "no such customers"; without it the cell would be NaN. Zero and "no data" mean different things in a report, so choose deliberately. The same operation in a spreadsheet is on Pivot Tables and Summaries.

Duplicates and missing days

Daily readings should have exactly one row per meter per day. duplicated() finds rows that repeat an earlier one, and reindex over a full calendar from pd.date_range turns a missing day into a visible NaN. These are real rows from the Kestrel sample:

import pandas as pd

readings = pd.DataFrame({
    "meter_id": ["M-30005"] * 3 + ["M-30010"] * 4,
    "reading_date": pd.to_datetime([
        "2026-09-11", "2026-09-12", "2026-09-12",
        "2026-09-15", "2026-09-16", "2026-09-18", "2026-09-19",
    ]),
    "kwh_import": [5.812, 8.307, 8.307, 9.348, 8.478, 9.705, 10.103],
})

print(readings[readings.duplicated()])

m10 = readings[readings["meter_id"] == "M-30010"].set_index("reading_date")
calendar = pd.date_range("2026-09-15", "2026-09-19", freq="D")
print(m10["kwh_import"].reindex(calendar))

  meter_id reading_date  kwh_import
2  M-30005   2026-09-12       8.307
2026-09-15     9.348
2026-09-16     8.478
2026-09-17       NaN
2026-09-18     9.705
2026-09-19    10.103
Freq: D, Name: kwh_import, dtype: float64

Whether to drop the duplicate (drop_duplicates()) and how to treat the gap (leave it, or estimate it and mark it as estimated) are decisions to record, not to bury in a script; see Data Types and Data Quality.

pandas 3 and outdated tutorials

pandas 3.0 changed several long-standing defaults. Code and answers written for pandas 1 and 2, which is most of what a search engine or an AI assistant offers, can fail or quietly do nothing.

Copy-on-Write and chained assignment

Under Copy-on-Write, the default since pandas 3.0, every selection or method result behaves as a new, independent object: changing it never changes the DataFrame it came from. pandas still shares the memory behind the scenes and copies only when something is actually written, so this costs little. The consequence is that "chained assignment", two indexing steps in a row, now never updates the original. pandas warns with a ChainedAssignmentError and leaves the data unchanged:

import warnings
import pandas as pd

df = pd.DataFrame({"meter_id": ["M-30001", "M-30002"], "kwh_import": [91.3, 8.593]})

with warnings.catch_warnings(record=True) as caught:
    warnings.simplefilter("always")
    df["kwh_import"][0] = 9.13          # chained assignment: two steps
print(type(caught[0].message).__name__)
print(df["kwh_import"].tolist())

df.loc[0, "kwh_import"] = 9.13          # one step: row and column together
print(df["kwh_import"].tolist())

ChainedAssignmentError
[91.3, 8.593]
[9.13, 8.593]

The example records the warning to print its name; in a normal script it appears on standard error, and is easy to miss in a scheduled job. The fix is always one .loc[row, column] assignment, or assigning a whole new column. The old SettingWithCopyWarning is gone, and the defensive .copy() calls that silenced it are no longer needed.

What else changed

pandas, SQL or a spreadsheet?

The three tools overlap heavily; pick by the job, not by habit.

pandas holds the whole table in memory, so it is comfortable up to a few million rows on a laptop. Kestrel's 15-minute readings are 38.4 million rows a day: that is a job for SQL in a warehouse or DuckDB over Parquet, with pandas on the aggregated result. The next step after the mechanics is looking at the data properly, on Exploratory Data Analysis.