> For the complete documentation index, see [llms.txt](https://codingnotes.gitbook.io/coding_notes/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://codingnotes.gitbook.io/coding_notes/coding/pandas/handling-missing-data-in-pandas.md). # Handling Missing Data In Pandas ## 1. INTRODUCTION **Detection of Missing Data** Two schemes to indicate the presence of missing data in a table or DataFrame: 1. Masking Approach: The mask that can be a ***separate Boolean array*** 2. Sentinel Approach: The sentinel value could be some; * **data-specific convention**, such as indicating a missing integer value with –9999 or some rare bit pattern, or * **global convention**, such as indicating a missing floating-point value with NaN (Not a Number), a special value which is part of the IEEE floating-point specification. **Handling Missing Data in Python** Pandas chose to use sentinels for missing data , and further chose to use two already-existing Python null values: the special floating-point `NaN` value, and the Python `None` object. 1. None: Pythonic Missing Data: Because `None` is a Python object, it cannot be used in any arbitrary NumPy array, but only in arrays with data type ‘object’ (i.e., arrays of Python objects) 2. NaN: Missing Numerical Data: `NaN` (acronym for Not a Number), is different; it is a **special floating-point value** recognized by all systems that use the standard IEEE floating-point representation ``` import numpy as np import pandas as pd ``` ``` # None, in an array, makes the whole array an object None_object = np.array([1,None,3,4]) None_object ``` ``` array([1, None, 3, 4], dtype=object) ``` ``` # Nan, in an array, returns a standard flaoting point type array NaN_type = np.array([1,np.nan,2,3]) NaN_type ``` ``` array([ 1., nan, 2., 3.]) ``` ``` # checking data type NaN_type.dtype ``` ``` dtype('float64') ``` **Operating on Null Values** Pandas treats `None` and `NaN` as essentially interchangeable for indicating missing or null values. To facilitate this convention, there are several useful methods for detecting, removing, and replacing null values in Pandas data structures. ## 2. DETECTING NULL VALUES Pandas data structures have two useful methods for detecting null data: `isnull()` and `notnull()` Either one will return a *Boolean mask* over the data. ### a. isnull ``` data_pd = pd.Series([1,np.nan,2,3,None]) data_pd.isnull() ``` ``` 0 False 1 True 2 False 3 False 4 True dtype: bool ``` ### b. notnull ``` # is not null? True means yes, not null data_pd.notnull() ``` ``` 0 True 1 False 2 True 3 True 4 False dtype: bool ``` ``` # masking to get only the data which is not null data_pd[data_pd.notnull()] ``` ``` 0 1.0 2 2.0 3 3.0 dtype: float64 ``` ## 3. DROPPING NULL VALUES We use `dropna()` method on Series or DataFrame, which removes `NaN` values ### a. On Series ``` data_pd.dropna() ``` ``` 0 1.0 2 2.0 3 3.0 dtype: float64 ``` ### b. On DataFrame ``` # Creating df with some missing values data_df = pd.DataFrame([[1, np.nan, 2], [2, 200, 300], ``` \[np.nan, 0,1] ]) print(data\_df) ``` 0 1 2 0 1.0 NaN 2 1 2.0 200.0 300 2 NaN 0.0 1 ``` ``` print(data_df.dropna()) ``` ``` 0 1 2 1 2.0 200.0 300 ``` * Using `dropna()` method, we cannot drop single values from a DataFrame; we can only drop complete row(s) or complete column(s), where one of the cell contains `NaN` * Depending on the application, you might want one or the other, so `dropna()` gives a number of options to handle this ➞ Using `axis=column` keyword argument to apply the `dropna()` to columns of a DataFrame ``` print(data_df.dropna(axis='columns')) ``` ``` 2 0 2 1 300 2 1 ``` ➞ We can drop column(s)/row(s) whose all cell values are `NaN` through kwarg `how='all'` ``` # adding column to data_df with all values as nan data_df[3] =np.nan print(data_df) ``` ``` 0 1 2 3 0 1.0 NaN 2 NaN 1 2.0 200.0 300 NaN 2 NaN 0.0 1 NaN ``` ``` # dropping the column(s) whose all values are NaN print(data_df.dropna(axis='columns', how='all')) ``` ``` 0 1 2 0 1.0 NaN 2 1 2.0 200.0 300 2 NaN 0.0 1 ``` ``` # by default, it applies on axis=index # as none of the rows in our dataframe contains all NaN, so no row is dropped print(data_df.dropna(how='all')) ``` ``` 0 1 2 3 0 1.0 NaN 2 NaN 1 2.0 200.0 300 NaN 2 NaN 0.0 1 NaN ``` ➞ Using keyword argument `thresh=integer` we can specify min number of non-null values, that must exist in row/column ``` print(data_df.dropna(thresh=3)) ``` ``` 0 1 2 3 1 2.0 200.0 300 NaN ``` ``` print(data_df.dropna(axis='columns', thresh=3)) ``` ``` 2 0 2 1 300 2 1 ``` ## 4. FILLING THE NULL VALUES * We use `fillna()` method on a Series or DataFrame, which fills `NaN` values with a given value. This value might be a single number like zero or some other good-values ### a. On Series ``` # reproducing series that contains NaN data_pd ``` ``` 0 1.0 1 NaN 2 2.0 3 3.0 4 NaN dtype: float64 ``` ``` # filling all NaN with integer, 101 data_pd.fillna(101) ``` ``` 0 1.0 1 101.0 2 2.0 3 3.0 4 101.0 dtype: float64 ``` ### b. On DataFrame For aDataFrame, we use same method but can also mention the `axis` keyword argument ``` # reproducing a DataFrame print(data_df) ``` ``` 0 1 2 3 0 1.0 NaN 2 NaN 1 2.0 200.0 300 NaN 2 NaN 0.0 1 NaN ``` ``` # fill all instances of NaN with integer, 101 print(data_df.fillna(101)) ``` ``` 0 1 2 3 0 1.0 101.0 2 101.0 1 2.0 200.0 300 101.0 2 101.0 0.0 1 101.0 ``` ### c. Types of Fill We can use the keyword argument `method=ffill` or `method=bfill` to fill the values #### Forward Fill We can use forward fill (`method=ffill`) — to propagate previous value forward ➞ On Series ``` data_pd.fillna(method='ffill') ``` ``` 0 1.0 1 1.0 2 2.0 3 3.0 4 3.0 dtype: float64 ``` ➞ On DataFrame ``` print(data_df.fillna(method='ffill')) ``` ``` 0 1 2 3 0 1.0 NaN 2 NaN 1 2.0 200.0 300 NaN 2 2.0 0.0 1 NaN ``` ``` # with `axis=1` print(data_df.fillna(method='ffill', axis=1)) ``` ``` 0 1 2 3 0 1.0 1.0 2.0 2.0 1 2.0 200.0 300.0 300.0 2 NaN 0.0 1.0 1.0 ``` #### Backward Fill We can use backward fill(`method=bfill`) — to propagate the next value backward ➞ On Series ``` data_pd.fillna(method='bfill') ``` ``` 0 1.0 1 2.0 2 2.0 3 3.0 4 NaN dtype: float64 ``` ➞ On DataFrame ``` df_new = pd.DataFrame({'a':[1,np.nan,2], 'b':[3,np.nan,4], 'c':[5,np.nan,6]}) print(f"DataFrame:\n{df_new}") print(f"DataFrame with bfill along axis=0:\n{df_new.fillna(method='bfill')}") print(f"DataFrame with bfill along axis=1:\n{df_new.fillna(method='bfill', axis=1)}") ``` ``` a b c 0 1.0 3.0 5.0 1 NaN NaN NaN 2 2.0 4.0 6.0 DataFrame with bfill along axis=0: a b c 0 1.0 3.0 5.0 1 2.0 4.0 6.0 2 2.0 4.0 6.0 DataFrame with bfill along axis=1: a b c 0 1.0 3.0 5.0 1 NaN NaN NaN 2 2.0 4.0 6.0 ```