Pandas is the Python library for working with tables of data. In practice, analysis with Pandas follows the same loop: load data into a DataFrame, inspect it, select and filter what you need, clean missing or wrong values, group and aggregate, merge with other tables, and reshape the result for a chart or a model. Learning to express each step as a whole-column operation, not a Python loop, is the key skill.
What are Series and DataFrames?
A Series is a single labelled column of values. A DataFrame is a table of Series sharing one index, like a spreadsheet or SQL table with typed columns. Almost everything in Pandas is a method on one of these two objects, and most operations return a new object rather than changing the original.
How do you load and inspect data?
Common loaders: pd.read_csv(), pd.read_excel(), pd.read_json(), pd.read_parquet() and pd.read_sql(). After loading, always inspect before analysing:
| Command | What it shows |
|---|---|
df.head() | First five rows |
df.shape | (rows, columns) |
df.info() | Column types and non-null counts |
df.describe() | Count, mean, std, min, quartiles, max for numeric columns |
df['col'].value_counts() | Frequency of each category |
df.isna().sum() | Missing values per column |
Five minutes here prevents hours of confusion later: wrong types (numbers stored as text), unexpected categories ("Kolkata" and "kolkata"), and missing values are almost always present in real data.
How do you select and filter data?
- Columns:
df['city']for one,df[['city', 'score']]for several. - Rows by condition:
df[df['score'] >= 80]; combine conditions with&and|and parentheses:df[(df['score'] >= 80) & (df['city'] == 'Pune')]. - loc and iloc:
df.loc[rows, columns]by labels or conditions;df.iloc[0:10, 0:3]by position. - Membership:
df[df['state'].isin(['Bihar', 'Odisha'])]. - Text:
df[df['name'].str.contains('kumar', case=False)].
How do you clean data with Pandas?
| Problem | Pandas tool |
|---|---|
| Missing values | dropna(), fillna() with a sensible value (median, "Unknown") |
| Wrong types | pd.to_numeric(errors='coerce'), pd.to_datetime(), astype() |
| Inconsistent text | str.strip().str.title(), replace() |
| Duplicates | duplicated(), drop_duplicates() |
| Outliers | Filter with quantiles; investigate before deleting |
| Renaming | rename(columns={...}) |
Never fill missing values blindly. A missing income may mean "not disclosed", which can itself be informative. Record every cleaning decision so your analysis can be reproduced.
How do groupby and aggregation work?
groupby follows "split, apply, combine": split rows into groups, apply a function to each, combine the results. Example: average score and number of students per city:
df.groupby('city').agg(avg_score=('score', 'mean'), students=('id', 'count'))
You can group by several columns, use several aggregations (sum, mean, median, min, max, count, nunique) and sort the result with sort_values(). This one pattern answers a huge share of business questions: revenue by month, conversion by channel, delays by route.
How do you combine tables?
pd.merge(left, right, on='key', how='inner') works exactly like SQL joins, with how set to 'inner', 'left', 'right' or 'outer'. The same duplication risks apply when keys aren't unique; check row counts before and after merging, and use validate='one_to_one' or 'many_to_one' to make Pandas raise an error if your assumption is wrong. See SQL joins explained for the join types. To stack tables with the same columns, use pd.concat().
How do you reshape data?
- Pivot tables:
df.pivot_table(index='city', columns='month', values='revenue', aggfunc='sum')turns long data into a summary grid. - Melt:
df.melt()turns wide data back into long format, which most plotting libraries and models prefer. - Dates: after
pd.to_datetime(), use.dt.month,.dt.day_name(), andresample('W')to aggregate by week or month.
Why are vectorised operations faster than loops?
Pandas columns are backed by NumPy arrays, and operations like df['total'] = df['price'] * df['qty'] run in optimised compiled code over the whole column at once. A Python loop over rows (iterrows()) does the same work one row at a time in the interpreter.
Counting interpreter-level operations: the vectorised version hands the whole loop to compiled code. Real speed-ups are typically tens to hundreds of times.
Rules of thumb: avoid iterrows(); use column arithmetic, np.where() for conditions, map() for lookups, and groupby().transform() for per-group calculations.
What does a mini analysis look like end to end?
- Load monthly sales from CSV and inspect with
info()anddescribe(). - Convert the date column, remove duplicate orders, standardise city names.
- Merge with a products table to get categories.
- Group by month and category to compute revenue and order counts.
- Pivot categories into columns and plot the trend (see our data visualisation guide).
- Write three findings in plain language, with the numbers that support them.
What mistakes do beginners make with Pandas?
- Chained assignment:
df[df['x'] > 0]['y'] = 1may modify a temporary copy, not your DataFrame. Usedf.loc[df['x'] > 0, 'y'] = 1. - Forgetting that most methods return a new object:
df.dropna()alone does nothing todf; assign the result. - Reading numbers as text: a single "N/A" in a column makes the whole column text. Check
info()after loading. - Merging without checking keys: duplicate keys multiply rows. Compare row counts before and after every merge.
- Ignoring the index: after filtering or grouping, the index may not be what you expect;
reset_index()when in doubt.
How do you make Pandas work on bigger data?
When files get large, a few habits keep Pandas usable. Load only the columns you need with usecols. Convert repeated text columns, such as city or state, to the category type, which can cut memory several-fold. Use smaller numeric types where values allow. Process very large CSVs in chunks with chunksize, or convert them once to Parquet, which loads faster and stores types. When data truly outgrows memory, tools like DuckDB, Polars or Spark take over, and the operations you learned in Pandas (filter, group, join, reshape) carry over almost directly.
How does Pandas compare with SQL and spreadsheets?
| Task | Spreadsheet | SQL | Pandas |
|---|---|---|---|
| Filter rows | Filter menu | WHERE | Boolean indexing |
| Summarise by group | Pivot table | GROUP BY | groupby().agg() |
| Combine tables | VLOOKUP / XLOOKUP | JOIN | merge() |
| Reproduce an analysis | Hard (manual clicks) | Easy (saved query) | Easy (script or notebook) |
| Feed a machine learning model | Export first | Export first | Directly |
Many analysts use all three: SQL to pull and pre-aggregate data from the database, Pandas for cleaning, deeper analysis and modelling, and spreadsheets to share small results with colleagues. If you already know Excel, map each spreadsheet habit to its Pandas equivalent and you'll learn twice as fast.
Notebooks or scripts?
Jupyter notebooks are ideal for exploring: run a cell, look at the result, adjust. They become a problem when analyses are rerun months later and cells were run out of order. A good habit is to explore in a notebook, then move the final cleaning and transformation steps into a Python script or functions that run top to bottom. That script can be scheduled, tested and reused to rebuild the dataset whenever new data arrives, which is exactly how analysis grows into a data pipeline, and later into the feature pipelines that machine learning models depend on in production.
How should you practise Pandas?
Pick a public dataset you care about (cricket, weather, government open data) and answer ten questions with it, writing each answer in one sentence. Then rebuild the same analysis in SQL and compare. Pandas, NumPy, EDA and machine learning are taught together in Program Zero's data science course, after the Python and database phases.