pandas: Your Data as a Table
Where NumPy falls short
In the last lesson, the marks table was a NumPy array. To get the maths marks you wrote scores[:, 0], and you had to remember that column 0 meant maths. Fine for three columns. Painful for thirty.
Real data has more problems. Columns have names like "area" and "rent". Some columns hold text and some hold numbers, side by side. Data comes in files, usually CSV files, not typed out by hand. And some values are missing.
pandas is the library built for exactly this. The easiest way to think of it: pandas is Excel, controlled by code. Everything you might do in a spreadsheet, like sorting, filtering, adding a column or finding averages, pandas does in one line. And it runs on NumPy underneath, so it is fast.
Loading real data
We have made a small dataset for you: 21 flats for rent across six areas of Pune. The numbers are made up, but realistic. Paste this into a Colab cell and run it:
1import pandas as pd
2
3df = pd.read_csv("https://cod-e.vercel.app/data/pune-flats.csv")
4df.head()area bhk size_sqft floor furnished rent 0 Hadapsar 3 1275 2 no 26500 1 Hinjewadi 1 675 5 no 14500 2 Baner 2 875 9 yes 29500 3 Baner 1 525 1 yes 17500 4 Kothrud 1 625 6 yes 20500
read_csv reads a CSV file, from a web link or from a file, into a table. head() shows the first five rows, which is always the first thing you do with new data: look at it. In Colab, the table appears with neat formatting.
Tip:Like np for NumPy, everyone writes import pandas as pd. And the table is almost always called df, short for DataFrame. You will see df in nearly every ML notebook.
Notice the numbers 0, 1, 2, 3, 4 down the left side. That is the index: a label for each row. pandas adds it for you. It is not part of the data.
1df.shape(21, 6)
Just like NumPy: 21 rows (flats) and 6 columns.
DataFrames and Series
pandas has two main building blocks, and the names come up everywhere:
Key Points
- DataFrame: the whole table. Rows and named columns. df is a DataFrame.
- Series: a single column. One list of values, with the index next to it.
You pick out a column by its name, in square brackets. That gives you a Series:
1df["rent"].head(3)0 26500 1 14500 2 29500 Name: rent, dtype: int64
The last line tells you the column name and its type. int64 just means whole numbers. And a Series has the same quick functions as a NumPy array:
1print(round(df["rent"].mean()))26690
The average rent across all 21 flats is ₹26,690. To pick several columns, pass a list of names. Note the double square brackets: the outer pair is for picking, the inner pair is the list.
1df[["area", "rent"]].head(3)area rent 0 Hadapsar 26500 1 Hinjewadi 14500 2 Baner 29500
Filtering rows
Remember boolean masks from NumPy? pandas works the same way. Say your budget is under ₹16,000:
1df[df["rent"] < 16000]area bhk size_sqft floor furnished rent 1 Hinjewadi 1 675 5 no 14500 6 Hadapsar 1 625 4 no 14000 14 Wakad 1 575 1 no 13500
Read it as "df, where rent is under 16,000". Only three flats, all unfurnished 1BHKs. To combine conditions, like 2BHKs in Baner, join them with & and wrap each condition in round brackets:
1df[(df["area"] == "Baner") & (df["bhk"] == 2)]area bhk size_sqft floor furnished rent 2 Baner 2 875 9 yes 29500 13 Baner 2 950 9 yes 30000
Tip:Use & for "and" and | for "or". The normal Python words and / or do not work here and give a confusing error. And always put each condition in its own brackets. Forgetting them is one of the most common pandas mistakes.
Sorting and adding columns
The three most expensive flats:
1df.sort_values("rent", ascending=False).head(3)area bhk size_sqft floor furnished rent 16 Viman Nagar 3 1575 1 yes 54000 11 Baner 3 1450 3 yes 44500 9 Wakad 3 1575 12 no 36000
Adding a new column works like maths on NumPy arrays: write the calculation once and it happens for every row. Rent per square foot is a fairer way to compare flats of different sizes:
1df["rent_per_sqft"] = (df["rent"] / df["size_sqft"]).round(1)
2df.head(3)area bhk size_sqft floor furnished rent rent_per_sqft 0 Hadapsar 3 1275 2 no 26500 20.8 1 Hinjewadi 1 675 5 no 14500 21.5 2 Baner 2 875 9 yes 29500 33.7
Look at that. The big Hadapsar 3BHK costs ₹20.8 per square foot, while the smaller Baner 2BHK costs ₹33.7. The raw rent alone would not have shown you that. Creating a helpful new column like this has a name, feature engineering, and it gets a full lesson in Chapter 3.
Group and summarise
This is where pandas really shines. What is the average rent in each area? One line:
1df.groupby("area")["rent"].mean().round().sort_values(ascending=False)area Viman Nagar 35000.0 Baner 30375.0 Kothrud 26750.0 Wakad 25875.0 Hinjewadi 21000.0 Hadapsar 20167.0 Name: rent, dtype: float64
Read it left to right like a sentence: take df, group the rows by area, look at the rent column, find the mean of each group, round it, and sort from high to low. Long chains like this are normal in pandas, and they read naturally once you get used to them.
Does furnishing matter? Compare rent per square foot:
1df.groupby("furnished")["rent_per_sqft"].mean().round(1)furnished no 23.4 yes 30.0 Name: rent_per_sqft, dtype: float64
Furnished flats cost noticeably more per square foot. You just did step 4 of the ML lifecycle from lesson 1.5, exploring the data, and found a pattern a model could use.
And to count how many rows fall in each group:
1df["area"].value_counts()area Baner 4 Kothrud 4 Wakad 4 Hadapsar 3 Hinjewadi 3 Viman Nagar 3 Name: count, dtype: int64
Picking a row by position
Square brackets with a name pick columns. To pick a row by its position, use iloc (think "integer location"):
1df.iloc[0]area Hadapsar bhk 3 size_sqft 1275 floor 2 furnished no rent 26500 rent_per_sqft 20.8 Name: 0, dtype: object
You get the first flat, shown sideways: every column name with its value. There is also loc, which picks rows by their index label instead of their position. For now, iloc is all you need.
From a DataFrame to a model
Now lesson 1.6 should make complete sense. There, we built a DataFrame, then picked out the features and the target:
1X = df[["bhk", "size_sqft", "floor"]] # features: a DataFrame
2y = df["rent"] # target: a SeriesDouble brackets for X because it has several columns. Single brackets for y because it is one column. scikit-learn happily accepts these, and turns them into NumPy arrays behind the scenes.
Why did we leave out area and furnished? Because they are text, and models only understand numbers. Turning them into numbers is a lesson of its own in Chapter 3.
Where people get confused
Key Points
- "df["rent"] and df[["rent"]] are the same." The first is a Series (one column). The second is a DataFrame that happens to have one column. Some functions care which one you give them.
- "df.sort_values(...) sorted my data." It returned a sorted copy and left df unchanged. To keep the result, save it: df = df.sort_values("rent").
- "I can use and / or in filters." You need & and |, with each condition in round brackets.
- "The index is a column of my data." It is just a label pandas adds to each row. It is not part of your data.
Quick check
How would you get all the furnished flats in Kothrud?
df[(df["area"] == "Kothrud") & (df["furnished"] == "yes")]. Two conditions, each in brackets, joined with &.
How would you find the average size of flats for each number of bedrooms?
df.groupby("bhk")["size_sqft"].mean(). Group by bedrooms, look at size, take the mean of each group.
What is the difference between a DataFrame and a Series?
A DataFrame is the whole table, with rows and named columns. A Series is a single column. Picking one column out of a DataFrame gives you a Series.
Recap
Key Points
- pandas is Excel controlled by code, built on top of NumPy.
- pd.read_csv loads a file into a DataFrame. head() shows the first rows. shape gives the size.
- df["col"] gives one column (a Series). df[["a", "b"]] gives several (a DataFrame).
- Filter with masks: df[df["rent"] < 16000]. Combine with & and |, each condition in brackets.
- groupby answers "what is the average of this, for each of that" in one line.
Next up: Matplotlib. Tables of numbers are useful, but you spot patterns much faster when you can see them. Time to draw your data.
Theia
Can you explain this concept?
Practise pandas: Your Data as a Table with realistic interview questions. Choose spoken or typed answers, get feedback on your reasoning, then return to the material that needs work.
Sign in to practise this conceptThe session stays focused on this lesson. Your selected interview level sets the bar.