Excel to Python

Filter#

.filter() keeps the rows where the condition is true. The condition is written into the code, so anybody who runs it again gets the same rows.

orders_pl.filter(pl.col("ShipCountry") == "Germany").select("OrderID", "OrderDate", "Freight")

In pandas:

orders[orders["ShipCountry"] == "Germany"][["OrderID", "OrderDate", "Freight"]]

See: §24. Selecting, filtering, sorting

Sort#

Each sort level in Excel's Sort dialog becomes one column in the list, in the same order. descending=True sorts that column largest first.

orders_pl.sort(["ShipCountry", "Freight"], descending=[False, True]).select("ShipCountry", "OrderID", "Freight")

In pandas:

orders.sort_values(["ShipCountry", "Freight"], ascending=[True, False])[["ShipCountry", "OrderID", "Freight"]]

See: §24. Selecting, filtering, sorting

Remove Duplicates#

.unique() returns each combination of the selected columns once and leaves the original frame untouched. It also keeps one row for a missing value, which is why the row count below is one higher than the number of named countries.

customers_pl.select("Country").unique(maintain_order=True)

The trap. .unique() does not promise an order unless you ask for one, so the same cell can return the same rows in a different arrangement on the next run. Pass maintain_order=True whenever the order of the result matters.

In pandas:

customers[["Country"]].drop_duplicates()

See: §24. Selecting, filtering, sorting

COUNTIFS#

Conditions that apply to every count go in the filter. A condition that applies to one count goes inside that count, which lets several counts sit side by side.

orders_pl.filter(pl.col("ShipCountry") == "Germany").select(
    pl.len().alias("german_orders"),
    (pl.col("Freight") > 100).sum().alias("over_100_freight"),
)

In pandas:

german = orders[orders["ShipCountry"] == "Germany"]
pd.DataFrame({"german_orders": [len(german)], "over_100_freight": [(german["Freight"] > 100).sum()]})

See: §26. .value_counts() and .group_by().agg()

SUMIFS#

Each criteria range and criterion becomes one condition, joined with &. Each condition needs its own parentheses, because & binds tighter than == does.

orders_pl.filter(
    (pl.col("ShipCountry") == "Germany") & (pl.col("OrderDate").dt.year() == 2016)
)["Freight"].sum()

In pandas:

orders[(orders["ShipCountry"] == "Germany") & (orders["OrderDate"].dt.year == 2016)]["Freight"].sum()

See: §24. Selecting, filtering, sorting

AVERAGEIFS#

The same filter, with the mean in place of the sum. Freight arrives as a Decimal, so cast it to a float first, which makes the result one type whichever library you are in, and round the result yourself.

orders_pl.filter(pl.col("ShipCountry") == "Germany").select(
    pl.col("Freight").cast(pl.Float64).mean().round(2)
)

In pandas:

round(orders[orders["ShipCountry"] == "Germany"]["Freight"].astype(float).mean(), 2)

See: §23. Series and DataFrame

PivotTable#

The Rows area becomes .group_by(), and the Values area becomes the aggregate inside .agg(). Sort the result, because group_by hands its groups back in no fixed order.

orders_pl.group_by("ShipCountry").agg(pl.col("Freight").sum()).sort("ShipCountry")

In pandas:

orders.groupby("ShipCountry")["Freight"].sum().reset_index()

.reset_index() is there because pandas moves the group labels into the index; polars keeps them as a column.

See: §26. .value_counts() and .group_by().agg()

PivotTable Columns area#

Dragging a second field into the Columns area gives you one column per value of that field. .pivot() is the direct equivalent: index= is the Rows area, on= the Columns area, values= the Values area, and aggregate_function= the summary it shows. sort_columns=True puts the new columns in order.

orders_pl.pivot(
    on="ShipVia", index="ShipCountry", values="Freight", aggregate_function="sum", sort_columns=True
).sort("ShipCountry")

In pandas:

orders.pivot_table(index="ShipCountry", columns="ShipVia", values="Freight", aggfunc="sum").reset_index()

See: §28. Wide and long

VLOOKUP and XLOOKUP#

A lookup is a join. how="left" keeps every row on the left and fills the missing matches with null, which is what XLOOKUP does when you give it an if_not_found.

orders_pl.join(customers_pl.select("CustomerID", "CompanyName"), on="CustomerID", how="left").select(
    "OrderID", "CustomerID", "CompanyName"
)

The trap. A lookup in Excel returns one value per row whatever the other table holds. A join returns one row per match, so a right-hand table with two rows for the same key gives you more rows than you started with. Pass validate="m:1", which says each key appears once on the right, and polars raises instead of adding rows.

In pandas:

orders.merge(customers[["CustomerID", "CompanyName"]], on="CustomerID", how="left")[
    ["OrderID", "CustomerID", "CompanyName"]
]

See: §27. Merging two tables

IF#

pl.when() takes a condition, .then() the value when it holds and .otherwise() the value when it does not. Chain another .when() before .otherwise() for a second condition, which is what a nested IF does without the nesting.

orders_pl.select(
    pl.when(pl.col("Freight") > 100).then(pl.lit("heavy")).otherwise(pl.lit("light")).alias("size")
)["size"].value_counts(sort=True)

In pandas:

heavy = orders["Freight"] > 100
pd.Series("light", index=orders.index).case_when([(heavy, "heavy")]).value_counts().reset_index()

See: §4. Conditionals

IFERROR#

IFERROR wraps one formula. try / except wraps whatever you put inside it, so it catches the failure and lets you decide what to return.

try:
    result = orders_pl["ShipCountry"] / 2
except pl.exceptions.InvalidOperationError:
    result = "cannot divide text"
result

The trap. A bare except: catches everything, including the typo in the line you are debugging, and hands back your fallback as though the code had worked. Name the error you expect. pandas raises a different one for the same line, TypeError.

See: §22. try / except, and assert

ISBLANK#

A missing value is null. Count them with .null_count(), or mark each row with .is_null().

customers_pl["Region"].null_count()

The trap. Comparing the column with an empty string returns zero on the same column where .null_count() finds two missing values, and it raises no error. In a spreadsheet a blank cell compares equal to an empty string. In Python it never does.

In pandas:

customers["Region"].isna().sum()

See: §23. Series and DataFrame

CONCAT#

+ joins two string columns the way & joins two cells. Everything in the expression has to be a string already, so convert anything that is not.

customers_pl.select((pl.col("City") + ", " + pl.col("Country")).alias("where")).head(1)

In pandas:

(customers["City"] + ", " + customers["Country"]).head(1)

See: §5. Strings

TEXTJOIN#

str.join puts a separator between many values, the way TEXTJOIN does. It takes strings and nothing else, and it skips nothing, so drop the missing values before you hand them over.

", ".join(sorted(customers_pl["Country"].drop_nulls().unique())[:3])

See: §5. Strings

LEFT, MID and LEN#

The string methods hang off .str on a column. .str.slice(1, 3) is MID(A2, 2, 3): the second number is a length, as in MID, and the first counts from zero, which is the one difference that bites. .str.len_chars() is LEN.

customers_pl["CustomerID"].str.slice(0, 3).head(1)

The trap. Python's own slice takes an end position where .str.slice() takes a length, so the same three characters of a single string are "ALFKI"[1:4], which is [1:1+3]. .str.slice() on a column and [ : ] on one string look alike and count differently.

In pandas:

customers["CustomerID"].str[:3].head(1)

See: §5. Strings

TRIM and UPPER#

.str.strip_chars() and .str.to_uppercase() return a new column and change nothing in place, so assign the result if you want to keep it.

customers_pl.select(("  " + pl.col("City") + "  ").str.strip_chars().str.to_uppercase()).head(1)

The trap. .str.strip_chars() removes only what is at the two ends of the text. To collapse the runs of spaces inside it as well, add .str.replace_all(r"\s+", " ").

In pandas:

("  " + customers["City"] + "  ").str.strip().str.upper().head(1)

See: §5. Strings

YEAR and MONTH#

.dt opens the date parts of a column the way .str opens the string methods. A column has to be a date for this to work, and pl.read_parquet gives you one already typed.

orders_pl.select(pl.col("OrderDate").dt.year().alias("year")).group_by("year").len().sort("year").head(1)

In pandas:

orders["OrderDate"].dt.year.value_counts().sort_index().reset_index().head(1)

See: §25. A column of dates

EOMONTH#

.dt.month_end() moves every date to the last day of its month.

orders_pl.select(pl.col("OrderDate").dt.month_end()).head(1)

In pandas:

(orders["OrderDate"] + pd.offsets.MonthEnd(0)).head(1)

pandas has no one method for it. Adding a month offset and taking the end of the period is the idiom, and MonthEnd is the offset that does it.

See: §25. A column of dates

DATEDIF#

Subtracting two date columns gives a duration. .dt.total_days() takes the number out of it.

orders_pl.select((pl.col("ShippedDate") - pl.col("OrderDate")).dt.total_days().mean().round(2))

The trap. The mean above is taken over the orders that shipped. An order that never shipped has no date at all, so both libraries leave it out of the average without saying so.

In pandas:

(orders["ShippedDate"] - orders["OrderDate"]).dt.days.mean().round(2)

See: §25. A column of dates

ROUND#

.round() on a column rounds every value in it. Python's own round() takes one number, which is what you reach for after an aggregate.

orders_pl["Freight"].cast(pl.Float64).round(1).head(1)

The trap. Python and polars both round a value ending in exactly five to the nearest even number, so round(0.5) is 0 and round(1.5) is 2. Excel rounds both away from zero. The difference shows up on money that ends in half a cent.

In pandas:

orders["Freight"].astype(float).round(1).head(1)

See: §3. Expressions and operators

Running total#

A running total in Excel is a formula that reaches one row back. .cum_sum() does the whole column at once, and it trusts the order the rows are already in, so sort first.

orders_pl.sort("OrderDate").group_by(pl.col("OrderDate").dt.year().alias("year"), maintain_order=True).agg(
    pl.col("Freight").cast(pl.Float64).sum()
).select("year", pl.col("Freight").cum_sum().round(2)).head(1)

In pandas:

by_year = orders.sort_values("OrderDate").groupby(orders["OrderDate"].dt.year)["Freight"].sum()
by_year.astype(float).cumsum().round(2).reset_index().head(1)

See: §26. .value_counts() and .group_by().agg()

Percent of total#

The denominator is the total over the whole column, so it has to be computed before the grouping narrows things down. Inside .with_columns(), pl.col("Freight").sum() is that total, one number set against every row.

orders_pl.group_by("ShipCountry").agg(pl.col("Freight").cast(pl.Float64).sum()).with_columns(
    (pl.col("Freight") / pl.col("Freight").sum() * 100).round(2).alias("share")
).sort("share", descending=True).select("ShipCountry", "share").head(1)

In pandas:

by_country = orders.groupby("ShipCountry")["Freight"].sum().astype(float)
(by_country / by_country.sum() * 100).round(2).sort_values(ascending=False).reset_index().head(1)

See: §26. .value_counts() and .group_by().agg()

Unpivot#

Power Query's Unpivot Columns turns one column per month into one row per month. .unpivot() is the same move, and it is how you get a wide sheet into the shape a chart wants.

wide = orders_pl.pivot(
    on="ShipVia", index="ShipCountry", values="Freight", aggregate_function="sum", sort_columns=True
).sort("ShipCountry")
wide.unpivot(index="ShipCountry", variable_name="ShipVia", value_name="Freight").head(1)

In pandas:

wide = orders.pivot_table(index="ShipCountry", columns="ShipVia", values="Freight", aggfunc="sum").reset_index()
wide.melt(id_vars="ShipCountry", var_name="ShipVia", value_name="Freight").head(1)

See: §28. Wide and long

Opening the file#

Double-clicking a workbook has no equivalent, because nothing is open until you read it. The read is a line of code, and the file stays on disk unchanged whatever you do to the frame.

pl.read_parquet("data/northwind/customers.parquet").shape

The trap. pl.read_csv guesses the type of every column from its first 100 rows, the way Excel guesses when you open a CSV, and stops with an error when a later row disagrees. A parquet file carries its types with it, which is why the warehouse is stored that way.

In pandas:

pd.read_parquet("data/northwind/customers.parquet").shape

See: §21. Parquet, CSV and JSON