Fixing values

Fixing Values¶

o far, we’ve dealt with structural issues in data. but there’s a lot more to cleaning.

Today, we’ll deal with how to fix the values within the data.

Cleaning Data review¶

Instead of more practice with these manipulations, below are more examples of cleaning data to see how these types of manipulations get used.
Your goal here is not to memorize every possible thing, but to build a general idea of what good data looks like and good habits for cleaning data and keeping it reproducible.

• Cleaning the Adult Dataset

• All Shades Also here are some tips on general data management and organization.

This article is a comprehensive discussion of data cleaning.

A Cleaning Data Recipe¶

not everything possible, but good enough for this course

1. Can you use parameters to read the data in better?

2. Fix the index and column headers (making these easier to use makes the rest easier)

3. Is the data strucutred well?

4. Are there missing values?

5. Do the datatypes match what you expect by looking at the head or a sample?

6. Are categorical variables represented in usable way?

7. Does your analysis require filtering or augmenting the data?

What is clean enough?¶

This is a great question, without an easy answer.

It depends on what you want to do. This is why it’s important to have potential questions in mind if you are cleaning data for others and why we often have to do a little bit more preparation after a dataset has been “cleaned”

whatever you do, document it

import pandas as pd
import seaborn as sns
import numpy as np #
na_toy_df = pd.DataFrame(data = [[1,3,4,5],[2 ,6, np.nan, ], [np.nan ]*4])

# make plots look nicer and increase font size
sns.set_theme(font_scale=2, palette='colorblind')
arabica_data_url = 'https://raw.githubusercontent.com/jldbc/coffee-quality-database/master/data/arabica_data_cleaned.csv'

coffee_df = pd.read_csv(arabica_data_url)


rhodyprog4ds_gh_events_url = 'https://api.github.com/orgs/rhodyprog4ds/events'
course_gh_df = pd.read_json(rhodyprog4ds_gh_events_url)

Missing Dtaa¶

Dealing with missing data is a whole research area. There isn’t one solution.

• in 2020 there was a whole workshop on missing

• one organizer is the main developer of sci-kit learn the ML package we will use soon. In a 2020 invited talk he listed more automatic handling as an active area of research and a development goal for sklearn.

• There are also many classic approaches both when training and when applying models.

• example application in breast cancer detection

Even in pandas, dealing with missing values is under experimentation as to how to represent it symbolically

Missing values causes the datatypes to change

We tend to store missing values as NaN or use the constants:

pd.NA, np.nan

Pandas makes that a special typed object, but converts the whole column to float

Numpy uses float value for NaN that is defined by IEEE floating point standard

Pandas gives a few basic tools for dealing with missing values :

• dropna

• fillna

type(pd.NA),type(np.nan)

We can see a few in this toy dataset

na_toy_df

Dropping Missing¶

Let’s try the default behavior of dropna

na_toy_df.dropna()

by default it drops all of the rows where any of the elements are missing (1 or more)

This is equivalent to:

na_toy_df.dropna(how='any', subset=na_toy_df.columns)

we can change how to its other mode:

na_toy_df.dropna(how='all', )

in 'all' mode it only drops rows where all of the values are missing

we can also change it to work along columns (axis=1) instead

na_toy_df.dropna(how='all',axis=1)

None of the columns are all missing so nothing is dropped

if only some of the columns, matter, we can say to only drop if any of those values are missing:

na_toy_df.dropna(subset=[0,1])

this means no drops, but if we change the important columns, it changes

na_toy_df.dropna(subset=[0,2],how='any')

Filling¶

Filling can be good if you know how to fill reasonably, but don’t have data to spare by dropping. For example

• you can approximate with another column

• you can approximate with that column from other rows

Special case, what if we’re filling a summary table?

• filling with a symbol for printing can be a good choice, but not for analysis.

toy_df_filled = na_toy_df.fillna(0)
toy_df_filled

Filling missing values in the coffee data¶

Let’s look at a real dataset now

coffee_df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1311 entries, 0 to 1310
Data columns (total 44 columns):
 #   Column                 Non-Null Count  Dtype  
---  ------                 --------------  -----  
 0   Unnamed: 0             1311 non-null   int64  
 1   Species                1311 non-null   object 
 2   Owner                  1304 non-null   object 
 3   Country.of.Origin      1310 non-null   object 
 4   Farm.Name              955 non-null    object 
 5   Lot.Number             270 non-null    object 
 6   Mill                   1001 non-null   object 
 7   ICO.Number             1163 non-null   object 
 8   Company                1102 non-null   object 
 9   Altitude               1088 non-null   object 
 10  Region                 1254 non-null   object 
 11  Producer               1081 non-null   object 
 12  Number.of.Bags         1311 non-null   int64  
 13  Bag.Weight             1311 non-null   object 
 14  In.Country.Partner     1311 non-null   object 
 15  Harvest.Year           1264 non-null   object 
 16  Grading.Date           1311 non-null   object 
 17  Owner.1                1304 non-null   object 
 18  Variety                1110 non-null   object 
 19  Processing.Method      1159 non-null   object 
 20  Aroma                  1311 non-null   float64
 21  Flavor                 1311 non-null   float64
 22  Aftertaste             1311 non-null   float64
 23  Acidity                1311 non-null   float64
 24  Body                   1311 non-null   float64
 25  Balance                1311 non-null   float64
 26  Uniformity             1311 non-null   float64
 27  Clean.Cup              1311 non-null   float64
 28  Sweetness              1311 non-null   float64
 29  Cupper.Points          1311 non-null   float64
 30  Total.Cup.Points       1311 non-null   float64
 31  Moisture               1311 non-null   float64
 32  Category.One.Defects   1311 non-null   int64  
 33  Quakers                1310 non-null   float64
 34  Color                  1044 non-null   object 
 35  Category.Two.Defects   1311 non-null   int64  
 36  Expiration             1311 non-null   object 
 37  Certification.Body     1311 non-null   object 
 38  Certification.Address  1311 non-null   object 
 39  Certification.Contact  1311 non-null   object 
 40  unit_of_measurement    1311 non-null   object 
 41  altitude_low_meters    1084 non-null   float64
 42  altitude_high_meters   1084 non-null   float64
 43  altitude_mean_meters   1084 non-null   float64
dtypes: float64(16), int64(4), object(24)
memory usage: 450.8+ KB

The ‘Lot.Number’ has a lot of NaN values, how can we explore it?

We can look at the type:

coffee_df['Lot.Number'].dtype

And we can look at the value counts.

coffee_df['Lot.Number'].value_counts()

We see that a lot are ‘1’, maybe we know that when the data was collected, if the Farm only has one lot, some people recorded ‘1’ and others left it as missing. So we could fill in with 1:

coffee_df['Lot.Number'].fillna('1').head()

coffee_df['Lot.Number'].head()

Note that even after we called fillna we display it again and the original data is unchanged. To save the filled in column, technically we have a few choices:

• fa-ban use the inplace parameter. This doesn’t offer performance advantages, but does It still copies the object, but then reassigns the pointer. Its under discussion to deprecate

• fa-check write to a new DataFrame

• fa-check add a column

we will add a column

coffee_df['lot_number_clean'] = coffee_df['Lot.Number'].fillna('1')

coffee_df.head(1)

coffee_df.shape

Dropping¶

Dropping is a good choice when you otherwise have a lot of data and the data is missing at random.

Dropping can be risky if it’s not missing at random. For example, if we saw in the coffee data that one of the scores was missing for all of the rows from one country, or even just missing more often in one country, that could bias our results.

here will will focus on how this impacts how much data we have:

coffee_df.dropna().shape

we lose a lot this way.

We could instead tell it to only drop rows with NaN in a subset of the columns.

coffee_df.dropna(subset=['altitude_low_meters']).shape

Now, it drops any row with one or more NaN values in that column.

In the Open Policing Project Data Summary we saw that they made a summary information that showed which variables had at least 70% not missing values. We can similarly choose to keep only variables that have more than a specific threshold of data, using the thresh parameter and axis=1 to drop along columns.

n_rows, _ = coffee_df.shape
coffee_df.dropna(thresh=.7*n_rows, axis=1).shape

Inconsistent values¶

This was one of the things that many of you anticipated or had observed. A useful way to investigate for this, is to use value_counts and sort them alphabetically by the values from the original data, so that similar ones will be consecutive in the list. Once we have the value_counts() Series, the values from the coffee_df become the index, so we use sort_index.

Let’s look at the In.Country.Partner column

coffee_df['In.Country.Partner'].value_counts().sort_index()

We can see there’s only one Blossom Valley International\n but 58 Blossom Valley International, the former is likely a typo, especially since \n is a special character for a newline. Similarly, with ‘Specialty Coffee Ass’ and ‘Specialty Coffee Association’.

partner_corrections = {'Blossom Valley International\n':'Blossom Valley International',
  'Specialty Coffee Ass':'Specialty Coffee Association'}
coffee_df['in_country_partner_clean'] = coffee_df['In.Country.Partner'].replace(
  to_replace=partner_corrections)
coffee_df['in_country_partner_clean'].value_counts().sort_index()

Multiple values in a single column¶

Let’s look at the column about the bag weights

coffee_df['Bag.Weight'].head()

it has both the value and the units in a single column, which is not what we want.

This would be better in two separate columns

bag_df = coffee_df['Bag.Weight'].str.split(' ').apply(pd.Series).rename({0:'bag_weight_clean',
                                                                1:'bag_weight_unit'},
                                                              axis=1)
bag_df.head()

This:

• picks the column

• treats it as a string with the pandas Series attribute .str

• uses base python str.split to split at ' ' spaces and makes a list

• casts each list to Series with .apply(pd.Series)

• renames the resulting columns from being numbered to usable names rename({0:'bag_weight_clean', 1:'bag_weight_unit'}, axis=1)

The following subsections break down the casting and string methods in more detail

String methods¶

Python has a powerful string class. There is also an even more powerful string module

we only need the base str methods most of the time

example_str = 'kjksfjds sklfjsdl'
type(example_str)

Some helpful ones:

example_str.split()

this gives a list

you can also change the separator

'phrases-with-hyphens'.split('-')

there are also mehtods for chaning the case and other similar things. *Use these instead of implementing your own string operations!!

example_str.upper(), example_str.capitalize()

Combining parts of dataframes¶

bag_df.head()

we can pass pd.concat and iterable of pandas objects (here a list of DataFrames) and it will, by default stack them vertically, or with axis=1 stack the horizontally

pd.concat([coffee_df,bag_df],axis=1)

Casting Review¶

If we have a variable that is not the type we want like this:

a='5'

we can check type

type(a)

and we can use the name of the type we want, as a function to cast it to the new type.

int(5)

and check

type(int(a))

Unpacking jsons¶

We can read json data from an api using read_json as we did in the first cell today

course_gh_df.head(1)

However, json data is often nested

If we look at one of those rows, we can see it looks sort of like a dictionary, but we want it to be a pd.Series

course_gh_df['actor'].head(3)

We can change the type, of eachc cell using apply to cast each individual dictionary to a series.

course_gh_df['actor'].apply(pd.Series).head(3)

This particular dataset has several of these:

course_gh_df.head(1)

We can iterate over them all and then combine them back together:

json_cols = ['actor','repo','payload','org']

pd.concat([ course_gh_df[col].apply(pd.Series)
            for col in json_cols],axis=1).head(3)

in the cell above, workign outside in:

• pd.concat with axis=1 will stack dataframes together horizontally

• the [] make alist comprehension that runs for each value in json_cols

• course_gh_df[col].apply(pd.Series) is like the previous cell, where we expanded only the 'actor' column

We can then do thie with renaming the columns to have better names

course_gh_df_clean = pd.concat([ course_gh_df[col].apply(pd.Series).rename(lambda in_col: f'{col}_{in_col}',axis=1)
            for col in json_cols],axis=1)

Here we use that rename can take a function and we format the names to be like sourcecol_subcol where sourcecol is the name of the column in the original dataset and subcol is the name fo the field inside of the dictionary value.

To combine with the rest of the columsn we can filter out the othe rcolumn names

reg_cols = [col for col in course_gh_df.columns if not(col in json_cols)]
reg_cols

and then combine that with the rest into a single dataframe:

course_gh_df_clean = pd.concat([course_gh_df[reg_cols]]+
    [ course_gh_df[col].apply(pd.Series).rename(lambda in_col: f'{col}_{in_col}',axis=1)
            for col in json_cols],axis=1)

course_gh_df_clean.head(3)

Questions¶

Today’s questions were all about the assignment or json example.