8  Introduction to Pandas

Now that we have learned the basics, it’s time to learn how to work with real data in Python.

8.1 Tabular Data and CSV Files

8.1.1 Tabular Data

Very often data are in tabular format, where columns represent variables and rows indicate observations, or records. For example, consider the following data on firm sales from two regions for different dates:

date region sales
2024-06-01 north 342
2024-06-01 south 251
2024-06-02 north 489
2024-06-02 south 372
2024-06-03 north 589
2024-06-03 south 492

There are 3 columns representing the variables date, region and sales, and the 6 rows under the variable names contain the individual observations. For example, the first observation is the sales on June 1 in the Northern region.

8.1.2 Comma-Separated Value Files

Such data would often be stored in comma-separated value (CSV) format, which have the file extension .csv. The contents of a file containing these data would look like:

date,region,sales
2024-06-01,north,342
2024-06-01,south,251
2024-06-02,north,489
2024-06-02,south,372
2024-06-03,north,589
2024-06-03,south,492

The variable names are on the first line, separated by commans, and the values for each variable on each subsequent line, also separated by commas. The commas therefore serve to separate the variables.

Sometimes variables can contain values that include commas. For example, if comma decimal separators are used, or a variable contains text that has commas. Consider this alternative example:

date regions revenue
2025-07-01 north,west 3423,40
2025-07-01 south,east 4352,45
2025-07-02 north,west 3635,30
2025-07-02 south,east 4252,53
2025-07-03 north,west 3734,35
2025-07-03 south,east 4742,67

Here there are two sets of regions: a “North, West” and “South, East”. The revenue variable also uses commas to separate the euros and the cents. Naturally this can cause some problems if commas are used to separate the variables.

Suppose we stored these data in a CSV file like this:

date,regions,revenue
2025-07-01,north,west,3423,40
2025-07-01,south,east,4352,45
2025-07-02,north,west,3635,30
2025-07-02,south,east,4252,53
2025-07-03,north,west,3734,35
2025-07-03,south,east,4742,67

Then the program reading the data would see from line 1 that there are 3 variables. However, it would think that there are 5 values on line 2: (i) 2025-07-01, (ii) north, (iii) west, (iv) 3423 and (v) 40. This is clearly not what we want.

To prevent the program reading the data to interpret these commas as separating the values, we can use quotation marks around the values:

"date","regions","revenue"
"2025-07-01","north,west","3423,40"
"2025-07-01","south,east","4352,45"
"2025-07-02","north,west","3635,30"
"2025-07-02","south,east","4252,53"
"2025-07-03","north,west","3734,35"
"2025-07-03","south,east","4742,67"

8.2 Reading CSV Files into Python

Although it is possible to read in data from a CSV into Python using the standard Python library (i.e. no modules), it is much easier and more common to use the Pandas module. This module also contains a large library of functions which are very useful for working with datasets.

We will learn how to use Pandas together with a dataset on Amsterdam House Prices from Kaggle, which is a website that contains many datasets you can explore. You can download the extracted data directly here.

If using Spyder, you should move the data file from your downloads folder to the folder where you are writing your Python script to analyze it. This way don’t need to provide the full file path to the file in our code. Note: Although you can open this CSV file with MS Excel or another spreadsheet software to look at it, do not use “Save” or “Save As” to change the file’s location. MS Excel may change the structure of the file, which can cause problems. Use your file browser to move the file instead.

If the data is now in the same folder as your Python script, you can use the following code to read in the data:

import pandas as pd
df = pd.read_csv("HousingPrices-Amsterdam-August-2021.csv")

Similar to how numpy is typically imported with the alias np, pandas is typically imported as pd. To read in a CSV dataset, we use the read_csv() function in Pandas. We provide the file name as the single argument to this function. In this code I have assigned the output of the read_csv() function to the name df, which is short for DataFrame, the data type that pandas reads the file in as. You don’t need to call it df, but this name is commonly used.

Let’s take a look what what happens when we print df:

print(df)
     Unnamed: 0                                 Address      Zip     Price  \
0             1            Blasiusstraat 8 2, Amsterdam  1091 CR  685000.0   
1             2  Kromme Leimuidenstraat 13 H, Amsterdam  1059 EL  475000.0   
2             3              Zaaiersweg 11 A, Amsterdam  1097 SM  850000.0   
3             4            Tenerifestraat 40, Amsterdam  1060 TH  580000.0   
4             5              Winterjanpad 21, Amsterdam  1036 KN  720000.0   
..          ...                                     ...      ...       ...   
919         920                     Ringdijk, Amsterdam  1097 AE  750000.0   
920         921         Kleine Beerstraat 31, Amsterdam  1033 CP  350000.0   
921         922       Stuyvesantstraat 33 II, Amsterdam  1058 AK  350000.0   
922         923   John Blankensteinstraat 51, Amsterdam  1095 MB  599000.0   
923         924      S. F. van Ossstraat 334, Amsterdam  1068 JS  300000.0   

     Area  Room       Lon        Lat  
0      64     3  4.907736  52.356157  
1      60     3  4.850476  52.348586  
2     109     4  4.944774  52.343782  
3     128     6  4.789928  52.343712  
4     138     5  4.902503  52.410538  
..    ...   ...       ...        ...  
919   117     1  4.927757  52.354173  
920    72     3  4.890612  52.414587  
921    51     3  4.856935  52.363256  
922   113     4  4.965731  52.375268  
923    79     4  4.810678  52.355493  

[924 rows x 8 columns]

We see that the data frame has 924 rows and 8 columns. We see the variable names and first and last 5 rows of data. It contains the street address, postcode, selling price, area (in square meters), number of rooms, and the GPS coordinates (latitude and longitude).

8.3 Data Cleaning

Very often when we read in data we need to make some adjustments before it is ready to be analyzed. We might want to drop some variables or some rows, create new variables from existing ones, or change variable names. Let’s take a look at some examples of these with these data.

8.3.1 Dropping Variables

The first variable, Unnamed: 0 is just a row index from 1 to 924. This variable is useless so we can drop it from the data using the drop method:

df.drop('Unnamed: 0', axis=1, inplace=True)

Here, the axis=1 option means I want to drop a column (it would be 0 for a row), and inplace=True means it modifies df in place. Without this second option we would have to assign the output of the command back to df with the command like this:

df = df.drop('Unnamed: 0', axis=1)

8.3.2 Dropping Rows with Missing Vales

The method .isna() returns for each value True if the value is missing and False otherwise. If we try it with our data we get:

df.isna()
Address Zip Price Area Room Lon Lat
0 False False False False False False False
1 False False False False False False False
2 False False False False False False False
3 False False False False False False False
4 False False False False False False False
... ... ... ... ... ... ... ...
919 False False False False False False False
920 False False False False False False False
921 False False False False False False False
922 False False False False False False False
923 False False False False False False False

924 rows × 7 columns

This isn’t very helpful because we only see the first and last 5 rows, none of which are missing. A better approach is to count the total number of missings per column using the .sum() method on this previous command:

df.isna().sum()
Address    0
Zip        0
Price      4
Area       0
Room       0
Lon        0
Lat        0
dtype: int64

Here we see that the variable Price has 4 missing values, whereas all the other variables have no missing values. Because we are mostly interested in the price of houses, we will want to drop these 4 values. But before we do, let’s take a look at the rows with the missing values. To do this, first let’s see how to extract the price variable from the DataFrame. We do that using the variable name in quotes, inside square brackets:

df['Price']
0      685000.0
1      475000.0
2      850000.0
3      580000.0
4      720000.0
         ...   
919    750000.0
920    350000.0
921    350000.0
922    599000.0
923    300000.0
Name: Price, Length: 924, dtype: float64

To see which of these are missing we add .isna():

df['Price'].isna()
0      False
1      False
2      False
3      False
4      False
       ...  
919    False
920    False
921    False
922    False
923    False
Name: Price, Length: 924, dtype: bool

This gives a 924\times1 vector of True and False. To see the rows of df when this vector is True, we use the vector to index the DataFrame as follows:

df[df['Price'].isna()]
Address Zip Price Area Room Lon Lat
73 Falckstraat 47 A, Amsterdam 1017 VV NaN 147 3 4.897454 52.360707
321 Haarlemmerweg 705, Amsterdam 1067 HP NaN 366 12 4.787874 52.383877
610 Zeeburgerkade 760, Amsterdam 1019 HT NaN 107 3 4.945022 52.369244
727 Suikerplein 16, Amsterdam 1013 CK NaN 81 3 4.880976 52.389623

Now we can see which rows have a missing price. Now that we have identified these we might be happy to drop these from our data.

One way to do this would be to keep all rows where price is not missing. To get a vector that is True when price is not missing and False otherwise we just need to flip the result of df['Price'].isna(). We can do that with the ~ symbol:

~df['Price'].isna()
0      True
1      True
2      True
3      True
4      True
       ... 
919    True
920    True
921    True
922    True
923    True
Name: Price, Length: 924, dtype: bool

Using this vector to index the data keeps only the rows with complete values:

df = df[~df['Price'].isna()]
df
Address Zip Price Area Room Lon Lat
0 Blasiusstraat 8 2, Amsterdam 1091 CR 685000.0 64 3 4.907736 52.356157
1 Kromme Leimuidenstraat 13 H, Amsterdam 1059 EL 475000.0 60 3 4.850476 52.348586
2 Zaaiersweg 11 A, Amsterdam 1097 SM 850000.0 109 4 4.944774 52.343782
3 Tenerifestraat 40, Amsterdam 1060 TH 580000.0 128 6 4.789928 52.343712
4 Winterjanpad 21, Amsterdam 1036 KN 720000.0 138 5 4.902503 52.410538
... ... ... ... ... ... ... ...
919 Ringdijk, Amsterdam 1097 AE 750000.0 117 1 4.927757 52.354173
920 Kleine Beerstraat 31, Amsterdam 1033 CP 350000.0 72 3 4.890612 52.414587
921 Stuyvesantstraat 33 II, Amsterdam 1058 AK 350000.0 51 3 4.856935 52.363256
922 John Blankensteinstraat 51, Amsterdam 1095 MB 599000.0 113 4 4.965731 52.375268
923 S. F. van Ossstraat 334, Amsterdam 1068 JS 300000.0 79 4 4.810678 52.355493

920 rows × 7 columns

We can see now that we have lost 4 rows, the rows with the missing values for price.

Another way to drop rows with missing values is the following command:

df.dropna(inplace=True)

This drops all rows with a missing value contained anywhere in it. This looks easier than what we did before, but an important part of data cleaning is to understand where the missings are, how many there are and why they might be there. Dropping rows blindly could lead to unexpected outcomes and should be avoided.

8.3.3 Creating New Variables

In real estate, a common variable is the price per square meter. We have the price and the number of square meters (variable Area), so we can calculate this variable from our existing data.

df['price_per_sqm'] = df['Price'] / df['Area']

8.3.4 Renaming Variables

Sometimes we want to change variable names. We can see all the variable names using the command df.columns:

df.columns
Index(['Address', 'Zip', 'Price', 'Area', 'Room', 'Lon', 'Lat',
       'price_per_sqm'],
      dtype='str')

Calling the postcode "Zip" makes no sense for 2 reasons:

  1. ZIP is an acronym for Zone Improvement Plan, and should be all upper case, and
  2. ZIP codes are only used in the US, and so not in Amsterdam.

So let’s rename this variable to postcode. We might also want to rename the variable "Room" to "num_rooms" to be more descriptive. To change the names of a subset of variables we can use the rename method and provide a dictionary to the columns argument, where the keys of the dictionary are the old names and the corresponding values are the new names. We do it all together with:

df.rename(columns={"Zip" : "postcode", "Room" : "num_rooms"}, inplace=True)

Now our variable names have a mix of upper and lower case names, which is not so satisfying. Suppose I wanted to make all my variable names lower case. I can do that by replacing all column names with the lower case version of the names, which I can obtain using df.columns.str.lower(). To change the names to lower case I do:

df.columns = df.columns.str.lower()

8.4 Describing Data

Now that we have cleaned our data, it’s time to start analyzing it. We can get an overview of the main summary statistics for each numeric variable using the .describe() method:

df.describe()
price area num_rooms lon lat price_per_sqm
count 9.200000e+02 920.000000 920.00000 920.000000 920.000000 920.000000
mean 6.220654e+05 95.607609 3.56413 4.888652 52.363271 6479.852299
std 5.389942e+05 56.849699 1.57103 0.053118 0.024054 2219.265218
min 1.750000e+05 21.000000 1.00000 4.644819 52.291519 2430.555556
25% 3.500000e+05 60.000000 3.00000 4.855834 52.351925 4649.086379
50% 4.670000e+05 83.000000 3.00000 4.886818 52.364499 6578.947368
75% 7.000000e+05 113.000000 4.00000 4.922337 52.377545 7768.912530
max 5.950000e+06 623.000000 14.00000 5.029122 52.423805 25252.808989

This gives the number of observations (920), the mean, standard deviation, minimum, 25th percentile, median, 75th percentile and maximum value for each numeric variable. Notice that address and postcode don’t appear here because they are strings (we can’t get the mean of a string variable). Because price is in euros, values are in the €175,000-€5.95m range. Therefore Python reports the numbers in scientific notation. 9.20e+02 is the same as 9.2\times10^2=9.2\times 100=920.

To get the mean of a single variable we can do:

df['price'].mean()
np.float64(622065.4195652173)

We can see above that the average house has 3.564 rooms. To see the distribution of the number of rooms we can use value_counts():

df['num_rooms'].value_counts()
num_rooms
3     330
4     201
2     191
5      97
6      42
7      19
1      17
8      11
9       6
13      2
10      2
14      2
Name: count, dtype: int64

This tells us that 330 observations had 3 rooms, 201 observations had 4 rooms, …, and 2 observations had 14 rooms. This shows 3 rooms first because it is the most common. To sort it by the number of rooms instead we can do:

df['num_rooms'].value_counts().sort_index()
num_rooms
1      17
2     191
3     330
4     201
5      97
6      42
7      19
8      11
9       6
10      2
13      2
14      2
Name: count, dtype: int64

To get the proportion of observations with each number of rooms we can use the option normalize=True:

df['num_rooms'].value_counts(normalize=True)
num_rooms
3     0.358696
4     0.218478
2     0.207609
5     0.105435
6     0.045652
7     0.020652
1     0.018478
8     0.011957
9     0.006522
13    0.002174
10    0.002174
14    0.002174
Name: proportion, dtype: float64

This tells us that 35.87% of houses sold had 3 rooms.

8.5 Subsetting

Suppose you want to see the average price of houses that satisfy certain criteria. Perhaps you want to know the average price of houses that have exactly 4 rooms. What we can do is first subset the data so that there are only observations with 4 rooms. Then using this subset we calculate the mean price. We can do this in steps as follows:

df_four_rooms = df[df['num_rooms'] == 4]
df_four_rooms['price'].mean()
np.float64(609845.0945273632)

But it’s not necessary to create a whole new dataframe to do this operation. We can combine them into one command to avoid this:

df[df['num_rooms'] == 4]['price'].mean()
np.float64(609845.0945273632)

Suppose I want to get the average price of houses that have exactly 4 rooms and have at least 100 square meters. I can combine logical vectors by putting them in parentheses and using the & operator:

df[(df['num_rooms'] == 4) & (df['area'] >= 100)]['price'].mean()
np.float64(859761.7764705883)

We can also do OR operations. Suppose now I want to get the average price of houses that have at least 4 rooms or have at least 100 square meters. This is similar to before but we use | to represent or:

df[(df['num_rooms'] >= 4) | (df['area'] >= 100)]['price'].mean()
np.float64(847846.6853932585)

8.6 Group by operations

Suppose now I want the average price of houses with exactly 1 room, exactly 2 rooms, exactly 3 rooms, and so on. I could use df['num_rooms'].unique() to get the unique values for the number of rooms and loop over these (sorting them first with sorted()):

for i in sorted(df['num_rooms'].unique()):
    print("The average price of houses with", str(i),
      "room" if i == 1 else "rooms", "is",
        str(round(df[df['num_rooms'] == i]['price'].mean())))
The average price of houses with 1 room is 394529
The average price of houses with 2 rooms is 383479
The average price of houses with 3 rooms is 512416
The average price of houses with 4 rooms is 609845
The average price of houses with 5 rooms is 845076
The average price of houses with 6 rooms is 919929
The average price of houses with 7 rooms is 1394737
The average price of houses with 8 rooms is 1751636
The average price of houses with 9 rooms is 1450000
The average price of houses with 10 rooms is 4300000
The average price of houses with 13 rooms is 4725000
The average price of houses with 14 rooms is 3772500

But there is a much easier way to do this in Pandas using groupby. We simply do:

df.groupby('num_rooms')['price'].mean()
num_rooms
1     3.945294e+05
2     3.834791e+05
3     5.124164e+05
4     6.098451e+05
5     8.450764e+05
6     9.199286e+05
7     1.394737e+06
8     1.751636e+06
9     1.450000e+06
10    4.300000e+06
13    4.725000e+06
14    3.772500e+06
Name: price, dtype: float64

For cleaner output we can add round() at the end:

df.groupby('num_rooms')['price'].mean().round()
num_rooms
1      394529.0
2      383479.0
3      512416.0
4      609845.0
5      845076.0
6      919929.0
7     1394737.0
8     1751636.0
9     1450000.0
10    4300000.0
13    4725000.0
14    3772500.0
Name: price, dtype: float64

8.7 Applying functions in Pandas

Suppose we want to create a new variable which is the 4-digit postcode (the first 4 digits in the address’s postcode).

For a single postcode, we could apply the following function to get it:

def get_four_digits(x):
    return x[:4]

Let’s try it on the first postcode in the data ('1091 CR'):

get_four_digits(df['postcode'][0])
'1091'

To apply this to all values in the data, we can use the apply method on the variable, and assign the output to a new variable:

df['four_digit_postcode'] = df['postcode'].apply(get_four_digits)
df
address postcode price area num_rooms lon lat price_per_sqm four_digit_postcode
0 Blasiusstraat 8 2, Amsterdam 1091 CR 685000.0 64 3 4.907736 52.356157 10703.125000 1091
1 Kromme Leimuidenstraat 13 H, Amsterdam 1059 EL 475000.0 60 3 4.850476 52.348586 7916.666667 1059
2 Zaaiersweg 11 A, Amsterdam 1097 SM 850000.0 109 4 4.944774 52.343782 7798.165138 1097
3 Tenerifestraat 40, Amsterdam 1060 TH 580000.0 128 6 4.789928 52.343712 4531.250000 1060
4 Winterjanpad 21, Amsterdam 1036 KN 720000.0 138 5 4.902503 52.410538 5217.391304 1036
... ... ... ... ... ... ... ... ... ...
919 Ringdijk, Amsterdam 1097 AE 750000.0 117 1 4.927757 52.354173 6410.256410 1097
920 Kleine Beerstraat 31, Amsterdam 1033 CP 350000.0 72 3 4.890612 52.414587 4861.111111 1033
921 Stuyvesantstraat 33 II, Amsterdam 1058 AK 350000.0 51 3 4.856935 52.363256 6862.745098 1058
922 John Blankensteinstraat 51, Amsterdam 1095 MB 599000.0 113 4 4.965731 52.375268 5300.884956 1095
923 S. F. van Ossstraat 334, Amsterdam 1068 JS 300000.0 79 4 4.810678 52.355493 3797.468354 1068

920 rows × 9 columns

You can also define and apply simple functions in one go using what are called lambda functions. These are small anonymous functions that are only used once. We can actually achieve what we did before with a single line using this approach:

df['four_digit_postcode'] = df['postcode'].apply(lambda x: x[:4])

8.8 Exporting Data

After doing some data cleaning and creating some new variables, we might want to export the data to a file. For example, perhaps we want to analyze the cleaned data in other software, or we want to share it with someone. We can export it using the to_csv() method, providing the filename we want to use. You should not use the same filename as the raw data, because then the your script will likely not work again and if you want to make any changes you will have to download the raw data again, which may not be easy to do. So let’s call it the same name but with -cleaned at the end so we know this is the cleaned version. We do it with:

df.to_csv("HousingPrices-Amsterdam-August-2021-cleaned.csv", index=False)

Here index=False is to tell Python not to include an extra column with the row indices, which go from 0 to 923. In most cases this column is useless and just makes your files bigger.