---
title: Pandas
tags: Templates, Talk
description: View the slide with "Slide Mode".
---
# Data in Python: Intro to Pandas & a bit of Seaborn :panda_face: :ocean:
By: Carlos Oliver (aka. @dondraper :man_in_business_suit_levitating:)
---
### Video Walkthrough
{%youtube 5iu6HRuvZ4M %}
---
### Motivation :zap:
* Tabular/matrix data (i.e. data entered in rows and columns of a table) is pervasive in many fields, including the life sciences :microscope:
* The most widely used software for dealing with this kind of data is Microsoft Excel.
---
### Why use Python? :snake:
* I'll give three (four) reasons why you might want to try working with this kind of data in Python instead
1. It's free. :money_with_wings:
2. Automation and complex manipulations is much easier :pie:
3. Seamlessly integrate with the rest of your Python tools (e.g. database management, machine learning training, etc.) :chart_with_upwards_trend:
4. Clicking on buttons hurts :hospital: :mouse: :no_entry_sign:
---
### Objectives :goal_net:
1. Loading a dataset in Pandas
2. Inspecting it
3. Basic manipulations
4. Visualization
:::danger
* These libraries are super extensive.
* This is not an exhaustive coverage of pandas, this is just to get a small taste and become accustomed to learning from [documentation](https://pandas.pydata.org/) to best suit your needs.
:::
---
### Getting a Dataset
* I browsed [Kaggle](www.kaggle.com) for some interesting COVID-related datasets (of course)
* We'll be using the [COVID by US county](https://www.kaggle.com/fireballbyedimyrnmom/us-counties-covid-19-dataset) dataset.
::: danger
**Disclaimer**: This is a real dataset, but the following visualizations and statistics are done purely for illustration purposes, not as a real analysis of COVID.
:::
---
:::info
1. Download the **CSV** file (Comma Separated Values). A CSV is basically a text version of an Excel Table.
2. Place the file in a folder `my_project/data/` (we'll be working in the `my_project` folder)
:::
---
### Loading the file in Python
:::info
Create a file in `my_project/` called `covid.py`
:::
* If the source file is a CSV. [doc](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html?highlight=read_csv#pandas.read_csv)
```python
import pandas as pd
df = pd.read_csv('../data/covid.csv')
```
---
* If the source file is an Excel File. [doc](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html)
```python
import pandas as pd
df = pd.read_excel('../data/covid.xls')
```
---
### DataFrames
* The core object that Pandas uses is called a DataFrame.
* We loaded our data into a DataFrame called `df`
* This is object stores the table data and supports most of the needed functionality.
* Let's take a look, using the `head` method which prints the first few rows of the table.
---
```python
print(df.head())
```
::: warning
```
date county state fips cases deaths
0 2020-01-21 Snohomish Washington 53061.0 1 0
1 2020-01-22 Snohomish Washington 53061.0 1 0
2 2020-01-23 Snohomish Washington 53061.0 1 0
3 2020-01-24 Cook Illinois 17031.0 1 0
4 2020-01-24 Snohomish Washington 53061.0 1 0
```
:::
* Each row is a day with number of deaths and cases for each US county.
---
### Selections: Columns
* To get a list of columns
```python
print(df.columns)
```
::: warning
```
Index(['date', 'county', 'state', 'fips', 'cases', 'deaths'], dtype='object')
```
:::
* To get a single column
```python
dates = df['date']
print(dates)
```
Output:
```
0 2020-01-21
1 2020-01-22
2 2020-01-23
3 2020-01-24
4 2020-01-24
5 2020-01-25
...
```
* To get multiple columns
```python
print(df[['date', 'county']])
```
---
### Selections: Row
* To select a specific row, we use the `iloc` attribute. [docs](https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html)
```python
print(df.iloc[2])
```
::: warning
```
date 2020-01-23
county Snohomish
state Washington
fips 53061
cases 1
deaths 0
Name: 2, dtype: object
```
:::
---
* You can then select columns within a row
```python
print(df.iloc[2]['date'])
```
::: warning
```
2020-01-23
```
:::
---
### Advanced Selection
* Row and column selection methods are very extensive and powerful. You should really read the [docs]() for a full understanding.
* Here is an example of getting the rows where deaths exceed cases by at least a factor of 2
* `.loc` is a powerful attribute which can take a condition on the column values to filter the rows.
```python
print(df.loc[df['deaths'] > df['cases'] * 2 ])
```
::: warning
```
date county state fips cases deaths
26292 2020-04-02 Unknown California NaN 0 1
28473 2020-04-03 Unknown Arizona NaN 0 4
29475 2020-04-03 Unknown Minnesota NaN 0 3
29803 2020-04-03 Unknown New York NaN 37 608
30135 2020-04-03 Unknown Pennsylvania NaN 0 1
30475 2020-04-03 Unknown Utah NaN 0 3
```
:::
---
### Adding columns
* Maybe we want to flag certain counties with lots of cases.
```python
df['hot'] = df['cases']> 100
print(df.head())
```
::: warning
```
date county state fips cases deaths hot
0 2020-01-21 Snohomish Washington 53061.0 1 0 False
1 2020-01-22 Snohomish Washington 53061.0 1 0 False
2 2020-01-23 Snohomish Washington 53061.0 1 0 False
3 2020-01-24 Cook Illinois 17031.0 1 0 False
4 2020-01-24 Snohomish Washington 53061.0 1 0 False
```
:::
---
### Writing DataFrames
* Let's say we want to publish our DataFrame with the `hot` column.
* We simply write it to a new CSV file which can be loaded later.
```python
df.to_csv('new_data.csv')
#or to excel format
df.to_excel('new_data.xls')
```
---
### Grouping
* Maybe we want to know averages for the columns **per state**
* the `groupby` method comes in handy here.
```python
#groupby object
state_grouped = df.groupby(['state'])
#new dataframe
means_df = state_grouped.mean()
```
::: warning
```
state fips cases deaths hot
0 Alabama 1067.804565 95.768957 3.544569 0.222011
1 Alaska 2148.606250 23.179455 0.465347 0.059406
2 Arizona 4014.058216 411.876394 18.223048 0.372677
3 Arkansas 5075.446098 37.349700 0.763608 0.077983
4 California 6058.734863 671.394359 25.821298 0.382446
5 Colorado 8062.313048 206.642279 10.232297 0.233342
```
:::
:::info
The logic behind grouping is a bit involved so I suggest reading the [docs](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.groupby.html).
:::
---
### Sorting
* Which states have the most daily deaths on average?
---
```python
print(means_df.sort_values(by=['deaths'], ascending=False)[['state', 'deaths']])
```
::: warning
```
state deaths
33 New York 274.444252
31 New Jersey 212.262184
6 Connecticut 171.599364
22 Massachusetts 152.209790
8 District of Columbia 140.775000
41 Puerto Rico 58.486486
```
---
:::
* I used the [`sort_values`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.sort_values.html) method with `ascending=False` for a decreasing sort by `deaths`
* Then I print the `state` and `deaths` columns.
---
### Plotting with Seaborn :ocean:
* [Seaborn](https://seaborn.pydata.org/) is a _wrapper_ on top of matplotlib which works nicely with DataFrames.
---
### Deaths per Day
* Let's compare deaths over time between three states.
* Notice the use of [`isin()`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.isin.html) to filter rows whose column values are within a list of accepted values.
```python
import seaborn as sns
import matplotlib.pyplot as plt
g = sns.lineplot(x="date", y="deaths", hue="state" data=df.loc[df['state'].isin(['New York', 'New Jersey', 'California'])])
plt.show()
```
::: spoiler
```python
#I used this to get rid of some x-ticks for easier reading.
for ind, label in enumerate(g.get_xticklabels()):
if ind % 30 == 0: # every 30th label is kept
label.set_visible(True)
else:
label.set_visible(False)
```
:::
---
### :hatching_chick:
---
### Mortality by State
* Let's do one more.
* Let's group by state again and instead of averaging take totals (sum)
* We also add a new column called `mortality`
```python
tot = df.groupby(['state'], as_index=False).sum()
tot['mortality'] = tot['deaths'] / tot['cases']
tot = tot.sort_values(by='mortality', ascending=False)
sns.barplot(x='state', y='mortality', data=tot)
```
---
### :hatching_chick:
---
Sign in with Wallet
Connect another wallet