Pandas¶

Introduction¶

pandas is the Python Data Analysis package. It allows for data ingestion, transformation and cleaning, and creates objects that can then be passed on to analytic packages like statsmodels and scikit-learn for modeling and packages like matplotlib, seaborn, and plotly for visualization.

pandas is built on top of numpy, so many numpy functions are commonly used in manipulating pandas objects.

pandas is a pretty extensive package, and we'll only be able to cover some of its features. For more details, there is free online documentation at pandas.pydata.org. You can also look at the book "Python for Data Analysis (2nd edition)" by Wes McKinney, the original developer of the pandas package, for more details.

Starting pandas¶

As with any Python module, you have to "activate" pandas by using import. The "standard" alias for pandas is pd. We will also import numpy, since pandas uses some numpy functions in the workflows.

In [1]:

import numpy as np
import pandas as pd

Data import and export¶

Most data sets you will work with are set up in tables, so are rectangular in shape. Think Excel spreadsheets. In pandas the structure that will hold this kind of data is a DataFrame. We can read external data into a DataFrame using one of many read_* functions. We can also write from a DataFrame to a variety of formats using to_* functions. The most common of these are listed below:

Format type	Description	reader	writer
text	CSV	read_csv	to_csv
	Excel	read_excel	to_excel
text	JSON	read_json	to_json
binary	Feather	read_feather	to_feather
binary	SAS	read_sas
SQL	SQL	read_sql	to_sql

We'll start by reading in the mtcars dataset stored as a CSV file

In [2]:

pd.read_csv('data/mtcars.csv')

Out[2]:

	make	mpg	cyl	disp	hp	drat	wt	qsec	vs	am	gear	carb
0	Mazda RX4	21.0	6	160.0	110	3.90	2.620	16.46	0	1	4	4
1	Mazda RX4 Wag	21.0	6	160.0	110	3.90	2.875	17.02	0	1	4	4
2	Datsun 710	22.8	4	108.0	93	3.85	2.320	18.61	1	1	4	1
3	Hornet 4 Drive	21.4	6	258.0	110	3.08	3.215	19.44	1	0	3	1
4	Hornet Sportabout	18.7	8	360.0	175	3.15	3.440	17.02	0	0	3	2
5	Valiant	18.1	6	225.0	105	2.76	3.460	20.22	1	0	3	1
6	Duster 360	14.3	8	360.0	245	3.21	3.570	15.84	0	0	3	4
7	Merc 240D	24.4	4	146.7	62	3.69	3.190	20.00	1	0	4	2
8	Merc 230	22.8	4	140.8	95	3.92	3.150	22.90	1	0	4	2
9	Merc 280	19.2	6	167.6	123	3.92	3.440	18.30	1	0	4	4
10	Merc 280C	17.8	6	167.6	123	3.92	3.440	18.90	1	0	4	4
11	Merc 450SE	16.4	8	275.8	180	3.07	4.070	17.40	0	0	3	3
12	Merc 450SL	17.3	8	275.8	180	3.07	3.730	17.60	0	0	3	3
13	Merc 450SLC	15.2	8	275.8	180	3.07	3.780	18.00	0	0	3	3
14	Cadillac Fleetwood	10.4	8	472.0	205	2.93	5.250	17.98	0	0	3	4
15	Lincoln Continental	10.4	8	460.0	215	3.00	5.424	17.82	0	0	3	4
16	Chrysler Imperial	14.7	8	440.0	230	3.23	5.345	17.42	0	0	3	4
17	Fiat 128	32.4	4	78.7	66	4.08	2.200	19.47	1	1	4	1
18	Honda Civic	30.4	4	75.7	52	4.93	1.615	18.52	1	1	4	2
19	Toyota Corolla	33.9	4	71.1	65	4.22	1.835	19.90	1	1	4	1
20	Toyota Corona	21.5	4	120.1	97	3.70	2.465	20.01	1	0	3	1
21	Dodge Challenger	15.5	8	318.0	150	2.76	3.520	16.87	0	0	3	2
22	AMC Javelin	15.2	8	304.0	150	3.15	3.435	17.30	0	0	3	2
23	Camaro Z28	13.3	8	350.0	245	3.73	3.840	15.41	0	0	3	4
24	Pontiac Firebird	19.2	8	400.0	175	3.08	3.845	17.05	0	0	3	2
25	Fiat X1-9	27.3	4	79.0	66	4.08	1.935	18.90	1	1	4	1
26	Porsche 914-2	26.0	4	120.3	91	4.43	2.140	16.70	0	1	5	2
27	Lotus Europa	30.4	4	95.1	113	3.77	1.513	16.90	1	1	5	2
28	Ford Pantera L	15.8	8	351.0	264	4.22	3.170	14.50	0	1	5	4
29	Ferrari Dino	19.7	6	145.0	175	3.62	2.770	15.50	0	1	5	6
30	Maserati Bora	15.0	8	301.0	335	3.54	3.570	14.60	0	1	5	8
31	Volvo 142E	21.4	4	121.0	109	4.11	2.780	18.60	1	1	4	2

This just prints out the data, but then it's lost. To use this data, we have to give it a name, so it's stored in Python's memory

In [3]:

mtcars = pd.read_csv('data/mtcars.csv')

One of the big differences between a spreadsheet program and a programming language from the data science perspective is that you have to load data into the programming language. It's not "just there" like Excel. This is a good thing, since it allows the common functionality of the programming language to work across multiple data sets, and also keeps the original data set pristine. Excel users can run into problems and corrupt their data if they are not careful.

If we wanted to write this data set back out into an Excel file, say, we could do

In [4]:

mtcars.to_excel('data/mtcars.xlsx')

You may get an error if you don't have the openpyxl package installed. You can easily install it from the Anaconda prompt using conda install openpyxl and following the prompts.

Exploring a data set¶

We would like to get some idea about this data set. There are a bunch of functions linked to the DataFrame object that help us in this. First we will use head to see the first 8 rows of this data set

In [5]:

mtcars.head(8)

Out[5]:

	make	mpg	cyl	disp	hp	drat	wt	qsec	vs	am	gear	carb
0	Mazda RX4	21.0	6	160.0	110	3.90	2.620	16.46	0	1	4	4
1	Mazda RX4 Wag	21.0	6	160.0	110	3.90	2.875	17.02	0	1	4	4
2	Datsun 710	22.8	4	108.0	93	3.85	2.320	18.61	1	1	4	1
3	Hornet 4 Drive	21.4	6	258.0	110	3.08	3.215	19.44	1	0	3	1
4	Hornet Sportabout	18.7	8	360.0	175	3.15	3.440	17.02	0	0	3	2
5	Valiant	18.1	6	225.0	105	2.76	3.460	20.22	1	0	3	1
6	Duster 360	14.3	8	360.0	245	3.21	3.570	15.84	0	0	3	4
7	Merc 240D	24.4	4	146.7	62	3.69	3.190	20.00	1	0	4	2

This is our first look into this data. We notice a few things. Each column has a name, and each row has an index, starting at 0.

If you're interested in the last N rows, there is a corresponding tail function

Let's look at the data types of each of the columns

In [6]:

mtcars.dtypes

Out[6]:

make     object
mpg     float64
cyl       int64
disp    float64
hp        int64
drat    float64
wt      float64
qsec    float64
vs        int64
am        int64
gear      int64
carb      int64
dtype: object

This tells us that some of the variables, like mpg and disp, are floating point (decimal) numbers, several are integers, and make is an "object". The dtypes function borrows from numpy, where there isn't really a type for character or categorical variables. So most often, when you see "object" in the output of dtypes, you think it's a character or categorical variable.

We can also look at the data structure in a bit more detail.

In [7]:

mtcars.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 32 entries, 0 to 31
Data columns (total 12 columns):
 #   Column  Non-Null Count  Dtype  
---  ------  --------------  -----  
 0   make    32 non-null     object 
 1   mpg     32 non-null     float64
 2   cyl     32 non-null     int64  
 3   disp    32 non-null     float64
 4   hp      32 non-null     int64  
 5   drat    32 non-null     float64
 6   wt      32 non-null     float64
 7   qsec    32 non-null     float64
 8   vs      32 non-null     int64  
 9   am      32 non-null     int64  
 10  gear    32 non-null     int64  
 11  carb    32 non-null     int64  
dtypes: float64(5), int64(6), object(1)
memory usage: 3.1+ KB

This tells us that this is indeed a DataFrame, wth 12 columns, each with 32 valid observations. Each row has an index value ranging from 0 to 11. We also get the approximate size of this object in memory.

You can also quickly find the number of rows and columns of a data set by using shape, which is borrowed from numpy.

In [8]:

mtcars.shape

Out[8]:

(32, 12)

More generally, we can get a summary of each variable using the describe function

In [9]:

mtcars.describe()

Out[9]:

	mpg	cyl	disp	hp	drat	wt	qsec	vs	am	gear	carb
count	32.000000	32.000000	32.000000	32.000000	32.000000	32.000000	32.000000	32.000000	32.000000	32.000000	32.0000
mean	20.090625	6.187500	230.721875	146.687500	3.596563	3.217250	17.848750	0.437500	0.406250	3.687500	2.8125
std	6.026948	1.785922	123.938694	68.562868	0.534679	0.978457	1.786943	0.504016	0.498991	0.737804	1.6152
min	10.400000	4.000000	71.100000	52.000000	2.760000	1.513000	14.500000	0.000000	0.000000	3.000000	1.0000
25%	15.425000	4.000000	120.825000	96.500000	3.080000	2.581250	16.892500	0.000000	0.000000	3.000000	2.0000
50%	19.200000	6.000000	196.300000	123.000000	3.695000	3.325000	17.710000	0.000000	0.000000	4.000000	2.0000
75%	22.800000	8.000000	326.000000	180.000000	3.920000	3.610000	18.900000	1.000000	1.000000	4.000000	4.0000
max	33.900000	8.000000	472.000000	335.000000	4.930000	5.424000	22.900000	1.000000	1.000000	5.000000	8.0000

These are usually the first steps in exploring the data.

Data structures and types¶

pandas has two main data types: Series and DataFrame. These are analogous to vectors and matrices, in that a Series is 1-dimensional while a DataFrame is 2-dimensional.

pandas.Series¶

The Series object holds data from a single input variable, and is required, much like numpy arrays, to be homogeneous in type. You can create Series objects from lists or numpy arrays quite easily

In [10]:

s = pd.Series([1,3,5,np.nan, 9, 13])
s

Out[10]:

0     1.0
1     3.0
2     5.0
3     NaN
4     9.0
5    13.0
dtype: float64

In [11]:

s2 = pd.Series(np.arange(1,20))
s2

Out[11]:

0      1
1      2
2      3
3      4
4      5
5      6
6      7
7      8
8      9
9     10
10    11
11    12
12    13
13    14
14    15
15    16
16    17
17    18
18    19
dtype: int64

You can access elements of a Series much like a dict

In [12]:

s2[4]

Out[12]:

There is no requirement that the index of a Series has to be numeric. It can be any kind of scalar object

In [13]:

s3 = pd.Series(np.random.normal(0,1, (5,)), index = ['a','b','c','d','e'])
s3

Out[13]:

a    0.375096
b    1.766950
c    0.133634
d   -0.188296
e   -1.206115
dtype: float64

In [14]:

s3['d']

Out[14]:

-0.18829610291631763

In [15]:

s3['a':'d']

Out[15]:

a    0.375096
b    1.766950
c    0.133634
d   -0.188296
dtype: float64

Well, slicing worked, but it gave us something different than expected. It gave us both the start and end of the slice, which is unlike what we've encountered so far!!

It turns out that in pandas, slicing by index actually does this. It is a discrepancy from numpy and Python in general that we have to be careful about.

You can extract the actual values into a numpy array

In [16]:

s3.to_numpy()

Out[16]:

array([ 0.37509614,  1.76694952,  0.13363448, -0.1882961 , -1.20611518])

In fact, you'll see that much of pandas' structures are build on top of numpy arrays. This is a good thing, since you can take advantage of the powerful numpy functions that are built for fast, efficient scientific computing.

Making the point about slicing again,

In [17]:

s3.to_numpy()[0:3]

Out[17]:

array([0.37509614, 1.76694952, 0.13363448])

This is different from index-based slicing done earlier.

pandas.DataFrame¶

The DataFrame object holds a rectangular data set. Each column of a DataFrame is a Series object. This means that each column of a DataFrame must be comprised of data of the same type, but different columns can hold data of different types. This structure is extremely useful in practical data science. The invention of this structure was, in my opinion, transformative in making Python an effective data science tool.

Creating a DataFrame¶

The DataFrame can be created by importing data, as we saw in the previous section. It can also be created by a few methods within Python.

First, it can be created from a 2-dimensional numpy array.

In [18]:

rng = np.random.RandomState(25)
d1 = pd.DataFrame(rng.normal(0,1, (4,5)))
d1

Out[18]:

	0	1	2	3	4
0	0.228273	1.026890	-0.839585	-0.591182	-0.956888
1	-0.222326	-0.619915	1.837905	-2.053231	0.868583
2	-0.920734	-0.232312	2.152957	-1.334661	0.076380
3	-1.246089	1.202272	-1.049942	1.056610	-0.419678

You will notice that it creates default column names, that are merely the column number, starting from 0. We can also create the column names and row index (similar to the Series index we saw earlier) directly during creation.

In [19]:

d2 = pd.DataFrame(rng.normal(0,1, (4, 5)), 
                  columns = ['A','B','C','D','E'], 
                  index = ['a','b','c','d'])
d2

Out[19]:

	A	B	C	D	E
a	2.294842	-2.594487	2.822756	0.680889	-1.577693
b	-1.976254	0.533340	-0.290870	-0.513520	1.982626
c	0.226001	-1.839905	1.607671	0.388292	0.399732
d	0.405477	0.217002	-0.633439	0.246622	-1.939546

We could also create a DataFrame from a list of lists, as long as things line up, just as we showed for numpy arrays. However, to me, other ways, including the dict method below, make more sense.

We can change the column names (which can be extracted and replaced with the columns attribute) and the index values (using the index attribute).

In [20]:

d2.columns

Out[20]:

Index(['A', 'B', 'C', 'D', 'E'], dtype='object')

In [21]:

d2.columns = pd.Index(['V'+str(i) for i in range(1,6)]) # Index creates the right objects for both column names and row names, which can be extracted and changed with the `index` attribute
d2

Out[21]:

	V1	V2	V3	V4	V5
a	2.294842	-2.594487	2.822756	0.680889	-1.577693
b	-1.976254	0.533340	-0.290870	-0.513520	1.982626
c	0.226001	-1.839905	1.607671	0.388292	0.399732
d	0.405477	0.217002	-0.633439	0.246622	-1.939546

Exercise: Can you explain what I did in the list comprehension above? The key points are understanding str and how I constructed the range.

In [22]:

d2.index = ['o1','o2','o3','o4']
d2

Out[22]:

	V1	V2	V3	V4	V5
o1	2.294842	-2.594487	2.822756	0.680889	-1.577693
o2	-1.976254	0.533340	-0.290870	-0.513520	1.982626
o3	0.226001	-1.839905	1.607671	0.388292	0.399732
o4	0.405477	0.217002	-0.633439	0.246622	-1.939546

You can also extract data from a homogeneous DataFrame to a numpy array

In [23]:

d1.to_numpy()

Out[23]:

array([[ 0.22827309,  1.0268903 , -0.83958485, -0.59118152, -0.9568883 ],
       [-0.22232569, -0.61991511,  1.83790458, -2.05323076,  0.86858305],
       [-0.92073444, -0.23231186,  2.1529569 , -1.33466147,  0.07637965],
       [-1.24608928,  1.20227231, -1.04994158,  1.05661011, -0.41967767]])

It turns out that you can use to_numpy for a non-homogeneous DataFrame as well. numpy just makes it homogeneous by assigning each column the data type object. This also limits what you can do in numpy with the array and may require changing data types using the astype function. There is some more detail about the object data type in the Python Tools for Data Science (notebook, PDF) document.

The other easy way to create a DataFrame is from a dict object, where each component object is either a list or a numpy array, and is homogeneous in type. One exception is if a component is of size 1; then it is repeated to meet the needs of the DataFrame's dimensions

In [24]:

df = pd.DataFrame({
    'A':3.,
    'B':rng.random_sample(5),
    'C': pd.Timestamp('20200512'),
    'D': np.array([6] * 5),
    'E': pd.Categorical(['yes','no','no','yes','no']),
    'F': 'NIH'})
df

Out[24]:

	A	B	C	D	E	F
0	3.0	0.958092	2020-05-12	6	yes	NIH
1	3.0	0.883201	2020-05-12	6	no	NIH
2	3.0	0.295432	2020-05-12	6	no	NIH
3	3.0	0.512376	2020-05-12	6	yes	NIH
4	3.0	0.088702	2020-05-12	6	no	NIH

In [25]:

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5 entries, 0 to 4
Data columns (total 6 columns):
 #   Column  Non-Null Count  Dtype         
---  ------  --------------  -----         
 0   A       5 non-null      float64       
 1   B       5 non-null      float64       
 2   C       5 non-null      datetime64[ns]
 3   D       5 non-null      int64         
 4   E       5 non-null      category      
 5   F       5 non-null      object        
dtypes: category(1), datetime64[ns](1), float64(2), int64(1), object(1)
memory usage: 429.0+ bytes

We note that C is a date object, E is a category object, and F is a text/string object. pandas has excellent time series capabilities (having origins in FinTech), and the TimeStamp function creates datetime objects which can be queried and manipulated in Python. We'll describe category data in the next section.

You can also create a DataFrame where each column is composed of composite objects, like lists and dicts, as well. This might have limited value in some settings, but may be useful in others. In particular, this allows capabilities like the list-column construct in R tibbles. For example,

In [26]:

pd.DataFrame({'list' :[[1,2],[3,4],[5,6]],
             'tuple' : [('a','b'), ('c','d'), ('e','f')],
              'set' : [{'A','B','C'}, {'D','E'}, {'F'}], 
            'dicts' : [{'A': [1,2,3]}, {'B':[5,6,8]}, {'C': [3,9]}]})

Out[26]:

	list	tuple	set	dicts
0	[1, 2]	(a, b)	{A, B, C}	{'A': [1, 2, 3]}
1	[3, 4]	(c, d)	{D, E}	{'B': [5, 6, 8]}
2	[5, 6]	(e, f)	{F}	{'C': [3, 9]}

Working with a DataFrame¶

You can extract particular columns of a DataFrame by name

In [27]:

df['E']

Out[27]:

0    yes
1     no
2     no
3    yes
4     no
Name: E, dtype: category
Categories (2, object): [no, yes]

In [28]:

df['B']

Out[28]:

0    0.958092
1    0.883201
2    0.295432
3    0.512376
4    0.088702
Name: B, dtype: float64

There is also a shortcut for accessing single columns, using Python's dot (.) notation.

In [29]:

df.B

Out[29]:

0    0.958092
1    0.883201
2    0.295432
3    0.512376
4    0.088702
Name: B, dtype: float64

This notation can be more convenient if we need to perform operations on a single column. If we want to extract multiple columns, this notation will not work. Also, if we want to create new columns or replace existing columns, we need to use the array notation with the column name in quotes.

Let's look at slicing a DataFrame

Extracting rows and columns¶

There are two extractor functions in pandas:

loc extracts by label (index label, column label, slice of labels, etc.
iloc extracts by index (integers, slice objects, etc.

In [30]:

df2 = pd.DataFrame(rng.randint(0,10, (5,4)), 
                  index = ['a','b','c','d','e'],
                  columns = ['one','two','three','four'])
df2

Out[30]:

	one	two	three	four
a	5	3	2	8
b	9	3	0	5
c	8	4	3	3
d	5	2	7	1
e	6	7	8	7

First, let's see what naively slicing this DataFrame does.

In [31]:

df2['one']

Out[31]:

a    5
b    9
c    8
d    5
e    6
Name: one, dtype: int64

Ok, that works. It grabs one column from the dataset. How about the dot notation?

In [32]:

df2.one

Out[32]:

a    5
b    9
c    8
d    5
e    6
Name: one, dtype: int64

Let's see what this produces.

In [33]:

type(df2.one)

Out[33]:

pandas.core.series.Series

So this is a series, so we can potentially do slicing of this series.

In [34]:

df2.one['b']

Out[34]:

In [35]:

df2.one['b':'d']

Out[35]:

b    9
c    8
d    5
Name: one, dtype: int64

In [36]:

df2.one[:3]

Out[36]:

a    5
b    9
c    8
Name: one, dtype: int64

Ok, so we have all the Series slicing available. The problem here is in semantics, in that we are grabbing one column and then slicing the rows. That doesn't quite work with our sense that a DataFrame is a rectangle with rows and columns, and we tend to think of rows, then columns.

Let's see if we can do column slicing with this.

In [37]:

df2[:'two']

Out[37]:

	one	two	three	four
a	5	3	2	8
b	9	3	0	5
c	8	4	3	3
d	5	2	7	1
e	6	7	8	7

That's not what we want, of course. It's giving back the entire data frame. We'll come back to this.

In [38]:

df2[['one','three']]

Out[38]:

	one	three
a	5	2
b	9	0
c	8	3
d	5	7
e	6	8

That works correctly though. We can give a list of column names. Ok.

How about row slices?

In [39]:

#df2['a'] # Doesn't work
df2['a':'c'] 

Out[39]:

	one	two	three	four
a	5	3	2	8
b	9	3	0	5
c	8	4	3	3

Ok, that works. It slices rows, but includes the largest index, like a Series but unlike numpy arrays.

In [40]:

df2[0:2]

Out[40]:

	one	two	three	four
a	5	3	2	8
b	9	3	0	5

Slices by location work too, but use the numpy slicing rules.

This entire extraction method becomes confusing. Let's simplify things for this, and then move on to more consistent ways to extract elements of a DataFrame. Let's agree on two things. If we're going the direct extraction route,

We will extract single columns of a DataFrame with [] or ., i.e., df2['one'] or df2.one
We will extract slices of rows of a DataFrame using location only, i.e., df2[:3].

For everything else, we'll use two functions, loc and iloc.

loc extracts elements like a matrix, using index and columns
iloc extracts elements like a matrix, using location

In [41]:

df2.loc[:,'one':'three']

Out[41]:

	one	two	three
a	5	3	2
b	9	3	0
c	8	4	3
d	5	2	7
e	6	7	8

In [42]:

df2.loc['a':'d',:]

Out[42]:

	one	two	three	four
a	5	3	2	8
b	9	3	0	5
c	8	4	3	3
d	5	2	7	1

In [43]:

df2.loc['b', 'three']

Out[43]:

So loc works just like a matrix, but with pandas slicing rules (include largest index)

In [44]:

df2.iloc[:,1:4]

Out[44]:

	two	three	four
a	3	2	8
b	3	0	5
c	4	3	3
d	2	7	1
e	7	8	7

In [45]:

df2.iloc[1:3,:]

Out[45]:

	one	two	three	four
b	9	3	0	5
c	8	4	3	3

In [46]:

df2.iloc[1:3, 1:4]

Out[46]:

	two	three	four
b	3	0	5
c	4	3	3

iloc slices like a matrix, but uses numpy slicing conventions (does not include highest index)

If we want to extract a single element from a dataset, there are two functions available, iat and at, with behavior corresponding to iloc and loc, respectively.

In [50]:

df2.iat[2,3]

Out[50]:

In [ ]:

df2.at['b','three']

Boolean selection¶

We can also use tests to extract data from a DataFrame. For example, we can extract only rows where column labeled one is greater than 3.

In [ ]:

df2[df2.one > 3]

We can also do composite tests. Here we ask for rows where one is greater than 3 and three is less than 9

In [ ]:

df2[(df2.one > 3) & (df2.three < 9)]

`query`¶

DataFrame's have a query method allowing selection using a Python expression

In [ ]:

n = 10
df = pd.DataFrame(np.random.rand(n, 3), columns = list('abc'))
df

In [ ]:

df[(df.a < df.b) & (df.b < df.c)]

We can equivalently write this query as

In [ ]:

df.query('(a < b) & (b < c)')

Replacing values in a DataFrame¶

We can replace values within a DataFrame either by position or using a query.

In [ ]:

df2

In [ ]:

df2['one'] = [2,5,2,5,2]
df2

In [ ]:

df2.iat[2,3] = -9 # missing value
df2

Let's now replace values using replace which is more flexible.

In [ ]:

df2.replace(0, -9) # replace 0 with -9

In [ ]:

df2.replace({2: 2.5, 8: 6.5}) # multiple replacements

In [ ]:

df2.replace({'one': {5: 500}, 'three': {0: -9, 8: 800}}) 
# different replacements in different columns

See more examples in the documentation

Categorical data¶

pandas provides a Categorical function and a category object type to Python. This type is analogous to the factor data type in R. It is meant to address categorical or discrete variables, where we need to use them in analyses. Categorical variables typically take on a small number of unique values, like gender, blood type, country of origin, race, etc.

You can create categorical Series in a couple of ways:

In [ ]:

s = pd.Series(['a','b','c'], dtype='category')

In [ ]:

df = pd.DataFrame({
    'A':3.,
    'B':rng.random_sample(5),
    'C': pd.Timestamp('20200512'),
    'D': np.array([6] * 5),
    'E': pd.Categorical(['yes','no','no','yes','no']),
    'F': 'NIH'})
df['F'].astype('category')

You can also create DataFrame's where each column is categorical

In [ ]:

df = pd.DataFrame({'A': list('abcd'), 'B': list('bdca')})
df_cat = df.astype('category')
df_cat.dtypes

You can explore categorical data in a variety of ways

In [ ]:

df_cat['A'].describe()

In [ ]:

df['A'].value_counts()

One issue with categories is that, if a particular level of a category is not seen before, it can create an error. So you can pre-specify the categories you expect

In [ ]:

df_cat['B'] = pd.Categorical(list('aabb'), categories = ['a','b','c','d'])
df_cat['B'].value_counts()

Re-organizing categories¶

In categorical data, there is often the concept of a "first" or "reference" category, and an ordering of categories. This tends to be important in both visualization as well as in regression modeling. Both aspects of a category can be addressed using the reorder_categories function.

In our earlier example, we can see that the A variable has 4 categories, with the "first" category being "a".

In [ ]:

df_cat.A

Suppose we want to change this ordering to the reverse ordering, where "d" is the "first" category, and then it goes in reverse order.

In [ ]:

df_cat['A'] = df_cat.A.cat.reorder_categories(['d','c','b','a'])
df_cat.A

Missing data¶

Both numpy and pandas allow for missing values, which are a reality in data science. The missing values are coded as np.nan. Let's create some data and force some missing values

In [ ]:

df = pd.DataFrame(np.random.randn(5, 3), index = ['a','c','e', 'f','g'], columns = ['one','two','three']) # pre-specify index and column names
df['four'] = 20 # add a column named "four", which will all be 20
df['five'] = df['one'] > 0
df

In [ ]:

df2 = df.reindex(['a','b','c','d','e','f','g'])
df2.style.applymap(lambda x: 'background-color:yellow', subset = pd.IndexSlice[['b','d'],:])

The code above is creating new blank rows based on the new index values, some of which are present in the existing data and some of which are missing.

We can create masks of the data indicating where missing values reside in a data set.

In [ ]:

df2.isna()

In [ ]:

df2['one'].notna()

We can obtain complete data by dropping any row that has any missing value. This is called complete case analysis, and you should be very careful using it. It is only valid if we belive that the missingness is missing at random, and not related to some characteristic of the data or the data gathering process.

In [ ]:

df2.dropna(how='any')

You can also fill in, or impute, missing values. This can be done using a single value..

In [ ]:

out1 = df2.fillna(value = 5)

out1.style.applymap(lambda x: 'background-color:yellow', subset = pd.IndexSlice[['b','d'],:])

or a computed value like a column mean

In [ ]:

df3 = df2.copy()
df3 = df3.select_dtypes(exclude=[object])   # remove non-numeric columns
out2 = df3.fillna(df3.mean())  # df3.mean() computes column-wise means

out2.style.applymap(lambda x: 'background-color:yellow', subset = pd.IndexSlice[['b','d'],:])

You can also impute based on the principle of last value carried forward which is common in time series. This means that the missing value is imputed with the previous recorded value.

In [ ]:

out3 = df2.fillna(method = 'ffill') # Fill forward

out3.style.applymap(lambda x: 'background-color:yellow', subset = pd.IndexSlice[['b','d'],:])

In [ ]:

out4 = df2.fillna(method = 'bfill') # Fill backward

out4.style.applymap(lambda x: 'background-color:yellow', subset = pd.IndexSlice[['b','d'],:])

Data transformation¶

Arithmetic operations¶

If you have a Series or DataFrame that is all numeric, you can add or multiply single numbers to all the elements together.

In [ ]:

A = pd.DataFrame(np.random.randn(4,5))
print(A)

In [ ]:

print(A + 6)

In [ ]:

print(A * -10)

If you have two compatible (same dimension) numeric DataFrames, you can add, subtract, multiply and divide elementwise

In [ ]:

B = pd.DataFrame(np.random.randn(4,5) + 4)
print(A + B)

In [ ]:

print(A * B)

If you have a Series with the same number of elements as the number of columns of a DataFrame, you can do arithmetic operations, with each element of the Series acting upon each column of the DataFrame

In [ ]:

c = pd.Series([1,2,3,4,5])
print(A + c)

In [ ]:

print(A * c)

This idea can be used to standardize a dataset, i.e. make each column have mean 0 and standard deviation 1.

In [ ]:

means = A.mean(axis=0)
stds = A.std(axis = 0)

(A - means)/stds

Concatenation of data sets¶

Let's create some example data sets

In [ ]:

df1 = pd.DataFrame({'A': ['a'+str(i) for i in range(4)],
    'B': ['b'+str(i) for i in range(4)],
    'C': ['c'+str(i) for i in range(4)],
    'D': ['d'+str(i) for i in range(4)]})

df2 =  pd.DataFrame({'A': ['a'+str(i) for i in range(4,8)],
    'B': ['b'+str(i) for i in range(4,8)],
    'C': ['c'+str(i) for i in range(4,8)],
    'D': ['d'+str(i) for i in range(4,8)]})
df3 =  pd.DataFrame({'A': ['a'+str(i) for i in range(8,12)],
    'B': ['b'+str(i) for i in range(8,12)],
    'C': ['c'+str(i) for i in range(8,12)],
    'D': ['d'+str(i) for i in range(8,12)]})

We can concatenate these DataFrame objects by row

In [ ]:

row_concatenate = pd.concat([df1, df2, df3])
print(row_concatenate)

This stacks the dataframes together. They are literally stacked, as is evidenced by the index values being repeated.

This same exercise can be done by the append function

In [ ]:

df1.append(df2).append(df3)

Suppose we want to append a new row to df1. Lets create a new row.

In [ ]:

new_row = pd.Series(['n1','n2','n3','n4'])
pd.concat([df1, new_row])

That's a lot of missing values. The issue is that the we don't have column names in the new_row, and the indices are the same, so pandas tries to append it my making a new column. The solution is to make it a DataFrame.

In [ ]:

new_row = pd.DataFrame([['n1','n2','n3','n4']], columns = ['A','B','C','D'])
print(new_row)

In [ ]:

pd.concat([df1, new_row])

or

In [ ]:

df1.append(new_row)

Adding columns¶

In [ ]:

pd.concat([df1,df2,df3], axis = 1)

The option axis=1 ensures that concatenation happens by columns. The default value axis = 0 concatenates by rows.

Let's play a little game. Let's change the column names of df2 and df3 so they are not the same as df1.

In [ ]:

df2.columns = ['E','F','G','H']
df3.columns = ['A','D','F','H']
pd.concat([df1,df2,df3])

Now pandas ensures that all column names are represented in the new data frame, but with missing values where the row indices and column indices are mismatched. Some of this can be avoided by only joining on common columns. This is done using the join option ir concat. The default value is 'outer`, which is what you see. above

In [ ]:

pd.concat([df1, df3], join = 'inner')

You can do the same thing when joining by rows, using axis = 0 and join="inner" to only join on rows with matching indices. Reminder that the indices are just labels and happen to be the row numbers by default.

Merging data sets¶

For this section we'll use a set of data from a survey, also used by Daniel Chen in "Pandas for Everyone"

In [ ]:

person = pd.read_csv('data/survey_person.csv')
site = pd.read_csv('data/survey_site.csv')
survey = pd.read_csv('data/survey_survey.csv')
visited = pd.read_csv('data/survey_visited.csv')

In [ ]:

print(person)

In [ ]:

print(site)

In [ ]:

print(survey)

In [ ]:

print(visited)

There are basically four kinds of joins:

pandas	R	SQL	Description
left	left_join	left outer	keep all rows on left
right	right_join	right outer	keep all rows on right
outer	outer_join	full outer	keep all rows from both
inner	inner_join	inner	keep only rows with common keys

The terms left and right refer to which data set you call first and second respectively.

We start with an left join

In [ ]:

s2v_merge = survey.merge(visited, left_on = 'taken',right_on = 'ident', how = 'left')

In [ ]:

print(s2v_merge)

Here, the left dataset is survey and the right one is visited. Since we're doing a left join, we keed all the rows from survey and add columns from visited, matching on the common key, called "taken" in one dataset and "ident" in the other. Note that the rows of visited are repeated as needed to line up with all the rows with common "taken" values.

We can now add location information, where the common key is the site code

In [ ]:

s2v2loc_merge = s2v_merge.merge(site, how = 'left', left_on = 'site', right_on = 'name')
print(s2v2loc_merge)

Lastly, we add the person information to this dataset.

In [ ]:

merged = s2v2loc_merge.merge(person, how = 'left', left_on = 'person', right_on = 'ident')
print(merged.head())

You can merge based on multiple columns as long as they match up.

In [ ]:

ps = person.merge(survey, left_on = 'ident', right_on = 'person')
vs = visited.merge(survey, left_on = 'ident', right_on = 'taken')
print(ps)

In [ ]:

print(vs)

In [ ]:

ps_vs = ps.merge(vs, 
                left_on = ['ident','taken', 'quant','reading'],
                right_on = ['person','ident','quant','reading']) # The keys need to correspond
ps_vs.head()

Note that since there are common column names, the merge appends _x and _y to denote which column came from the left and right, respectively.

Tidy data principles and reshaping datasets¶

The tidy data principle is a principle espoused by Dr. Hadley Wickham, one of the foremost R developers. Tidy data is a structure for datasets to make them more easily analyzed on computers. The basic principles are

Each row is an observation
Each column is a variable
Each type of observational unit forms a table

Tidy data is tidy in one way. Untidy data can be untidy in many ways

Let's look at some examples.

In [ ]:

from glob import glob
filenames = sorted(glob('data/table*.csv')) # find files matching pattern. I know there are 6 of them
table1, table2, table3, table4a, table4b, table5 = [pd.read_csv(f) for f in filenames] # Use a list comprehension

This code imports data from 6 files matching a pattern. Python allows multiple assignments on the left of the =, and as each dataset is imported, it gets assigned in order to the variables on the left. In the second line I sort the file names so that they match the order in which I'm storing them in the 3rd line. The function glob does pattern-matching of file names.

The following tables refer to the number of TB cases and population in Afghanistan, Brazil and China in 1999 and 2000

In [ ]:

print(table1)

In [ ]:

print(table2)

In [ ]:

print(table3)

In [ ]:

print(table4a) # cases

In [ ]:

print(table4b) # population

In [ ]:

print(table5)

Exercise: Describe why and why not each of these datasets are tidy.

Melting (unpivoting) data¶

Melting is the operation of collapsing multiple columns into 2 columns, where one column is formed by the old column names, and the other by the corresponding values. Some columns may be kept fixed and their data are repeated to maintain the interrelationships between the variables.

We'll start with loading some data on income and religion in the US from the Pew Research Center.

In [ ]:

pew = pd.read_csv('data/pew.csv')
print(pew.head())

This dataset is considered in "wide" format. There are several issues with it, including the fact that column headers have data. Those column headers are income groups, that should be a column by tidy principles. Our job is to turn this dataset into "long" format with a column for income group.

We will use the function melt to achieve this. This takes a few parameters:

id_vars is a list of variables that will remain as is
value_vars is a list of column nmaes that we will melt (or unpivot). By default, it will melt all columns not mentioned in id_vars
var_name is a string giving the name of the new column created by the headers (default: variable)
value_name is a string giving the name of the new column created by the values (default: value)

In [ ]:

pew_long = pew.melt(id_vars = ['religion'], var_name = 'income_group', value_name = 'count')
print(pew_long.head())

Separating columns containing multiple variables¶

We will use an Ebola dataset to illustrate this principle

In [ ]:

ebola = pd.read_csv('data/country_timeseries.csv')
print(ebola.head())

Note that for each country we have two columns -- one for cases (number infected) and one for deaths. Ideally we want one column for country, one for cases and one for deaths.

The first step will be to melt this data sets so that the column headers in question from a column and the corresponding data forms a second column.

In [ ]:

ebola_long = ebola.melt(id_vars = ['Date','Day'])
print(ebola_long.head())

We now need to split the data in the variable column to make two columns. One will contain the country name and the other either Cases or Deaths. We will use some string manipulation functions that we will see later to achieve this.

In [ ]:

variable_split = ebola_long['variable'].str.split('_', expand=True) # split on the `_` character
print(variable_split[:5])

The expand=True option forces the creation of an DataFrame rather than a list

In [ ]:

type(variable_split)

We can now concatenate this to the original data

In [ ]:

variable_split.columns = ['status','country']

ebola_parsed = pd.concat([ebola_long, variable_split], axis = 1)

ebola_parsed.drop('variable', axis = 1, inplace=True) # Remove the column named "variable" and replace the old data with the new one in the same location

print(ebola_parsed.head())

Pivot/spread datasets¶

If we wanted to, we could also make two columns based on cases and deaths, so for each country and date you could easily read off the cases and deaths. This is achieved using the pivot_table function.

In the pivot_table syntax, index refers to the columns we don't want to change, columns refers to the column whose values will form the column names of the new columns, and values is the name of the column that will form the values in the pivoted dataset.

In [ ]:

ebola_parsed.pivot_table(index = ['Date','Day', 'country'], columns = 'status', values = 'value')

This creates something called MultiIndex in the pandas DataFrame. This is useful in some advanced cases, but here, we just want a normal DataFrame back. We can achieve that by using the reset_index function.

In [ ]:

ebola_parsed.pivot_table(index = ['Date','Day','country'], columns = 'status', values = 'value').reset_index()

Pivoting is a 2-column to many-column operation, with the number of columns formed depending on the number of unique values present in the column of the original data that is entered into the columns argument of pivot_table

Exercise: Load the file weather.csv into Python and work on making it a tidy dataset. It requires melting and pivoting. The dataset comprises of the maximun and minimum temperatures recorded each day in 2010. There are lots of missing value. Ultimately we want columns for days of the month, maximum temperature and minimum tempearture along with the location ID, the year and the month.

Data aggregation and split-apply-combine¶

We'll use the Gapminder dataset for this section

In [ ]:

df = pd.read_csv('data/gapminder.tsv', sep = '\t') # data is tab-separated, so we use `\t` to specify that

The paradigm we will be exploring is often called split-apply-combine or MapReduce or grouped aggregation. The basic idea is that you split a data set up by some feature, apply a recipe to each piece, compute the result, and then put the results back together into a dataset. This can be described in teh following schematic.

pandas is set up for this. It features the groupby function that allows the "split" part of the operation. We can then apply a function to each part and put it back together. Let's see how.

In [ ]:

df.head()

In [ ]:

f"This dataset has {len(df['country'].unique())} countries in it"

One of the variables in this dataset is life expectancy at birth, lifeExp. Suppose we want to find the average life expectancy of each country over the period of study.

In [ ]:

df.groupby('country')['lifeExp'].mean()

So what's going on here? First, we use the groupby function, telling pandas to split the dataset up by values of the column country.

In [ ]:

df.groupby('country')

pandas won't show you the actual data, but will tell you that it is a grouped dataframe object. This means that each element of this object is a DataFrame with data from one country.

In [ ]:

df.groupby('country').ngroups

In [ ]:

df.groupby('country').get_group('United Kingdom')

In [ ]:

type(df.groupby('country').get_group('United Kingdom'))

In [ ]:

avg_lifeexp_country = df.groupby('country').lifeExp.mean()
avg_lifeexp_country['United Kingdom']

In [ ]:

df.groupby('country').get_group('United Kingdom').lifeExp.mean()

Let's look at if life expectancy has gone up over time, by continent

In [ ]:

df.groupby(['continent','year']).lifeExp.mean()

In [ ]:

avg_lifeexp_continent_yr = df.groupby(['continent','year']).lifeExp.mean().reset_index()
avg_lifeexp_continent_yr

In [ ]:

type(avg_lifeexp_continent_yr)

The aggregation function, in this case mean, does both the "apply" and "combine" parts of the process.

We can do quick aggregations with pandas

In [ ]:

df.groupby('continent').lifeExp.describe()

In [ ]:

df.groupby('continent').nth(10) # Tenth observation in each group

You can also use functions from other modules, or your own functions in this aggregation work.

In [ ]:

df.groupby('continent').lifeExp.agg(np.mean)

In [ ]:

def my_mean(values):
    n = len(values)
    sum = 0
    for value in values:
        sum += value
    return(sum/n)

df.groupby('continent').lifeExp.agg(my_mean)

You can do many functions at once

In [ ]:

df.groupby('year').lifeExp.agg([np.count_nonzero, np.mean, np.std])

You can also aggregate on different columns at the same time by passing a dict to the agg function

In [ ]:

df.groupby('year').agg({'lifeExp': np.mean,'pop': np.median,'gdpPercap': np.median}).reset_index()

Transformation¶

You can do grouped transformations using this same method. We will compute the z-score for each year, i.e. we will substract the average life expectancy and divide by the standard deviation

In [ ]:

def my_zscore(values):
    m = np.mean(values)
    s = np.std(values)
    return((values - m)/s)

In [ ]:

df.groupby('year').lifeExp.transform(my_zscore)

In [ ]:

df['lifeExp_z'] = df.groupby('year').lifeExp.transform(my_zscore)

In [ ]:

df.groupby('year').lifeExp_z.mean()

Filter¶

We can split the dataset by values of one variable, and filter out those splits that fail some criterion. The following code only keeps countries with a population of at least 10 million at some point during the study period

In [ ]:

df.groupby('country').filter(lambda d: d['pop'].max() > 10000000)

Table of Contents

Pandas¶

Introduction¶

Starting pandas¶

Data import and export¶

Exploring a data set¶

Data structures and types¶

pandas.Series¶

pandas.DataFrame¶

Creating a DataFrame¶

Working with a DataFrame¶

Extracting rows and columns¶

Boolean selection¶

query¶

Replacing values in a DataFrame¶

Categorical data¶

Re-organizing categories¶

Missing data¶

Data transformation¶

Arithmetic operations¶

Concatenation of data sets¶

Adding columns¶

Merging data sets¶

Tidy data principles and reshaping datasets¶

Melting (unpivoting) data¶

Separating columns containing multiple variables¶

Pivot/spread datasets¶

Data aggregation and split-apply-combine¶

Transformation¶

Filter¶

`query`¶