Analyzing Tabular Data With Pandas

Setup

Download Data

import owncloud
from pathlib import Path

Path('data').mkdir(exist_ok=True, parents=True)

owncloud.Client.from_public_link('https://uni-bonn.sciebo.de/s/Jzmi8zBPsbn8AHs').get_file('/', 'data/salaries.csv')

owncloud.Client.from_public_link('https://uni-bonn.sciebo.de/s/ddzMt8N7oBPCaT5').get_file('/', 'data/titanic.csv')

True

In many analyses, it is useful to organize data in a tabular format. Tables are great because they allow us to store heterogeneous data and assign names to columns which makes exploration more intuitive. The most popular library for working with tabular data in Python is called pandas which derives from panel data. In this section we are going to learn how to use pandas and how it interfaces with other tools to create a powerful ecosystem for data analysis. At the core of pandas is the DataFrame - an object that stores two-dimensional tabular data. Variables that store data frames are commonly called df. Most of pandas functionality come as so-called methods of the data frame object. Methods are called by typing the variable name and the method name separated by a dot. For example: df.head() will call the .head() method of the data frame df. If you are not used to this syntax, don’t worry - all sections contain examples for how the respective methods are used.

Section 1: Reading and Writing Tabular Data

There are many different file formats for tabular data that pandas supports (for a full list, see this website). One of the most commonly used formats is CSV which stands for comma-separated values. A CSV file contains plain text where items are separated by commas. Because it is plain text, CSV is independent of the programming language which makes it a useful interoperable standard. In this section, we are going to learn how to read data from a CSV file into pandas and how to write it back to the CSV format.

Exercise: Import the pandas library under the alias pd.

import pandas as pd

Exercise: Read the file salaries.csv into a data frame and assign it to a variable called df.

df = pd.read_csv('data/salaries.csv')
df

Exercise: Print the first 3 rows of the data frame df.

df.head(3)

Exercise: Get the first 3 rows of df and assign them to another variable called new_df. Write new_df to a file called new_salaries.csv.

new_df = df[:3]
new_df.to_csv('new_salaries.csv')

Section 2: Exploring a Data Set in Pandas

Now that we know how to read CSV files, we can start working with some actual data! In this section, we are going to analyze demographic data from passengers of the Titanic which are stored in the file titanic.csv. Once we have loaded the data into a data frame, we can access a given column by providing its name as a string inside square brackets. For example, df["age"] will return the column "age" in the data frame df. By extracting individual columns and using methods like .max() or .mean(), one can quickly get an overview of the data stored in a table.

Exercise: Read the file titanic.csv into a pandas data frame and assign it to the variable df. Display the first three rows of the dataframe.

df = pd.read_csv('data/titanic.csv')
df.head(3)

Exercise: Get the column "survived" from df.

df['survived']

0      0
1      1
2      1
3      1
4      0
      ..
886    0
887    1
888    0
889    1
890    0
Name: survived, Length: 891, dtype: int64

Exercise: What is the mean "fare" that passengers on the Titanic paid?

df['fare'].mean()

32.204207968574636

Exercise: What is the mean "age" of passengers on the Titanic?

df['age'].mean()

29.69911764705882

Exercise: What is the minimum and maximum "age" of the Titanic’s passengers?

df['age'].min(), df['age'].max()

(0.42, 80.0)

Exercise: What are the different "deck"s on the Titanic?

df['deck'].unique()

array([nan, 'C', 'E', 'G', 'D', 'A', 'B', 'F'], dtype=object)

Exercise: What is the most common "embark_town" for passengers of the Titanic? Hint: The value_counts() function may be useful here.

df['embark_town'].value_counts()

embark_town
Southampton    644
Cherbourg      168
Queenstown      77
Name: count, dtype: int64

Section 3: Indexing and Filtering a Data Frame

Sometimes, we want to access elements in specific elements in a data frame. This can be done by using the df.loc attribute which gets the data located at a specific index. The index of a data frame is the unnamed column you see on the very left. By combining indices and column names, df.loc can get the value stored at a specific index, in a specific column. Also, just like arrays, a data frame can be filtered using Boolean masks. The process is exactly the same: you create a mask of True and False values by applying a logical condition to a data frame. Then, you apply it to the data frame to extract the values where the mask equals True. One can also combine indexing and filtering by using the mask as an input for df.loc. In this section, we will use indexing and filtering to do simple statistical analyses of the Titanic data.

Exercise: Get the row at the index 4 in df.

df.loc[4]

survived                 0
pclass                   3
sex                   male
age                   35.0
fare                  8.05
embark_town    Southampton
deck                   NaN
Name: 4, dtype: object

Exercise: Get the rows at indices 10 to 15 in df.

df.loc[10:15]

Exercise: Get the "sex" of the first 6 passengers in df.

df.loc[:6, 'sex']

0      male
1    female
2    female
3    female
4      male
5      male
6      male
Name: sex, dtype: object

Exercise: Get the data from all passengers on deck E.

# solution without ".isin()"
mask = df['deck']=='E'
print(df[mask])

# solution with ".isin()"
mask = df['deck'].isin(['E'])
df[mask]

     survived  pclass     sex   age      fare  embark_town deck
6           0       1    male  54.0   51.8625  Southampton    E
92          0       1    male  46.0   61.1750  Southampton    E
123         1       2  female  32.5   13.0000  Southampton    E
166         1       1  female   NaN   55.0000  Southampton    E
262         0       1    male  52.0   79.6500  Southampton    E
303         1       2  female   NaN   12.3500   Queenstown    E
309         1       1  female  30.0   56.9292    Cherbourg    E
319         1       1  female  40.0  134.5000    Cherbourg    E
337         1       1  female  41.0  134.5000    Cherbourg    E
356         1       1  female  22.0   55.0000  Southampton    E
370         1       1    male  25.0   55.4417    Cherbourg    E
429         1       3    male  32.0    8.0500  Southampton    E
434         0       1    male  50.0   55.9000  Southampton    E
456         0       1    male  65.0   26.5500  Southampton    E
460         1       1    male  48.0   26.5500  Southampton    E
462         0       1    male  47.0   38.5000  Southampton    E
512         1       1    male  36.0   26.2875  Southampton    E
558         1       1  female  39.0   79.6500  Southampton    E
572         1       1    male  36.0   26.3875  Southampton    E
577         1       1  female  39.0   55.9000  Southampton    E
585         1       1  female  18.0   79.6500  Southampton    E
662         0       1    male  47.0   25.5875  Southampton    E
701         1       1    male  35.0   26.2875  Southampton    E
707         1       1    male  42.0   26.2875  Southampton    E
717         1       2  female  27.0   10.5000  Southampton    E
724         1       1    male  27.0   53.1000  Southampton    E
751         1       3    male   6.0   12.4750  Southampton    E
772         0       2  female  57.0   10.5000  Southampton    E
809         1       1  female  33.0   53.1000  Southampton    E
823         1       3  female  27.0   12.4750  Southampton    E
835         1       1  female  39.0   83.1583    Cherbourg    E
857         1       1    male  51.0   26.5500  Southampton    E

Example: Is the survival rate higher for male or female passengers?

mask = df['sex'] == 'male'
survival_male = df[mask]['survived'].mean()
survival_female = df[~mask]['survived'].mean()
print(survival_male, survival_female)

0.18890814558058924 0.7420382165605095

Exercise: Is the survival rate higher for passengers below or above the age of 40?

mask = df['age'] > 40
df[mask]['survived'].mean(), df[~mask]['survived'].mean()

(0.36666666666666664, 0.3873144399460189)

Exercise: What was the average fare paid by passengers who survived and those who didn’t?

# this is a solution that requires slightly less code
df.groupby('survived')['fare'].mean()

survived
0    22.117887
1    48.395408
Name: fare, dtype: float64

Exercise: What was the average fare paid by passengers in "pclass" 1 and "pclass" 3?

# this is a solution that requires slightly less code
df.groupby('pclass')['fare'].mean()

pclass
1    84.154687
2    20.662183
3    13.675550
Name: fare, dtype: float64

Section 4: The Pandas Ecosystem

Another advantage of pandas is its integration with other software which creates a powerful ecosystem for data analysis. In this section we are going to explore two such libraries: seaborn, a tool for visualizations and pingouin, a package for statistical analysis. Both libraries provide a similar interface where you pass a data frame as data and specify variables based on their column name. This homogeneous interface makes data analysis within the pandas framework very convenient.

Visualization with Seaborn

The Seaborn library allows us to create detailed visualizations, with little effort. We just have to specify the data we want to visualize and the software will take care of the details like adding colors and labeling axes. In this section, we are going to use the sns.catplot() function which is for plotting categorical data. This function takes in a data frame and the labels of columns we wish to plot on the x and y axis. It also takes other arguments to further configure the plot such as hue or kind (for a full list of parameters, see the documentation).

Exercise: Import the seaborn library under the alias sns.

import seaborn as sns

Example: Create a catplot to visualize the distribution of "fare" across the different passenger classes ("pclass").

sns.catplot(df, x = 'pclass', y = 'fare', )

Exercise: Create a bar plot for the same data by using the kind argument.

Solution

sns.catplot(df, x = 'pclass', y = 'fare', kind='bar')

Exercise: Add a color or hue to the bar plot to encode passenger sex.

Solution

sns.catplot(df, x = 'pclass', y = 'fare', kind='bar', hue='sex')

Exercise: Create a bar plot to show differences in survival rates ("survived") between the different passenger classes ("pclass") and add a hue to encode "sex".

Solution

sns.catplot(df, x = 'pclass', y = 'survived', kind='bar', hue='sex')

Statistical Analysis with Pingouin

Pingouin is a library for performing statistical tests that is neatly integrated with pandas. In this section we are going to use Pingouin to perform an analysis of variance (ANOVA) which is a test that compares the variance between groups to the variance within groups with respect to a specific variable. This allows us to estimate if the difference between groups exceeds the “background noise” in the data. We are also going to compute pairwise correlations for all variables in the data set. By checking which variables are correlated, we can identify possible confounds in our analysis.

Exercise: Import the pingouin library under the alias pg.

import pingouin as pg

Example: Perform an analysis of variance (ANOVA) to check if the variance in "fare" between "embark_town"s exceeds the variance within each "embark_town".

pg.anova(df, dv='fare', between='embark_town')

Exercise: Perform an analysis of variance (ANOVA) to check if the variance in "age" between "pclass"es exceeds the variance within each "pclass".

pg.anova(df, dv='age', between='pclass')

Exercise: Calculate the pairwise correlations for all columns in df. Assign the data frame returned by pg.pariwise_corr to a new variable called df_corr and print it.

df_corr = pg.pairwise_corr(df, )
df_corr

Exercise: Get all rows of df_corr where "p-unc" is smaller than 0.001 and print the columns "X", "Y" and "r" (Tip: to select multiple columns, use a lists, e.g. df.loc[mask, ["var1", "var2"]]).

mask = df_corr['p-unc'] < 0.001
df_corr.loc[mask, ["X", "Y", 'r']]