{"id":968,"date":"2021-07-04T15:49:33","date_gmt":"2021-07-04T19:49:33","guid":{"rendered":"https:\/\/www.alpharithms.com\/?p=968"},"modified":"2022-06-16T15:24:07","modified_gmt":"2022-06-16T19:24:07","slug":"fix-a-missing-value-in-pandas-dataframe","status":"publish","type":"post","link":"https:\/\/www.alpharithms.com\/fix-a-missing-value-in-pandas-dataframe-334915\/","title":{"rendered":"How to Find and Fix Missing Values in Pandas DataFrames"},"content":{"rendered":"

Pandas is a highly utilized data science library for the Python programming language. One of the many reasons Pandas has become the de facto data processing library is the ease with which it allows developers to find and replace missing values in datasets.<\/p>\n

In this short article, you’ll learn how to inspect a dataset using Pandas and identify any potential missing data, how to replace those data, and the functions Pandas afford for the many options of doing so.<\/p>\n

TL;DR<\/strong> – Pandas provides several methods for replacing missing data. Note the use of the inplace<\/code> argument. That transforms the DataFrame object without creating another copy in memory. Here’s a basic example of each:<\/p>\n

import pandas as pd\r\n\r\n# Parse data with missing values\r\n# as Pandas DataFrame object\r\ndf = pd.DataFrame(dirty_data)\r\n\r\n# Replace with 0 values. Accepts regex.\r\ndf.replace(np.NAN, 0, inplace=True)\r\n\r\n# Replace with zero values\r\ndf.fillna(value=0, inplace=True)\r\n\r\n# Replace with interpolated value.\r\ndf.interpolate(inplace=True)<\/pre>\n
Introduction<\/h2>\n
Pandas is one of the defacto data science libraries for Python and for good reason. This library makes replacing missing values in one’s data a breeze. The DataFrame class is host to several methods designed specifically for this use case. In this article, we’ll cover three of the most common methods used to replace missing data in Pandas. We’ll take a stepwise approach covering the following stages:<\/p>\n
    \n
  1. Obtain data with missing values<\/li>\n
  2. Check data for missing values<\/li>\n
  3. Replace missing values<\/li>\n<\/ol>\n
    These are three basic concepts but I find it important to have an explicit step-by-step approach to dealing with what is often a\u00a0very\u00a0<\/em>messy situation. Fortunately, Pandas doesn’t require any complicated syntax to move mountains of data.<\/p>\n
    Step 1: Generate\/Obtain Data with Missing Values<\/h2>\n
    For this tutorial, we’ll be using randomly generated TimeSeries data with a date and random integer value. Several of the integer values have been purposefully corrupted resulting in NaN values after loading into pandas. These values are those that Pandas is going to fix for us in the next step. The data used in this article can be generated via the code below or downloaded from here<\/a> (sequence of missing values may vary.)<\/p>\n
    import pandas as pd\r\n\r\n# Generate series from start of 2018 to end of 2020\r\nseries = pd.date_range(start='2018-01-01', end='2020-12-31', frequency='d') \r\n\r\n# Convert to DataFrame object\r\nseries = pd.DataFrame(series, columns=['date']) \r\n\r\n# Set index \r\nseries.set_index('date', inplace=True)\r\n\r\n# Create series of random integers with some None values\r\nints = [random.choice(list(range(10)) + [None]) for x in range(len(series))]\r\n\r\n# Add values to DataFrame series object as new column\r\nseries['nums'] = nums\r\n\r\n# Inspect result\r\n>>> series\r\n            nums\r\ndate            \r\n2018-01-01   NaN\r\n2018-01-02   3.0\r\n2018-01-03   9.0\r\n2018-01-04   5.0\r\n2018-01-05   2.0\r\n...          ...\r\n2020-12-27   2.0\r\n2020-12-28   8.0\r\n2020-12-29   7.0\r\n2020-12-30   3.0\r\n2020-12-31   5.0\r\n\r\n[1096 rows x 1 columns]<\/pre>\n
    Here we see that the first value for our time series was given a randomly selected NaN value (representing a None object in Python). This value, along with identical NaN entries, will represent the missing data we’ll be using Pandas to replace. If you wish to save such data for convenience the DataFrame.to_csv() method is recommended<\/a>.<\/p>\n
    Note<\/strong>: The code above is available as a function definition<\/a> on Github here.<\/p>\n
    Step 2: Check for Missing Data<\/h2>\n
    Checking for missing data is an essential step in any analytical pipeline. Pandas offers several convenient methods to do this, each with varying specificity and utility. The following three methods are useful:<\/p>\n
      \n
    1. DataFrame.isnull()<\/code> – replaces all data with boolean values such that False indicates missing data. Best suited for granular assessment of specific data ranges;<\/li>\n
    2. DataFrame.isnull().values.any()<\/code> – returns a boolean indicating if the entire dataset includes any missing data. Great for quick assessment;<\/li>\n
    3. DataFrame.isnull().sum()<\/code> – returns a total count of missing values for each column and datatype. Great balance of holism and specificity.<\/li>\n<\/ol>\n
      Below is an example usage of each of these methods and their resulting descriptions of our time series data:<\/p>\n
      # Replace missing values with Booleans\r\n>>> series.isnull()\r\n\r\n             nums\r\ndate             \r\n2018-01-01  False\r\n2018-01-02  False\r\n2018-01-03   True\r\n2018-01-04  False\r\n2018-01-05  False\r\n...           ...\r\n2020-12-27   True\r\n2020-12-28  False\r\n2020-12-29  False\r\n2020-12-30  False\r\n2020-12-31  False\r\n\r\n[1096 rows x 1 columns]\r\n\r\n# Check entire set for any missing values\r\n>>> series.isnull().values.any()\r\n\r\nTrue\r\n\r\n# Check for total count of missing datum\r\n>>> series.isnull().sum()\r\n\r\nnums 88\r\ndtype: int64\r\n\r\n<\/pre>\n
      There are a handful of other methods available for the DataFrame.isnull()<\/code> method that are described in the official Pandas documentation<\/a>. For more information on the values().any()<\/code> method see the official NumPy documentation for the np.array<\/a> object.\u00a0 Now that we know our data contains missing values we can formulate an approach to begin replacing the data as we best see fit.<\/p>\n
      Quick Aside: isnull() vs. isna()<\/h3>\n
      Pandas offers two methods for the DataFrame object that\u00a0both<\/em> detect null<\/code> or NaN<\/code> values. The isnull()<\/code> and isna()<\/code> methods both do exactly the same thing<\/strong>. Don’t believe me? Check out the source code here<\/a>. These methods can be used interchangeably with DataFrame instances. One possible reasoning behind this design is the developers of Pandas offering a hat-tip to Ruby (a popular data science language<\/a>) treats NaN (NA) and null values differently\u2014just my best guess!<\/p>\n
      Step 3: Replace the Missing Data<\/h2>\n
      Pandas offers several utilities that are useful in the replacement of missing data. Here we’ll cover three common methods:<\/p>\n
        \n
      1. DataFrame.fillna()<\/code> – Fills NaN<\/code> values with a specified replacement value. Several optional keyword arguments of which inplace: bool<\/code>‘ adds the replacement value without copying the DataFrame<\/code> object.<\/li>\n
      2. DataFrame.replace()<\/code> – Replaces a specified value with an alternative. Not strictly limited to NaN<\/code> replacements.<\/li>\n
      3. DataFrame.interpolate()<\/code> – Fills NaN<\/code> values with interpolated values generated by a specified method. e.g. linear, time, index.<\/li>\n<\/ol>\n
        Let’s see how each of these methods of replacing missing data in Pandas works with our Time Series data. First up, let’s try out the fillna()<\/code> method:<\/p>\n
        Option 1: DataFrame.fillna()<\/h3>\n
        Still using our data before, we can\u00a0fill<\/em> the NaN values using the fillna() method in one of several ways. First, let’s use only the required argument value to replace the NaN values.<\/p>\n
        # Count NaN values\r\n>>> series.isnull().sum()\r\n\r\n# Result\r\nnums    89\r\ndtype: int64\r\n\r\n# Replace NaN with value of 1, recount\r\n>>> series.fillna(value=1)\r\n>>> series.isnull().sum()\r\n\r\n# Result\r\nnums    89\r\ndtype: int64\r\n<\/pre>\n
        It looks like nothing has changed in our data and our NaN<\/code> values persisted. This is because the fillna()<\/code> method returns a copy of our DataFrame<\/code> object. Without assigning that object to a variable we have no way to reference it again.<\/p>\n
        To avoid this pitfall, simply assign the return as such: series = series.fillna(value=1)<\/code>. Alternatively, you can use a different variable name rather than overwriting the initial series object. Another approach is to simply make use of the in_place<\/code> argument offered by fillna()<\/code> as such:<\/p>\n
        # Check our data for NaN again\r\n>>> series.isnull().sum()\r\n\r\n# Result\r\nnums    113\r\ndtype: int64\r\n\r\n# Use fillna() w\/in_place set to True\r\n>>> series.fillna(value=1, in_place=True)\r\n\r\n# Result\r\nnums    0\r\ndtype: int64\r\n<\/pre>\n
        This approach is more elegant in my opinion but does eliminate access to the original DataFrame object\u2014and the original data\u2014keep that in mind. Time Series data is linear data and often reflects a strong linear correlation.<\/p>\n
        Replacing NaN values with a static value may not be well-suited for later analysis. In such cases, replacing values with a calculated value may be better suited. The fillna() method offers several built-in options for this. Here we’ll be using the bfill option to replace NaN values with the last value in the Time Series:<\/p>\n
        # Print a representation of our data\r\n>>> series\r\n\r\n# Result\r\n            nums\r\ndate            \r\n2018-01-01   7.0\r\n2018-01-02   4.0\r\n2018-01-03   1.0\r\n2018-01-04   5.0\r\n2018-01-05   7.0\r\n...          ...\r\n2020-12-27   0.0\r\n2020-12-28   NaN\r\n2020-12-29   4.0\r\n2020-12-30   3.0\r\n2020-12-31   2.0\r\n\r\n[1096 rows x 1 columns]\r\n\r\n# Fill NaN values using 'ffill' method\r\n>>> series.fillna(method='ffill', inplace=True)\r\n\r\n# Result\r\n            nums\r\ndate            \r\n2018-01-01   7.0\r\n2018-01-02   4.0\r\n2018-01-03   1.0\r\n2018-01-04   5.0\r\n2018-01-05   7.0\r\n...          ...\r\n2020-12-27   0.0\r\n2020-12-28   0.0\r\n2020-12-29   4.0\r\n2020-12-30   3.0\r\n2020-12-31   2.0\r\n\r\n[1096 rows x 1 columns]<\/pre>\n
        Note that we have the same number of columns and all our values, other than the NaN were unaffected. The NaN was replaced by the 0.0 value before it (forward-filling). Check out the official Pandas documentation<\/a> for a full list of method<\/code> options as well as other keyword arguments that can be used.<\/p>\n
        The fillna()<\/code> method can also use dynamic values as such: series.fillna(series.mean(), inplace=True)<\/code>. This will replace all NaN values for each column in a DataFrame with the average value for that column. That can be incredibly useful but, again, might influence correlation values by an unintended magnitude.<\/p>\n
        Option 2: DataFrame.replace()<\/h3>\n
        The replace()<\/code> method is similar to the fillna()<\/code> method but offers a different set of options. Differences between fillna()<\/code> and replace()<\/code> can be characterized by noting the replace()<\/code> method accommodates the use of regular expressions for searching and replacing values. Check out the official Pandas Documentation<\/a> for a fuller explanation.<\/p>\n
        This can be useful in cases where data isn’t\u00a0missing\u00a0<\/em>but may be of improper format. For example, a list of emails of which some contain [at]<\/code> instead of the @<\/code> symbol. We’ll not be covering this method in depth given its similarity to the fillna()<\/code> method but a basic example can be seen below:<\/p>\n
        # View our initial series data\r\n>>> series\r\n\r\n# Result\r\n            nums\r\ndate            \r\n2018-01-01   NaN\r\n2018-01-02   3.0\r\n2018-01-03   2.0\r\n2018-01-04   NaN\r\n2018-01-05   2.0\r\n...          ...\r\n2020-12-27   1.0\r\n2020-12-28   NaN\r\n2020-12-29   4.0\r\n2020-12-30   1.0\r\n2020-12-31   3.0\r\n\r\n# Replace all NaN values with 1's\r\n>>> series.replace(to_replace=np.NAN, value=1, inplace=True)\r\n\r\n# Result\r\n[1096 rows x 1 columns]\r\n            nums\r\ndate            \r\n2018-01-01   1.0\r\n2018-01-02   3.0\r\n2018-01-03   2.0\r\n2018-01-04   1.0\r\n2018-01-05   2.0\r\n...          ...\r\n2020-12-27   1.0\r\n2020-12-28   1.0\r\n2020-12-29   4.0\r\n2020-12-30   1.0\r\n2020-12-31   3.0\r\n\r\n[1096 rows x 1 columns]<\/pre>\n
        Option 3: DataFrame.interpolate()<\/h3>\n
        The Pandas DataFrame object offers a powerful interpolation method to fix missing data with values more congruent to valid data. Quickly; interpolation is a method of estimation based on values within a [usually] nearish range of numbers. Check out this article by ArcGIS<\/a> for a nice visual description of interpolation. Let’s see how the DataFrame.interpolate() function can help fix our missing data:<\/p>\n
        # View our initial data\r\nseries.head(15)\r\n\r\n# Result\r\n           nums\r\ndate            \r\n...\r\n2018-01-07   2.0\r\n2018-01-08   1.0\r\n2018-01-09   0.0\r\n2018-01-10   NaN\r\n2018-01-11   NaN\r\n2018-01-12   1.0\r\n2018-01-13   NaN\r\n2018-01-14   1.0\r\n2018-01-15   0.0\r\n\r\n# Replace NaN values via interpolate\r\n>>> series.interpolate(inplace=True)\r\n\r\n# Result\r\n                nums\r\ndate                \r\n...\r\n2018-01-07  2.000000\r\n2018-01-08  1.000000\r\n2018-01-09  0.000000\r\n2018-01-10  0.333333  # interpolated\r\n2018-01-11  0.666667  # interpolated\r\n2018-01-12  1.000000\r\n2018-01-13  1.000000  # interpolated\r\n2018-01-14  1.000000\r\n2018-01-15  0.000000\r\n<\/pre>\n
        Here I’ve done things a little bit differently. I’ve used the DataFrame.head() method to show a more congruent series of values for illustration. You can see that Pandas uses the values before and after the NaN values to create a sort of average.<\/p>\n
        This is a linear estimate and has applications in many areas of statistics, machine learning, and mathematics. Pandas offers several other interpolation methods that can be used by passing an appropriate keyword. Check out the official documentation<\/a> for more info.<\/p>\n
        Final Thoughts<\/h2>\n
        We’ve seen the basic approaches to finding and replacing missing values in Pandas. These methods and approaches will handle the basic cases of data wrangling one might encounter. While more advanced methods and pipelines are needed for most data-processing workflows these methods and approaches can still be integrated easily. Replacing a NaN is\u2014usually\u2014still a matter of replacing an NaN.<\/p>\n","protected":false},"excerpt":{"rendered":"
        Pandas is a highly utilized data science library for the Python programming language. One of the many reasons Pandas has become the de facto data processing library is the ease with which it allows developers to find and replace missing values in datasets. In this short article, you’ll learn how to inspect a dataset using […]<\/p>\n","protected":false},"author":2,"featured_media":972,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[655,10],"tags":[196,391,211,389,390,392,210,143,381],"acf":[],"_links":{"self":[{"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/posts\/968"}],"collection":[{"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/comments?post=968"}],"version-history":[{"count":6,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/posts\/968\/revisions"}],"predecessor-version":[{"id":975,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/posts\/968\/revisions\/975"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/media\/972"}],"wp:attachment":[{"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/media?parent=968"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/categories?post=968"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/tags?post=968"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}