Lecture 10 (4/18/22)

Announcements

  • Problem set 2 due tonight at 11:59pm

  • Change of grading basis deadline 4/22 (shooting to have pset 1 & 2, labs 1 & 2 graded by then)

Last time we covered:

  • graphing with matplotlib

  • graphing best practices

  • graphing with seaborn

Today’s agenda:

  • Cleaning and structuring data with pandas

Cleaning data: principles and tools

import pandas as pd
import numpy as np

What are the sorts of things we (typically) don’t want in our data?

What do the things we don’t want in our data look like in python?

Duplicates

# Duplicates: what do they look like?

df = pd.DataFrame({
    "sample_int": np.random.randint(low = 1, high = 10, size = 10)
})

df
# df[df.sample_int == 1]
sample_int
0 9
1 9
2 2
3 5
4 8
5 9
6 5
7 2
8 4
9 8

Duplicates are hard to detect without a more careful search because they may look like totally normal data otherwise.

Missing data

# Missing data: what does it look like?

df.iloc[0] = np.nan
df.iloc[1] = None

df # is this what we expected?
sample_int
0 NaN
1 NaN
2 2.0
3 5.0
4 8.0
5 9.0
6 5.0
7 2.0
8 4.0
9 8.0
df['nans'] = np.nan
df['nones'] = None

df
sample_int nans nones
0 NaN NaN None
1 NaN NaN None
2 2.0 NaN None
3 5.0 NaN None
4 8.0 NaN None
5 9.0 NaN None
6 5.0 NaN None
7 2.0 NaN None
8 4.0 NaN None
9 8.0 NaN None

Note: sometimes missing data can take other forms, for example empty strings.

This is especially true when your data has had some intermediate processing like being stored as a csv.

Outliers

# Outliers: what do they look like?

df.iloc[9, 0] = 400

df
sample_int nans nones
0 NaN NaN None
1 NaN NaN None
2 2.0 NaN None
3 5.0 NaN None
4 8.0 NaN None
5 9.0 NaN None
6 5.0 NaN None
7 2.0 NaN None
8 4.0 NaN None
9 400.0 NaN None

Be careful here!

Not every unexpected data point is an outlier…

Aside: why do we want to detect these things in our data?

# A simple illustration

df

np.mean(df.sample_int)

# np.sum(df.sample_int) / len(df.sample_int)

# len(df.sample_int)
# np.sum(df.sample_int) / 8



np.median(df.sample_int) # uh oh...
nan

Take-away: these things can impact our analyses in lots of ways, which is why we want to find them!

How do we check for stuff we don’t want in our data?

Duplicates

# Duplicates

df[df.duplicated()] # just show the info that's duplicated after the first occurrence

# df[df.duplicated(keep = False)] # show all duplicated rows
# df
sample_int nans nones
1 NaN NaN None
6 5.0 NaN None
7 2.0 NaN None

Missing values

# Missing values

# Pandas tools: `pd.isna` and `pd.isnull`
pd.isna(df['sample_int'])
df[pd.isna(df['sample_int'])]

df[pd.isnull(df['sample_int'])] # Note: this is the same as `pd.isna` above
sample_int nans nones
0 NaN NaN None
1 NaN NaN None
# Numpy tools: `np.isnan` to detect NaNs
df[np.isnan(df['sample_int'])]

# But watch out! 
np.isnan(np.nan)
# np.isnan(None) # that's weird...
True

Outliers

This is one area where graphing our data can be really helpful!

anscombe

How do we get rid of stuff we don’t want in our data?

Our options are:

  1. Remove obervations (or otherwise ignore them)

  2. Fill in the observations (missing values only)

Duplicates

With duplicate data, the best thing is most often to remove it (unless the duplication is expected)

df_no_dupes = df.drop_duplicates(ignore_index = True) # Note: we need this `ignore_index = True`

df_no_dupes
sample_int nans nones
0 NaN NaN None
1 2.0 NaN None
2 5.0 NaN None
3 8.0 NaN None
4 9.0 NaN None
5 4.0 NaN None
6 400.0 NaN None

Missing values

With missing values, it may be best to drop missing values, or fill them in with a reasonable value.

More info on missing value handling here.

Dropping missing values: why might we do that?

# Dropping NAs

df_no_na = df.dropna() # can specify an `axis`: 0 for columns, 1 for rows containing missing values

df_no_na # What happened here??
sample_int nans nones
df_no_na = df.dropna(how = "all") 
# how = "all" will drop rows where *all* values are NaN
# how = "any" (default) will drop rows where *any* column value is NaN

df_no_na # that's better
sample_int nans nones
2 2.0 NaN None
3 5.0 NaN None
4 8.0 NaN None
5 9.0 NaN None
6 5.0 NaN None
7 2.0 NaN None
8 4.0 NaN None
9 400.0 NaN None

Filling in missing values: Why might we do that instead? (and what to put there?)

The pandas fillna function provides a few convenient ways to fill in missing data

# Can provide a specific number

df_no_na = df.fillna(0) 

# df_no_na = df.fillna(df.mean()) # note this can be handy
# df_no_na = df.fillna(np.nanmedian(df['sample_int'])) 

df_no_na
sample_int nans nones
0 0.0 0.0 0
1 0.0 0.0 0
2 2.0 0.0 0
3 5.0 0.0 0
4 8.0 0.0 0
5 9.0 0.0 0
6 5.0 0.0 0
7 2.0 0.0 0
8 4.0 0.0 0
9 400.0 0.0 0
# Can also use the previous or next value in the sequence (handy for time series data)

df.iloc[4, 0] = np.nan

df

df_no_na = df.fillna(method = 'pad',
                     limit = 1 # optional limit argument says how many consecutive times to do this
                    )

df_no_na
sample_int nans nones
0 NaN NaN None
1 NaN NaN None
2 2.0 NaN None
3 5.0 NaN None
4 5.0 NaN None
5 9.0 NaN None
6 5.0 NaN None
7 2.0 NaN None
8 4.0 NaN None
9 400.0 NaN None

Finally, pandas also provides tools for using interpolation to guess the missing value.

We won’t get into that here, but you can check out this page if you’re curious.

Outliers

When you have data that includes large outliers, it may be okay to remove them from your data, but you should do this with caution!

As a general rule, only remove outliers when you have a good justification for doing so.

What constitutes a good justification?

  • If for whatever reason your analysis is only interested in values within a particular range

  • Or, more often, if you have strong reason to suspect that a datapoint was generated differently than the rest (for example, somebody falling asleep at the keyboard)

In these cases, you typically set a criterion and remove rows that meet that criterion.

np.std(df.sample_int) # can specify e.g., values > X standard deviations above the mean
thresh = np.mean(df.sample_int) + 3*np.std(df.sample_int)

np.nanquantile(df.sample_int, [0.95]) # or, similarly, the top N% of data points
thresh = np.nanquantile(df.sample_int, [0.95])[0]

thresh

df_no_outliers = df[df.sample_int <= thresh]

df_no_outliers
sample_int nans nones
2 2.0 NaN None
3 5.0 NaN None
5 9.0 NaN None
6 5.0 NaN None
7 2.0 NaN None
8 4.0 NaN None