pandas Tutorial: DataFrames for Excel Users

pandas is a Python library that lets you work with tables the way you do in Excel, only with code instead of clicks. A pandas DataFrame is essentially a worksheet: rows, named columns and a type for each column. This tutorial maps the Excel tasks you already know (filters, formulas, VLOOKUP, pivot tables) to their pandas equivalents so you can be productive quickly.

DataFrames explained for Excel users

Think of a DataFrame as an Excel Table (the kind you create with Ctrl+T) with a few differences:

The biggest mental shift: in Excel you write a formula in one cell and drag it down. In pandas you write the operation once for the whole column. There is no dragging, no broken ranges and no forgotten rows at the bottom.

New to Python itself? Read Python for Data Analysis: Where to Start first to set up your environment.

Loading and inspecting data

pandas reads CSV, Excel, JSON, SQL databases and more. Reading .xlsx files requires the openpyxl package (included with Anaconda).

import pandas as pd

df = pd.read_excel("orders.xlsx", sheet_name="Orders")
# or: df = pd.read_csv("orders.csv")

df.head()        # first 5 rows
df.shape         # (rows, columns)
df.info()        # column names, types, non-empty counts
df.describe()    # count, mean, min, max for numeric columns

Run df.info() every time you load a file. If a column you expected to be numeric shows up as object, it almost always contains text such as "N/A" or currency symbols. Fix it with pd.to_numeric(df["Amount"], errors="coerce"), which turns anything that is not a number into a missing value you can inspect.

Combining many files at once

In Excel, stacking twelve monthly exports means a lot of copy and paste (or a Power Query append). In pandas it takes a few lines, and it works the same whether you have three files or three hundred:

from pathlib import Path

files = sorted(Path("exports").glob("sales_*.csv"))
df = pd.concat([pd.read_csv(f) for f in files], ignore_index=True)
print(len(files), "files,", len(df), "rows")

Printing the number of files and rows is a simple safety check: if a month is missing or a file is empty, you notice immediately instead of discovering it in a meeting.

Excel tasks translated to pandas

Selecting and filtering (AutoFilter)

# Keep only some columns
df[["OrderDate", "Customer", "Region", "Amount"]]

# Filter: Region is East AND Amount over 500
df[(df["Region"] == "East") & (df["Amount"] > 500)]

# Filter by a list of values (like ticking boxes in AutoFilter)
df[df["Region"].isin(["East", "West"])]

# Text contains
df[df["Customer"].str.contains("Market", case=False, na=False)]

Use & for AND and | for OR, and always wrap each condition in parentheses. Forgetting the parentheses is the most common pandas error beginners see.

Calculated columns (formulas)

import numpy as np

df["Revenue"] = df["Units"] * df["UnitPrice"]
df["Margin %"] = (df["Revenue"] - df["Cost"]) / df["Revenue"]

# IF formula
df["Size"] = np.where(df["Revenue"] >= 1000, "Large", "Small")

# Date parts, like YEAR() and MONTH()
df["OrderDate"] = pd.to_datetime(df["OrderDate"])
df["Year"] = df["OrderDate"].dt.year
df["Month"] = df["OrderDate"].dt.month

Sorting and removing duplicates

df = df.sort_values(["Region", "Revenue"], ascending=[True, False])
df = df.drop_duplicates(subset=["OrderID"])

VLOOKUP / XLOOKUP: merge

In Excel you would use XLOOKUP to bring a customer's segment into the orders table. In pandas you merge two DataFrames on a shared key:

customers = pd.read_excel("customers.xlsx")
df = df.merge(customers[["CustomerID", "Segment"]],
              on="CustomerID", how="left")

how="left" keeps every order even if no customer matches, just like XLOOKUP returning a blank. Unlike VLOOKUP, a merge can match on several columns at once and returns every match, so check for duplicate keys in the lookup table first with customers["CustomerID"].duplicated().sum(). Our comparison of XLOOKUP vs VLOOKUP vs INDEX-MATCH covers the Excel side, and SQL JOINs explained covers the same logic in SQL.

Pivot tables: groupby and pivot_table

# Total and average revenue by region
df.groupby("Region")["Revenue"].agg(["sum", "mean", "count"])

# Classic pivot: regions as rows, years as columns
pd.pivot_table(df, values="Revenue", index="Region",
               columns="Year", aggfunc="sum", fill_value=0)

pivot_table works almost exactly like the Excel dialog: index is Rows, columns is Columns, values is Values and aggfunc is "Summarize values by."

Handling missing values and messy data

Real exports are rarely clean. These commands cover most problems:

• df.isna().sum() counts missing values per column.
• df["Region"] = df["Region"].fillna("Unknown") fills blanks with a label.
• df = df.dropna(subset=["Amount"]) removes rows without an amount.
• df["Customer"] = df["Customer"].str.strip().str.title() works like TRIM and PROPER together.
• df.columns = df.columns.str.strip() removes hidden spaces from headers, a frequent cause of KeyError.

Decide deliberately whether to fill or drop missing values, because the choice changes your totals and averages. When in doubt, count how many rows are affected before you decide, and write that decision down in a comment.

Common errors and what they mean

• KeyError: 'Sales': the column name does not exist exactly as typed. Print df.columns and check spelling, capitalization and hidden spaces.
• SettingWithCopyWarning: you modified a filtered slice. Add .copy() when you create the filtered DataFrame, for example east = df[df["Region"] == "East"].copy().
• Totals look too high after a merge: the lookup table had duplicate keys, so rows were multiplied. Compare len(df) before and after every merge.
• Dates sort alphabetically: the column is still text. Convert it with pd.to_datetime before sorting or grouping.

Exporting results back to Excel

summary = pd.pivot_table(df, values="Revenue", index="Region",
                         columns="Year", aggfunc="sum", fill_value=0)

with pd.ExcelWriter("report.xlsx") as writer:
    df.to_excel(writer, sheet_name="Data", index=False)
    summary.to_excel(writer, sheet_name="Pivot")

This produces a workbook your colleagues can open normally. For formatting, charts and scheduling, continue with Automate Excel Reports with Python.

pandas vs Excel: when to use which

pandas shines with large files, repeated cleaning steps, combining many sources and anything you need to reproduce exactly next month. Excel is faster for quick one-off questions, manual what-if exploration and sharing with people who do not code. Many analysts use both: pandas prepares the data, Excel or Power BI presents it. A practical rule is that if you have done the same manual cleanup three times, it is time to script it.

One more advantage is transparency. A pandas script is a written record of every step: which rows were removed, how blanks were filled and which tables were joined. When a manager asks how a number was calculated, you can show the exact logic instead of trying to remember which cells you edited by hand last Tuesday. That audit trail is also what makes your work easy to hand over to a teammate.

Next steps

Take one report you build in Excel every week and rebuild it in pandas: load, clean, merge, pivot and export. Compare the totals with your Excel version until they match. Once that works, add a chart with Matplotlib or Plotly and try running the script on next week's file without changing a single line.

Watch: video lessons

▶

Frequently asked questions