Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 64 ยท Getting Started

pandas for Excel Users: VLOOKUP, Pivot, Filters and Formulas

Translate what you know from Excel into pandas: VLOOKUP and XLOOKUP with merge, IF and SUMIF with where and groupby, filters, text to columns, sorting and pivot tables.

If you have spent years in Excel, you already think like a data analyst. You know what a lookup is, what a filter does and why a pivot table is magic. pandas does the same jobs, but with code instead of clicks, and with one big advantage: your steps are written down, so you can run them again next month on a new file with no repeated mouse work.

This lesson is a translation table. For each thing you do in a spreadsheet, you will see the pandas equivalent, run it, and learn the one habit that changes the most: you work on whole columns at once, never cell by cell. There is no fill-down handle, because a formula on a column applies to every row by itself.

The mental model

An Excel sheet is a grid where each cell can hold anything. A DataFrame is a table where each column has one type and the first row is the header. Instead of a cell address like C2, you name the column and the row condition.

Excel to pandas

Sheet A DataFrame
Column header A column name
Formula A column expression
Pivot table pivot_table

One formula for the whole column, no fill-down.

1 / 2

We will use two small tables for the rest of the lesson: orders, and a product list with prices.

Python Two tables: orders and products

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Formulas become column expressions

In Excel you type =C2*D2 in the first row and drag it down. In pandas you write the formula once, on the whole column:

Python
df["total"] = df["qty"] * df["price"]

Arithmetic between columns happens row by row, so the result for each row uses that row's own values. Functions work the same way: df["price"].round(1) is ROUND, df["qty"].abs() is ABS. The details are in Vectorised Operations.

VLOOKUP and XLOOKUP: merge

VLOOKUP(A2, products, 3, FALSE) looks up a key in another table and brings back a column. In pandas this is merge, and a left merge is the exact equivalent: keep every row of the left table, and add matching columns from the right one.

Python VLOOKUP as a left merge

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Order 105 uses product P9, which is not in the product list. Excel would show #N/A. pandas shows NaN and carries on, and fillna is your IFERROR. Two differences from VLOOKUP are worth remembering:

  • The key column does not have to be the first one. on="product_id" names it directly, and you can use different names with left_on and right_on.
  • Duplicates in the right table multiply rows. VLOOKUP silently returns the first match. A merge returns every match, so a product listed twice makes each order appear twice. Check with products["product_id"].is_unique, or pass validate="m:1" and pandas raises an error if the right side has duplicate keys.

For a quick lookup of a single value, a Series with an index does the job: products.set_index("product_id")["price"]["P2"]. More patterns are in Merge and Join.

Approximate match

VLOOKUP(x, table, 2, TRUE) finds the closest value at or below x, the trick behind tax brackets and commission bands. The pandas twin is merge_asof, which needs both tables sorted by the key:

Python Approximate-match lookup with merge_asof

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

IF, nested IF and IFS: np.where and np.select

=IF(C2>=5,"bulk","single") becomes np.where(condition, value_if_true, value_if_false). For several branches (nested IFs or IFS), use np.select with a list of conditions and a list of results. The first matching condition wins, just like in IFS.

Python IF and IFS as np.where and np.select

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

SUMIF, COUNTIF and AVERAGEIF: groupby

=SUMIF(region, "North", total) adds up one group. With groupby you get every group at once, which is the real power: one line replaces a whole column of SUMIF formulas.

Python SUMIF, COUNTIF and SUMIFS

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice & instead of AND, and the parentheses around each comparison. Use | for OR and ~ for NOT. See Grouping with groupby for more.

Filters and sorting

The Excel filter dropdown becomes a Boolean mask: a column of True and False that decides which rows stay. Sorting is sort_values, and "Remove Duplicates" is drop_duplicates.

Python Filter, sort and remove duplicates

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Excel's filter hides rows but keeps them in the sheet. pandas creates a new DataFrame and leaves the original alone, so you can never lose data by filtering. More in Boolean Filtering and Sorting and Ranking.

Text to Columns, CONCAT and TRIM

The .str accessor holds the text functions. Data > Text to Columns is str.split(..., expand=True); CONCAT is + between text columns; TRIM, UPPER, LEFT and SUBSTITUTE are strip, upper, slice and replace.

Python Text to columns and friends

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The full list of text tools is in Working with Text.

Pivot tables

The Excel PivotTable maps directly onto pivot_table: rows become index, columns become columns, values become values, and the summary function becomes aggfunc.

Python A pivot table with totals

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

fill_value=0 fills empty cells, and margins=True adds the grand totals. The same options and more are covered in Pivot Tables.

Things Excel does that pandas does differently

  • Formulas are not live. Changing price later does not recalculate an old total column. Rerun the code, which is cheap, and the result is always consistent.
  • No mixed columns. A cell can hold a number in one row and text in the next. A column in pandas has one dtype, so a stray "n/a" makes the whole column text until you clean it. See Data Types and Conversion.
  • Row numbers are labels. The index is not a position you can rely on after filtering. Use reset_index(drop=True) if you want a fresh 0, 1, 2 count.
  • Size. Excel stops at about one million rows. pandas handles tens of millions on an ordinary laptop. See Big Data Options.
  • Open and save. pd.read_excel("file.xlsx") opens a workbook and df.to_excel(...) saves one. See Working with Excel Files.

Tip: When you are stuck translating, say the Excel step out loud: "for each region, add up the totals" is groupby("region")["total"].sum(). If your sentence has the words "for each", you want groupby. If it has "look up", you want merge.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which pandas call is the closest equivalent of VLOOKUP with exact match?

    A left merge keeps every row of the left table and adds matching columns from the right table, which is what VLOOKUP does. Unlike VLOOKUP, it returns all matches, not just the first.
  2. 2. How do you write =IF(C2>=5, "bulk", "single") for a whole column?

    np.where takes a Boolean condition and two results and applies them to every row at once. There is no fill-down in pandas.
  3. 3. What happens to a lookup key that is missing from the lookup table when you use a left merge?

    A left merge keeps all left rows. Where there is no match, the columns from the right table are NaN, much like #N/A, and you can fill them with fillna (the IFERROR of pandas).
  4. 4. Which tool gives you the sum of total for every region in one line, like a column of SUMIF formulas?

    groupby splits the rows by region and sums each group. SUMIF handles only one region per formula.
  5. 5. Why can a merge produce more rows than the left table?

    VLOOKUP returns the first match only. A merge returns every match, so duplicate keys in the lookup table duplicate the left rows. validate='m:1' catches this.

Summary

  • Work on whole columns: write the formula once and it applies to every row.
  • VLOOKUP and XLOOKUP are merge (left merge); approximate match is merge_asof; fillna replaces IFERROR.
  • IF and IFS are np.where and np.select; SUMIF, COUNTIF and AVERAGEIF are groupby with sum, count and mean.
  • Filters are Boolean masks with &, | and ~; sorting is sort_values; Remove Duplicates is drop_duplicates.
  • Text to Columns is str.split(expand=True); PivotTables are pivot_table.
  • Formulas do not stay live and every column has one type; rerun the code to refresh results.

Next: Selecting Columns and Rows shows every way to pick the data you want.