In this lesson we explore the basics of how to use the pandas package to work with data in Python. Pandas is an industry-standard library that provides a highly flexible data structure called a dataframe, which makes data manipulation fast and easy. As a complement to this tutorial, the 10 minutes to pandas tutorial in the official pandas documentation is also a useful starting reference.
1. Dataset
This tutorial is based on the Credit dataset from the Introduction to Statistical Learning texbook by James, Witten, Hastie and Tibshirani. It records the average credit card balace at end of the month for customers of a financial services company, as well as other individual characteristics such age, education, gender, marital status, number of cards, and credit rating.
The dataset can be found 👉 here
or here
2. Importing data
We start by loading the pandas package.
import pandas as pd
Since the dataset is in the csv format, we use the pandas read_csv function to import the file. The package will automatically read the column names and infer the variable types. We assign the data variable to a variable called data, which will store the dataset as an object called a DataFrame.
# We will always assume that the data file is in a subdirectory called "Data"
data = pd.read_csv('Data/Credit.csv')
type(data)
pandas.core.frame.DataFrame
The data file needs to be in the correct place for this to work. You can run the pwd command to check which folder your notebook is running from. This folder needs to have a subfolder called Data, and the Credit.csv file needs to be there. If the data file was in the same folder as the notebook, the command would be pd.read_csv('Credit.csv').
Note: To get help on any function or object, append a question mark to it and run the cell. For example, try running pd.read_csv?.
Our present dataset is quite simple to work with, but others may require more complex calls to read_csv. Refer to the documentation to find the appropriate options for other situations that you may come across.
Pandas also has specialised functions for reading other types of input files, such as Excel files. You can see a list of available functions here. The pandas read table function reads data stored as general delimited text files (for example, where the columns are separated by space rather than commas). In practical business situations, you may often need to obtain data from a relational database rather than a flat file stored in your computer. You can read database tables and queries into a DataFrame by using the read_sql_table, read_sql_query, or read_sql functions.
3. Inspecting the data
A pandas DataFrame has some similarities to a spreadsheet. However, unlike a spreadsheet, you are not able to click through it or manually make modifications in the current setup. We do everything through coding. This may initially feel restrictive, but it is ultimately more efficient and scalable to work in this way. Some Python environments have GUIs (graphical user interfaces) that allow you to view and modify dataframes by clicking through an interface.
The first thing that we’ll want to do is to inspect the data. Two basic methods to do this are the head and tail methods. The head method displays the first rows of the data (by default five), while the tail displays the last rows. You can specify the number of rows within the parentheses.
Running a cell with only the name of the DataFrame will provide a full view, but pandas limits number of rows and columns that can be shown by default. Check here if you want to change this setting.
data.head()
| Obs | Income | Limit | Rating | Cards | Age | Education | Gender | Student | Married | Ethnicity | Balance | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1 | 14.891 | 3606 | 283 | 2 | 34 | 11 | Male | No | Yes | Caucasian | 333 |
| 1 | 2 | 106.025 | 6645 | 483 | 3 | 82 | 15 | Female | Yes | Yes | Asian | 903 |
| 2 | 3 | 104.593 | 7075 | 514 | 4 | 71 | 11 | Male | No | No | Asian | 580 |
| 3 | 4 | 148.924 | 9504 | 681 | 3 | 36 | 11 | Female | No | No | Asian | 964 |
| 4 | 5 | 55.882 | 4897 | 357 | 2 | 68 | 16 | Male | No | Yes | Caucasian | 331 |
data.tail()
| Obs | Income | Limit | Rating | Cards | Age | Education | Gender | Student | Married | Ethnicity | Balance | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 395 | 396 | 12.096 | 4100 | 307 | 3 | 32 | 13 | Male | No | Yes | Caucasian | 560 |
| 396 | 397 | 13.364 | 3838 | 296 | 5 | 65 | 17 | Male | No | No | African American | 480 |
| 397 | 398 | 57.872 | 4171 | 321 | 5 | 67 | 12 | Female | No | Yes | Caucasian | 138 |
| 398 | 399 | 37.728 | 2525 | 192 | 1 | 44 | 13 | Male | No | Yes | Caucasian | 0 |
| 399 | 400 | 18.701 | 5524 | 415 | 5 | 64 | 7 | Female | No | No | Asian | 966 |
data.head(3)
| Obs | Income | Limit | Rating | Cards | Age | Education | Gender | Student | Married | Ethnicity | Balance | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1 | 14.891 | 3606 | 283 | 2 | 34 | 11 | Male | No | Yes | Caucasian | 333 |
| 1 | 2 | 106.025 | 6645 | 483 | 3 | 82 | 15 | Female | Yes | Yes | Asian | 903 |
| 2 | 3 | 104.593 | 7075 | 514 | 4 | 71 | 11 | Male | No | No | Asian | 580 |
The rows of the DataFrame have a numerical index (in bold above), which is the default behaviour. As usual in Python, the index starts from zero. This does not need to be the case in the DataFrame, but pandas follows the Python convention by default.
Alternatively, we can specify an index variable for the DataFrame (that is, a label for each row), which does not need to be a number. For example, if you have time series data, it can be the date.
We can see that for the Credit dataset, the first column is an observation index. Thus, we can directly identify it as the index variable and assign it to the DataFrame index when reading the original data file.
data=pd.read_csv('Data/Credit.csv', index_col='Obs')
data.head()
| Income | Limit | Rating | Cards | Age | Education | Gender | Student | Married | Ethnicity | Balance | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| Obs | |||||||||||
| 1 | 14.891 | 3606 | 283 | 2 | 34 | 11 | Male | No | Yes | Caucasian | 333 |
| 2 | 106.025 | 6645 | 483 | 3 | 82 | 15 | Female | Yes | Yes | Asian | 903 |
| 3 | 104.593 | 7075 | 514 | 4 | 71 | 11 | Male | No | No | Asian | 580 |
| 4 | 148.924 | 9504 | 681 | 3 | 36 | 11 | Female | No | No | Asian | 964 |
| 5 | 55.882 | 4897 | 357 | 2 | 68 | 16 | Male | No | Yes | Caucasian | 331 |
Another useful method to use at this stage is info, which allows you to check total number of rows and columns, the data types for each column (called dtypes in pandas), and verify whether there are missing values.
data.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 400 entries, 1 to 400
Data columns (total 11 columns):
Income 400 non-null float64
Limit 400 non-null int64
Rating 400 non-null int64
Cards 400 non-null int64
Age 400 non-null int64
Education 400 non-null int64
Gender 400 non-null object
Student 400 non-null object
Married 400 non-null object
Ethnicity 400 non-null object
Balance 400 non-null int64
dtypes: float64(1), int64(6), object(4)
memory usage: 37.5+ KB
The object dtype is the most general type: a column with this dtype will typically contain text data. If you expect a column to be numerical but it appears as object, that tells you that there is an issue that you need to investigate and clean up.
There are no missing values in this dataset since info tells us that the dataframe has 400 entries and all columns have 400 non-null values. A direct way of counting missing values is as follows.
data.isnull().sum()
Income 0
Limit 0
Rating 0
Cards 0
Age 0
Education 0
Gender 0
Student 0
Married 0
Ethnicity 0
Balance 0
dtype: int64
4. Data selection
There are two ways to select data in pandas: by providing the column and index labels or passing a numerical index.
4.1 Selecting a column by label
The output will now look different because selecting only one column returns a Series (a specialised object for a single data series) rather than a DataFrame.
data['Income'].head()
Obs
1 14.891
2 106.025
3 104.593
4 148.924
5 55.882
Name: Income, dtype: float64
4.2 Selecting multiple columns by label
data[['Income','Education']].head()
| Income | Education | |
|---|---|---|
| Obs | ||
| 1 | 14.891 | 11 |
| 2 | 106.025 | 15 |
| 3 | 104.593 | 11 |
| 4 | 148.924 | 11 |
| 5 | 55.882 | 16 |
Note that we passed desired column labels as a Python list. The example will make this clear.
names=['Income','Education']
print(names)
['Income', 'Education']
data[names].head()
| Income | Education | |
|---|---|---|
| Obs | ||
| 1 | 14.891 | 11 |
| 2 | 106.025 | 15 |
| 3 | 104.593 | 11 |
| 4 | 148.924 | 11 |
| 5 | 55.882 | 16 |
4.3 Selecting a column by numerical index
The iloc method allows us to select data by numerical indexing. We just have to be careful not be confused by zero indexing. If we want the first column, then the index is zero.
data.iloc[:,0].head()
Obs
1 14.891
2 106.025
3 104.593
4 148.924
5 55.882
Name: Income, dtype: float64
The : syntax indicates that we want to include all rows in the selection.
4.4 Selecting multiple columns by numerical indexes
Here, we pass a list of column numbers for indexing.
data.iloc[:,[0,5]].head(2)
| Income | Education | |
|---|---|---|
| Obs | ||
| 1 | 14.891 | 11 |
| 2 | 106.025 | 15 |
Another method is slicing. Suppose that we want to select the data from the 1st to the 6th column. When specifying a range of integer indexes, the last one is not included. For example, the cell below requests indexes 0, 1, 2, 3, 4, 5 (first to the sixth column).
data.iloc[:,0:6].head(2)
| Income | Limit | Rating | Cards | Age | Education | |
|---|---|---|---|---|---|---|
| Obs | ||||||
| 1 | 14.891 | 3606 | 283 | 2 | 34 | 11 |
| 2 | 106.025 | 6645 | 483 | 3 | 82 | 15 |
4.5 Selecting rows by labels
The loc method alows one to select rows by the designated index labels (Obs).
data.loc[[1,2,5],:]
| Income | Limit | Rating | Cards | Age | Education | Gender | Student | Married | Ethnicity | Balance | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| Obs | |||||||||||
| 1 | 14.891 | 3606 | 283 | 2 | 34 | 11 | Male | No | Yes | Caucasian | 333 |
| 2 | 106.025 | 6645 | 483 | 3 | 82 | 15 | Female | Yes | Yes | Asian | 903 |
| 5 | 55.882 | 4897 | 357 | 2 | 68 | 16 | Male | No | Yes | Caucasian | 331 |
Unlike slicing with numerical indexing, slicing with label indexes includes the last item.
data.loc[1:2,:]
| Income | Limit | Rating | Cards | Age | Education | Gender | Student | Married | Ethnicity | Balance | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| Obs | |||||||||||
| 1 | 14.891 | 3606 | 283 | 2 | 34 | 11 | Male | No | Yes | Caucasian | 333 |
| 2 | 106.025 | 6645 | 483 | 3 | 82 | 15 | Female | Yes | Yes | Asian | 903 |
This next example makes this part clearer by demonstrating how a DataFrame can have a non-numerical index (row labels).
df = data.head(5).copy() # make a copy of the first five rows of the dataframe
df.index = ['a','b','c', 'd', 'e'] # replace the index with arbitrary labels
df.head()
| Income | Limit | Rating | Cards | Age | Education | Gender | Student | Married | Ethnicity | Balance | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| a | 14.891 | 3606 | 283 | 2 | 34 | 11 | Male | No | Yes | Caucasian | 333 |
| b | 106.025 | 6645 | 483 | 3 | 82 | 15 | Female | Yes | Yes | Asian | 903 |
| c | 104.593 | 7075 | 514 | 4 | 71 | 11 | Male | No | No | Asian | 580 |
| d | 148.924 | 9504 | 681 | 3 | 36 | 11 | Female | No | No | Asian | 964 |
| e | 55.882 | 4897 | 357 | 2 | 68 | 16 | Male | No | Yes | Caucasian | 331 |
df.loc[['a','c'],:]
| Income | Limit | Rating | Cards | Age | Education | Gender | Student | Married | Ethnicity | Balance | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| a | 14.891 | 3606 | 283 | 2 | 34 | 11 | Male | No | Yes | Caucasian | 333 |
| c | 104.593 | 7075 | 514 | 4 | 71 | 11 | Male | No | No | Asian | 580 |
df.loc['a':'c',:]
| Income | Limit | Rating | Cards | Age | Education | Gender | Student | Married | Ethnicity | Balance | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| a | 14.891 | 3606 | 283 | 2 | 34 | 11 | Male | No | Yes | Caucasian | 333 |
| b | 106.025 | 6645 | 483 | 3 | 82 | 15 | Female | Yes | Yes | Asian | 903 |
| c | 104.593 | 7075 | 514 | 4 | 71 | 11 | Male | No | No | Asian | 580 |
4.6 Selecting rows by numerical index
This is useful when the index variable is a string or date. Here, we are back to zero indexing.
data.iloc[:2,:]
| Income | Limit | Rating | Cards | Age | Education | Gender | Student | Married | Ethnicity | Balance | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| Obs | |||||||||||
| 1 | 14.891 | 3606 | 283 | 2 | 34 | 11 | Male | No | Yes | Caucasian | 333 |
| 2 | 106.025 | 6645 | 483 | 3 | 82 | 15 | Female | Yes | Yes | Asian | 903 |
df.iloc[:2,:]
| Income | Limit | Rating | Cards | Age | Education | Gender | Student | Married | Ethnicity | Balance | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| a | 14.891 | 3606 | 283 | 2 | 34 | 11 | Male | No | Yes | Caucasian | 333 |
| b | 106.025 | 6645 | 483 | 3 | 82 | 15 | Female | Yes | Yes | Asian | 903 |
4.7 Jointly selecting rows and columns
We can combine the previous examples to simultaneously select specific rows and columns.
data.loc[1:2,['Income', 'Education']] # label indexing
| Income | Education | |
|---|---|---|
| Obs | ||
| 1 | 14.891 | 11 |
| 2 | 106.025 | 15 |
data.iloc[:2,[0,5]] # numerical indexing
| Income | Education | |
|---|---|---|
| Obs | ||
| 1 | 14.891 | 11 |
| 2 | 106.025 | 15 |
4.8 Combining labels and numerical indexes
As a more advanced concept, a slice of a DataFrame is itself a pandas object. That means that we can chain the syntax to select data using a mix of label and numerical indexing.
data[['Income', 'Education']].iloc[0:2,:]
| Income | Education | |
|---|---|---|
| Obs | ||
| 1 | 14.891 | 11 |
| 2 | 106.025 | 15 |
4.9 Selecting columns by data type
We sometimes want select all columns of a particular dtype so that we can process them accordingly. You can do this as follows.
text_variables=data.select_dtypes(['object'])
text_variables.head()
| Gender | Student | Married | Ethnicity | |
|---|---|---|---|---|
| Obs | ||||
| 1 | Male | No | Yes | Caucasian |
| 2 | Female | Yes | Yes | Asian |
| 3 | Male | No | No | Asian |
| 4 | Female | No | No | Asian |
| 5 | Male | No | Yes | Caucasian |
5. Conditional data selection
Suppose that we want to know the average credit card balance among female customers. Below, we select the rows of the balance column for female customers only.
data.loc[data['Gender']=='Female','Balance'].mean()
529.536231884058
This is called Boolean indexing in Python, because it involves the creation of binary variables indicating whether the condition is True of False for each row. The next cell will help you to understand this.
print(data['Gender'].head())
print('')
print((data['Gender']=='Female').head())
Obs
1 Male
2 Female
3 Male
4 Female
5 Male
Name: Gender, dtype: object
Obs
1 False
2 True
3 False
4 True
5 False
Name: Gender, dtype: bool
You can also specify multiple conditions. The following cell selects females with age equal or lower than 30.
data.loc[(data['Gender']=='Female') & (data['Age']<=30),'Balance'].mean()
499.59090909090907
See this reference page for a list of Python comparison operators.
6. Assigning new values
The unique method from pandas allows us to view all the unique values in a column.
data['Gender'].unique()
array([' Male', 'Female'], dtype=object)
We note that the first character in “Male” above is a space, which is a minor error in the data. This is a common type of error, and more problematically we can have a mix of correct and incorrect labels.
We can use our new knowledge of data selection to fix this. Below, we joinly select the rows where the gender is " Male" and the Gender column. We then replace the values in those locations with the correct label.
data.loc[data['Gender']==' Male','Gender']='Male'
data.head()
| Income | Limit | Rating | Cards | Age | Education | Gender | Student | Married | Ethnicity | Balance | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| Obs | |||||||||||
| 1 | 14.891 | 3606 | 283 | 2 | 34 | 11 | Male | No | Yes | Caucasian | 333 |
| 2 | 106.025 | 6645 | 483 | 3 | 82 | 15 | Female | Yes | Yes | Asian | 903 |
| 3 | 104.593 | 7075 | 514 | 4 | 71 | 11 | Male | No | No | Asian | 580 |
| 4 | 148.924 | 9504 | 681 | 3 | 36 | 11 | Female | No | No | Asian | 964 |
| 5 | 55.882 | 4897 | 357 | 2 | 68 | 16 | Male | No | Yes | Caucasian | 331 |
data['Gender'].unique()
array(['Male', 'Female'], dtype=object)
7. Descriptive statistics
After loading and preparing the data, we can start exploring it by looking at the basic descriptive statitistics. The describe method provides a table with basic summary statistics for the data.
data.describe().round(1)
# I appended round to limit the number of decimal places in the output, try without it
| Income | Limit | Rating | Cards | Age | Education | Balance | |
|---|---|---|---|---|---|---|---|
| count | 400.0 | 400.0 | 400.0 | 400.0 | 400.0 | 400.0 | 400.0 |
| mean | 45.2 | 4735.6 | 354.9 | 3.0 | 55.7 | 13.4 | 520.0 |
| std | 35.2 | 2308.2 | 154.7 | 1.4 | 17.2 | 3.1 | 459.8 |
| min | 10.4 | 855.0 | 93.0 | 1.0 | 23.0 | 5.0 | 0.0 |
| 25% | 21.0 | 3088.0 | 247.2 | 2.0 | 41.8 | 11.0 | 68.8 |
| 50% | 33.1 | 4622.5 | 344.0 | 3.0 | 56.0 | 14.0 | 459.5 |
| 75% | 57.5 | 5872.8 | 437.2 | 4.0 | 70.0 | 16.0 | 863.0 |
| max | 186.6 | 13913.0 | 982.0 | 9.0 | 98.0 | 20.0 | 1999.0 |
There are also individual functions for a range of summary statistics. Refer to the pandas documentation for a full list of available functions. As examples, we calculate below: (a) the mean for all variables in the dataset (b) the correlation between income and credit card limit.
data.mean().round(2)
Income 45.22
Limit 4735.60
Rating 354.94
Cards 2.96
Age 55.67
Education 13.45
Balance 520.02
dtype: float64
data[['Income','Limit']].corr().round(2)
| Income | Limit | |
|---|---|---|
| Income | 1.00 | 0.79 |
| Limit | 0.79 | 1.00 |
8. Exporting data
Once you have made the necessary modications to the dataset, you may want to save it to continue working on it later. More generally, you may wish to save the results of your analysis or export tables so that they insert them in a report or webpage (after formatting). Pandas has methods to export data as csv and Excel files, LaTex and HTML tables, among other options.
In the following example I export our DataFrame as an Excel file. You can run the cell and try to open the file in Excel to check that it worked.
data.to_excel('new_data.xlsx')
9. Credits
Credits to Dr Marcel Scharth