Strategies for Handling Missing Data:

• Missing data is a common circumstance in data analytics, although the underlying reasons for why data is missing can vary from problem to problem.
• Both understanding to what extent data is missing, as well as considering different strategies for dealing with missing data, are key aspects of data preparation and analysis.
• The three broad strategies for handling missing data include:
  • Ignore missing data
  • Drop missing data
  • Fill-in missing data

How Pandas Handles Missing Data:

• It’s worth noting that Pandas can deal quite well with missing data in the sense that if you have a CSV file or an Excel spreadsheet that has empty cells, empty entries, Pandas can quite easily read in that data set.
• For example, let’s imagine we have a CSV file containing office supplies that we can read into Pandas using the read_csv() method to create a DataFrame.

1import pandas as pd
2
3df = pd.read_csv('Data/salesdata_missing.csv')
4print(df)

Output:

1   Month   Pens  Pencils  Erasers  Paper
20    Jan  400.0    550.0     80.0  480.0
31    Feb  370.0    420.0     55.0    NaN
42    Mar  255.0    302.0     25.0  280.0
53    Apr  150.0    225.0     20.0  200.0
64    May  200.0    275.0      NaN  225.0
75    Jun  125.0    170.0     25.0  184.0
86    Jul   50.0     80.0     10.0  100.0
97    Aug  425.0      NaN     90.0  505.0
108    Sep  423.0    580.0     95.0  525.0
119    Oct  200.0      NaN     60.0  400.0
1210   Nov    NaN    106.0     12.0    NaN
1311   Dec   78.0     69.0     15.0   99.0

• What we see is that these missing data are essentially replaced by what appears textually as NaN, which stands for not a number.

  • This is actually representing underneath a number within NumPy, NumPy dot NaN which is used to indicate items that are not numerical.

• More information about the DataFrame can be obtained using the info() method.

5print(df.info())

Output:

1<class 'pandas.core.frame.DataFrame'>
2RangeIndex: 12 entries, 0 to 11
3Data columns (total 5 columns):
4  #   Column   Non-Null Count  Dtype
5 ---  ------   --------------  -----
6  0   Month    12 non-null     object
7  1   Pens     11 non-null     float64
8  2   Pencils  10 non-null     float64
9  3   Erasers  11 non-null     float64
10  4   Paper    10 non-null     float64
11dtypes: float64(4), object(1)
12memory usage: 608.0+ bytes

• In the pens column, we have 11 non-null floats, and same thing with the erasers column.
• In the pencils and paper columns, each have 10 non-null floats.

Ignore missing data:

• One strategy for handling missing data is to ignore it.
• In the above example, our DataFrame is encoded with NaN special values, but Pandas can still provide a lot of summary statistics on the data that do remain.
  • For example, we can sum up the total number of pens, pencils, erasers, and paper sold over the year.

6print(df.sum(numeric_only=True))

Output:

1Pens       2676.0
2Pencils    2777.0
3Erasers     487.0
4Paper      2998.0
5dtype: float64

• As noted above, for pens we have one missing entry in November, and so it’s just the sum of all the other remaining data.

Drop missing data:

• Another strategy is to try and drop those elements that are missing to produce a smaller data set that is then complete.
• For example, let’s imagine we have a DataFrame with ten rows and a number of columns, which are either ones or zeroes, but then there are missing entries, encoded as NaN.
  • We can drop all rows that have any missing data by using the dropna() method.
    • The dropna() method can also be used to drop each column individually (e.g., df[‘A’].dropna()).

Fill-in missing data:

• A third broad strategy is to fill in missing data with a value.
• The fillna() method can be used to fill in missing data with a specific value.
  • E.g., df.fillna(0.5) will fill in all missing data with 0.5.
• Another option is to fill in missing data with the mean of the column.
  • E.g., df.fillna(df.mean()) will fill in all missing data with the mean of the column.