Convert › Pandas → T-SQL

Pandas to SQL Server translation hub

Notebook habits, written as T-SQL. merge, groupby, fillna, drop_duplicates, loc. 28 pages. Runs in the browser.

28 methods

mergehow="left" is LEFT JOIN. on= is the ON clause.joinjoin aligns on the index. In SQL the index is just another key.concatStacking frames is UNION ALL. Aligning columns by name is the SELECT list.groupbygroupby("col").agg(...) is GROUP BY col plus aggregates.aggagg(["sum","mean"]) is SUM and AVG in the same SELECT.pivotindex / columns / values is the PIVOT shape.pivot_tablepivot_table is GROUP BY plus an optional PIVOT. aggfunc=sum is SUM.fillnafillna(0) is ISNULL(col, 0). fillna(method="ffill") is harder.dropnadropna() is WHERE col IS NOT NULL. how="any" is several ANDs.drop_duplicateskeep="first" is ROW_NUMBER = 1. A unique list is DISTINCT.locloc[mask, cols] is SELECT cols WHERE mask.ilociloc[:10] is TOP 10. iloc[10:20] is OFFSET 10 FETCH NEXT 10.queryquery("a > 1 and b == 'x'") is WHERE a > 1 AND b = 'x'.sort_valuesascending=False is DESC. Two columns are two ORDER BY keys.renamerename(columns={"a":"b"}) is SELECT a AS b.astypeastype("int64") is CAST or TRY_CONVERT. Dirty text needs TRY.applyapply(lambda r: ...) is usually a CASE. Rarely a function.value_countsvalue_counts() is GROUP BY col, COUNT(*), ORDER BY n DESC.nuniquenunique() is COUNT(DISTINCT col).headhead(n) is SELECT TOP n. You still need ORDER BY.tailtail(n) is TOP n with the opposite ORDER BY, or OFFSET to the end.reset_indexreset_index() turns an index into a column. SQL columns already are.assignassign(x=...) is a computed column in the SELECT.wherewhere(cond, other) keeps the value when cond, else other. That is CASE.replacereplace("a","b") on a string is REPLACE(). A dict of values is CASE.str.containsstr.contains("x") is LIKE '%x%'. regex=True is PATINDEX or a real CLR — prefer LIKE.dt.to_periodto_period("M") is CONVERT(char(7), d, 120). to_period("Q") is year plus quarter.to_sqlto_sql writes rows. In SQL Server that is a staging table plus INSERT or BULK INSERT.

Then write it on a ticket

Monday is free. A colleague asks, you type, we mark it.