<jupyter_start><jupyter_text>2 - Missing ValuesIn this second step I'll show you how to detect and clean missing values in the purchase column.You will see: comment of action, python code and results.<jupyter_code>import pandas as pd

df = pd.read_json("customer_data.json", convert_dates = False)
df.head()<jupyter_output><empty_output><jupyter_text>Taking a look at unique values, and missing values.The .isna() method will only find standard missing value types, so we'll need to use the .unique() method for identifying non-standard missing values.<jupyter_code>print(df["purchase"].unique())
print(sum(df["purchase"].isna()))<jupyter_output>['soap' 'shorts' 'lawn_mower' 'laptop' 'car wash' 'lawn mower' 'grill'
 'household cleaner' 'slow cooker' 'camera' 'snow shovel' 'shoes'
 'blender' 'shirts' 'toaster' 'detergent' 'tv' 'paper products' 'tools'
 'pants' 'audio' 'microwave' 'food processor' 'jackets' 'cell phone'
 'flower pot' None '?' '__' 'na' '--' 'cell' 'cell_phone' 'lawnmower'
 '1111']
36<jupyter_text>Lets change the standard missing values to a new category called unavailable.We can double check that it worked by summing for missing values using the .isna() method after we've changed the missing values using the .fillna() method. If it worked, the sum should be 0.We can also take a look at the unique values again to see if this new unavailable shows up.<jupyter_code># changing standard missing values to "unavailable"
df["purchase"].fillna("unavailable", inplace=True)

# double checking that it worked by summing for missing values, and looking at unique categories
print(sum(df["purchase"].isna()))
print(df["purchase"].unique())<jupyter_output>0
['soap' 'shorts' 'lawn_mower' 'laptop' 'car wash' 'lawn mower' 'grill'
 'household cleaner' 'slow cooker' 'camera' 'snow shovel' 'shoes'
 'blender' 'shirts' 'toaster' 'detergent' 'tv' 'paper products' 'tools'
 'pants' 'audio' 'microwave' 'food processor' 'jackets' 'cell phone'
 'flower pot' 'unavailable' '?' '__' 'na' '--' 'cell' 'cell_phone'
 'lawnmower' '1111']<jupyter_text>We can see several non-standard missing value types.Lets identify those by using a list of missing_values which contains the following ["?", "__", "na", "--"].We'll replace them by looping through the purchase column, and replacing the non-standard missing values using the .loc() method.<jupyter_code># list of non-standard missing values
missing_values = ["?", "__", "na", "--"]

# replacing the missing values with the new category, "unavailable"
cnt = 0
for i in df["purchase"]:
    if i in missing_values:
        df.loc[cnt, "purchase"] = "unavailable"
    cnt+=1

print(df["purchase"].unique())<jupyter_output>['soap' 'shorts' 'lawn_mower' 'laptop' 'car wash' 'lawn mower' 'grill'
 'household cleaner' 'slow cooker' 'camera' 'snow shovel' 'shoes'
 'blender' 'shirts' 'toaster' 'detergent' 'tv' 'paper products' 'tools'
 'pants' 'audio' 'microwave' 'food processor' 'jackets' 'cell phone'
 'flower pot' 'unavailable' 'cell' 'cell_phone' 'lawnmower' '1111']<jupyter_text>We could also convert missing values when we read in the data.The .read_json() method does not have the na_values parameter like .read_csv() does. We'll need to read in the json file, convert it to a csv file, and then read it back in using .read_csv().<jupyter_code># reading in the json file
df = pd.read_json("customer_data.json", convert_dates = False)

# writing the json file to a csv file
df.to_csv("customer_data2.csv")

# reading the csv file back in, replacing missing values
df2 = pd.read_csv("customer_data2.csv", na_values=missing_values)

print(df2["purchase"].unique())<jupyter_output>['soap' 'shorts' 'lawn_mower' 'laptop' 'car wash' 'lawn mower' 'grill'
 'household cleaner' 'slow cooker' 'camera' 'snow shovel' 'shoes'
 'blender' 'shirts' 'toaster' 'detergent' 'tv' 'paper products' 'tools'
 'pants' 'audio' 'microwave' 'food processor' 'jackets' 'cell phone'
 'flower pot' nan 'cell' 'cell_phone' 'lawnmower' '1111']<jupyter_text>The non-standard missing values have all been changed to missing values.Lets finish up by replacing the missing values with the new category, unavailable.<jupyter_code># changing missing values to "unavailable"
df2["purchase"].fillna("unavailable", inplace=True)

# double checking that it worked by summing for missing values, and looking at unique categories
print(sum(df2["purchase"].isna()))
print(df2["purchase"].unique())<jupyter_output>0
['soap' 'shorts' 'lawn_mower' 'laptop' 'car wash' 'lawn mower' 'grill'
 'household cleaner' 'slow cooker' 'camera' 'snow shovel' 'shoes'
 'blender' 'shirts' 'toaster' 'detergent' 'tv' 'paper products' 'tools'
 'pants' 'audio' 'microwave' 'food processor' 'jackets' 'cell phone'
 'flower pot' 'unavailable' 'cell' 'cell_phone' 'lawnmower' '1111']
{'fraction_non_alphanumeric': 0.13503879219962256, 'fraction_numerical': 0.007339064793457748, 'mean_word_length': 6.066666666666666, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 20, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '267078', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 1618, 'n_tokens_neox': 1537, 'n_words': 546, 'path': '2 - Missing Values.ipynb', 'repo_name': 'NickScherer/example-NickScherer.github.io', 'size': '4769'}