{"id":4866,"date":"2020-09-16T18:04:34","date_gmt":"2020-09-16T12:34:34","guid":{"rendered":"https:\/\/www.h2kinfosys.com\/blog\/?p=4866"},"modified":"2020-09-16T18:04:36","modified_gmt":"2020-09-16T12:34:36","slug":"data-cleansing-using-pandas","status":"publish","type":"post","link":"https:\/\/www.h2kinfosys.com\/blog\/data-cleansing-using-pandas\/","title":{"rendered":"Data Cleansing Using Pandas"},"content":{"rendered":"\n

Data scientists spend a large amount of their time cleaning datasets and getting them down to a form with which they can work. A lot of data scientists argue that the initial steps of obtaining and cleaning data constitute 80% of the job. Therefore, if you are just stepping into this field or planning to step into this field, it is important to be able to deal with messy data, whether that means missing values, inconsistent formatting, malformed records, or nonsensical outliers.<\/p>\n\n\n\n

In this article, we will cover a few pandas libraries<\/a> that are used to clean the data.<\/p>\n\n\n\n

Functions Used for Data Cleaning<\/strong><\/h2>\n\n\n\n

After reading the data set into a data frame using .read_csv( ) we will try to clean the Data using different functions<\/p>\n\n\n\n

Why Should we Rename Columns and Index<\/strong><\/h2>\n\n\n\n

If your data was generated by a computer program, it probably has some computer-generated column names, too. Those can be hard to read and understand while working, so if you want to rename a column to something more user-friendly, you can do it using df.rename()<\/strong><\/p>\n\n\n\n

Consider the following DataFrame<\/p>\n\n\n\n

   <\/strong><\/td>A  <\/strong><\/td>B<\/strong><\/td><\/tr>
0<\/strong><\/td>1<\/td>4<\/td><\/tr>
1<\/strong><\/td>2<\/td>5<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

df.rename(columns={\"A\": \"a\", \"B\": \"b\"})<\/code><\/p>\n\n\n\n

Output:<\/strong><\/p>\n\n\n\n

   <\/strong><\/td>a  <\/strong><\/td>b<\/strong><\/td><\/tr>
0<\/strong><\/td>1<\/td>4<\/td><\/tr>
1<\/strong><\/td>2<\/td>5<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

We can also rename the index using .rename()<\/p>\n\n\n\n

df.rename(index={0: \"x\", 1: \"y\"})<\/code><\/p>\n\n\n\n

   <\/strong><\/td>a  <\/strong><\/td>b<\/strong><\/td><\/tr>
x  <\/strong><\/td>1<\/td>4<\/td><\/tr>
y  <\/strong><\/td>2<\/td>5<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Missing data is always a problem in real-life scenarios. Areas like machine learning and data mining<\/a> face severe issues in the accuracy of their model predictions because of the poor quality of data caused by missing values. In these areas, missing value treatment is a major point of focus to make their models more accurate and valid.<\/p>\n\n\n\n

When and Why Is Data Missed?<\/strong><\/h2>\n\n\n\n

Let us consider an online survey for a product. Many times, people do not share all the information related to them. Few people share their experience, but not how long they are using the product; few people share how long they are using the product, their experience but not their contact information. Thus, in some or the other way, a part of data is always missing, and this is very common in real-time.<\/p>\n\n\n\n

In Pandas missing data is represented by two value: <\/p>\n\n\n\n

None<\/strong>: None is a Python singleton object that is often used for missing <\/strong>data in Python code. <\/p>\n\n\n\n

NaN<\/strong>: NaN (an acronym for Not a Number), is a special floating point value recognized by all systems that use the standard IEEE floating-point representation.<\/p>\n\n\n\n

Let us now see how to identify missing values in our Data Set<\/p>\n\n\n\n

Consider the following Data Set<\/p>\n\n\n\n

    <\/strong><\/td>one        <\/strong><\/td>two       <\/strong><\/td>three    <\/strong><\/td><\/tr>
a   <\/strong><\/td>-1.359063<\/td>1.613255<\/td>-0.669396<\/td><\/tr>
b   <\/strong><\/td>      NaN  <\/td>    NaN  <\/td>      NaN<\/td><\/tr>
c   <\/strong><\/td>0.885117<\/td>0.609271<\/td>0.330818<\/td><\/tr>
d   <\/strong><\/td>      NaN  <\/td>    NaN  <\/td>      NaN<\/td><\/tr>
e   <\/strong><\/td>-0.136086<\/td>1.132808<\/td>0.496091<\/td><\/tr>
f   <\/strong><\/td>0.210065<\/td>0.533174<\/td>0.111560<\/td><\/tr>
g   <\/strong><\/td>      NaN  <\/td>    NaN  <\/td>      NaN<\/td><\/tr>
h   <\/strong><\/td>1.027689<\/td>0.630037<\/td>0.727022<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Now we will find the missing values in the data set using the function    .isnull( )<\/p>\n\n\n\n

df.isnull()<\/code><\/p>\n\n\n\n

Output:<\/strong><\/p>\n\n\n\n

   <\/strong><\/td>  one  <\/strong><\/td>  two  <\/strong><\/td>three<\/strong><\/td><\/tr>
a  <\/strong><\/td>False  <\/td>False  <\/td>FALSE<\/td><\/tr>
b  <\/strong><\/td>True  <\/td>True  <\/td>True<\/td><\/tr>
c  <\/strong><\/td>False  <\/td>False  <\/td>FALSE<\/td><\/tr>
d  <\/strong><\/td>True  <\/td>True  <\/td>True<\/td><\/tr>
e  <\/strong><\/td>False  <\/td>False  <\/td>FALSE<\/td><\/tr>
f  <\/strong><\/td>False  <\/td>False  <\/td>FALSE<\/td><\/tr>
g  <\/strong><\/td>True  <\/td>True  <\/td>True<\/td><\/tr>
h  <\/strong><\/td>False  <\/td>False  <\/td>FALSE<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

.isnull() checks every column for NULL values and a boolean series is returned by the isnull() method which stores True for NaN value and False for a Not null value.<\/p>\n\n\n\n

We can also use .notnull() function to find the null values. It is opposite of .isnull()<\/p>\n\n\n\n

df.notnull()<\/code><\/p>\n\n\n\n

Output:<\/strong><\/p>\n\n\n\n

   <\/strong><\/td>  one  <\/strong><\/td>  two  <\/strong><\/td>three<\/strong><\/td><\/tr>
a  <\/strong><\/td>True  <\/td>True  <\/td>True<\/td><\/tr>
b  <\/strong><\/td>False  <\/td>False  <\/td>FALSE<\/td><\/tr>
c  <\/strong><\/td>True  <\/td>True  <\/td>True<\/td><\/tr>
d  <\/strong><\/td>False  <\/td>False  <\/td>FALSE<\/td><\/tr>
e  <\/strong><\/td>True  <\/td>True  <\/td>True<\/td><\/tr>
f  <\/strong><\/td>True  <\/td>True  <\/td>True<\/td><\/tr>
g  <\/strong><\/td>False  <\/td>False  <\/td>FALSE<\/td><\/tr>
h  <\/strong><\/td>True  <\/td>True  <\/td>True<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

.notnull() checks every column is checked for NULL values and a boolean series is returned by the notnull() method which stores True for every NON-NULL value and False for a null value.<\/p>\n\n\n\n

How to Drop rows with Nan values<\/strong><\/h2>\n\n\n\n

There are several options for handling missing values each with its PROS and CONS. However, the choice of what should be done is largely dependent on the nature of our data and the missing values. Below is a summary highlight of several options we have for handling missing values.<\/p>\n\n\n\n

  • Drop the missing values<\/li><\/ul>\n\n\n\n
    • Fill the missing values<\/li><\/ul>\n\n\n\n

      Drop the missing values<\/strong><\/h2>\n\n\n\n

      .dropna() function this function drop Rows Columns off datasets with Null values<\/p>\n\n\n\n

      Consider the following Data Set<\/p>\n\n\n\n

          <\/strong><\/td>one        <\/strong><\/td>two       <\/strong><\/td>three    <\/strong><\/td><\/tr>
      a   <\/strong><\/td>-1.359063<\/td>1.613255<\/td>-0.669396<\/td><\/tr>
      b   <\/strong><\/td>      NaN  <\/td>    NaN  <\/td>      NaN<\/td><\/tr>
      c   <\/strong><\/td>0.885117<\/td>0.609271<\/td>0.330818<\/td><\/tr>
      d   <\/strong><\/td>      NaN  <\/td>    NaN  <\/td>      NaN<\/td><\/tr>
      e   <\/strong><\/td>-0.136086<\/td>1.132808<\/td>0.496091<\/td><\/tr>
      f   <\/strong><\/td>0.210065<\/td>0.533174<\/td>0.111560<\/td><\/tr>
      g   <\/strong><\/td>      NaN  <\/td>    NaN  <\/td>      NaN<\/td><\/tr>
      h   <\/strong><\/td>1.027689<\/td>0.630037<\/td>0.727022<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

      df.dropna()<\/code><\/p>\n\n\n\n

      Output:<\/strong><\/p>\n\n\n\n

      <\/td><\/td><\/td><\/td><\/tr>
          <\/strong><\/td>one        <\/td>two       <\/td>three    <\/td><\/tr>
      a   <\/strong><\/td>-1.359063<\/td>1.613255<\/td>-0.669396<\/td><\/tr>
      c   <\/strong><\/td>0.885117<\/td>0.609271<\/td>0.330818<\/td><\/tr>
      e   <\/strong><\/td>-0.136086<\/td>1.132808<\/td>0.496091<\/td><\/tr>
      f   <\/strong><\/td>0.210065<\/td>0.533174<\/td>0.111560<\/td><\/tr>
      h   <\/strong><\/td>1.027689<\/td>0.630037<\/td>0.727022<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

      Fill the missing values<\/strong><\/h2>\n\n\n\n

      Pandas df.replace() function is used to replace a string, regex, list, dictionary, series, number etc. from a dataframe. This is a very rich function as it has many variations.<\/p>\n\n\n\n

      Using this .replace() function we can replace we replace all the NaN with whatever value\u2019s we like<\/p>\n\n\n\n

      Now we will replace all the NaN<\/strong> with \u20180\u2019<\/strong><\/p>\n\n\n\n

      df.replace(np.nan,0) \/\/(orginal value,replaced value)<\/p>\n\n\n\n

      Output:<\/strong><\/p>\n\n\n\n

          <\/strong><\/td>one        <\/strong><\/td>two       <\/strong><\/td>three    <\/strong><\/td><\/tr>
      a   <\/strong><\/td>-1.359063<\/td>1.613255<\/td>-0.669396<\/td><\/tr>
      b   <\/strong><\/td>0<\/td>0<\/td>0<\/td><\/tr>
      c   <\/strong><\/td>0.885117<\/td>0.609271<\/td>0.330818<\/td><\/tr>
      d   <\/strong><\/td>0<\/td>0<\/td>0<\/td><\/tr>
      e   <\/strong><\/td>-0.136086<\/td>1.132808<\/td>0.496091<\/td><\/tr>
      f   <\/strong><\/td>0.210065<\/td>0.533174<\/td>0.111560<\/td><\/tr>
      g   <\/strong><\/td>0<\/td>0<\/td>0<\/td><\/tr>
      h   <\/strong><\/td>1.027689<\/td>0.630037<\/td>0.727022<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

      You can also replace the NaN with mean, median, mode and we can also impute values using machine learning models .<\/p>\n\n\n\n

      We will learn about statistical summary calculations in next article\u2019s<\/p>\n\n\n\n

      Now we will learn how to convert the datatypes of the variables <\/p>\n\n\n\n

      When doing data analysis, it is important to make sure you are using the correct data types; otherwise, you may get unexpected results or errors. In the case of pandas, it will correctly infer data types in many cases and you can move on with your analysis without any further thought on the topic.<\/p>\n\n\n\n

      Despite how well pandas works, at some point in your data analysis processes, you will likely need to explicitly convert data from one type to another. <\/p>\n\n\n\n

      Now we will discuss the basic pandas data types, how they map to python and numpy data types, and the options for converting from one pandas type to another.<\/p>\n\n\n\n

      Pandas Data Types<\/strong><\/h2>\n\n\n\n

      A data type is essentially an internal construct that a programming language uses to understand how to store and manipulate data. For instance, a program needs to understand that you can add two numbers together like 5 + 10 to get 15. Or, if you have two strings such as \u201ccat\u201d and \u201cdog\u201d you could concatenate (add) them together to get \u201ccatdog.\u201d<\/p>\n\n\n\n

      A possible confusing point about pandas data types is that there is some overlap between pandas, and numpy. This table summarizes the key points:<\/p>\n\n\n\n

      Pandas dtype<\/td>NumPy type<\/td>Usage<\/td><\/tr>
      object<\/td>string_, unicode_, mixed types<\/td>Text or mixed numeric<\/td><\/tr>
      int64<\/td>int_, int8, int16, int32, int64, uint8, uint16, uint32, uint64<\/td>Integer numbers<\/td><\/tr>
      float64<\/td>float_, float16, float32, float64<\/td>Floating point numbers<\/td><\/tr>
      bool<\/td>bool_<\/td>True\/False values<\/td><\/tr>
      datetime64<\/td>datetime64[ns]<\/td>Date and time values<\/td><\/tr>
      category<\/td>NA<\/td>Finite list of text values<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

      Now we will focus on the following pandas data types and learn how to convert them from one form to another form <\/p>\n\n\n\n

      • object<\/li><\/ul>\n\n\n\n
        • int64<\/li><\/ul>\n\n\n\n
          • float64<\/li><\/ul>\n\n\n\n

            Consider a DataFrame<\/p>\n\n\n\n

                     <\/strong><\/td>Age  <\/strong><\/td>Height  <\/strong><\/td>Weight<\/strong><\/td><\/tr>
            Krishna  <\/strong><\/td>25.5<\/td>5.40<\/td>45.5<\/td><\/tr>
            Ram      <\/strong><\/td>45.0<\/td>5.11<\/td>50.0<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

            As we all know that age is an integer value but if we closely observe the data frame age values is in float form<\/p>\n\n\n\n

            We can check the datatypes of every column by using .dtypes<\/p>\n\n\n\n

            df.dtypes<\/code><\/p>\n\n\n\n

            Age       float64\nHeight    float64\nWeight    float64\ndtype: object<\/pre>\n\n\n\n
            It is showing that Age is a float in-order to convert it into int we will use .astype()<\/p>\n\n\n\n
            df[\u2018Age\u2019].astype(\u2018int32\u2019)<\/code><\/p>\n\n\n\n
            Ram        25\nKrishna    45\nName: Age, dtype: int32<\/pre>\n\n\n\n
            Now we can see that Age column is now converted into int datatype<\/p>\n\n\n\n
            We will see the rest of the pandas applications in the next article\u2026\u2026<\/p>\n","protected":false},"excerpt":{"rendered":"
            Data scientists spend a large amount of their time cleaning datasets and getting them down to a form with which they can work. A lot of data scientists argue that the initial steps of obtaining and cleaning data constitute 80% of the job. Therefore, if you are just stepping into this field or planning to […]<\/p>\n","protected":false},"author":1,"featured_media":4892,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[500],"tags":[1369,1367,1368],"class_list":["post-4866","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-science-using-python-tutorials","tag-data-cleaning-functions","tag-data-cleansing-using-pandas","tag-pandas-data-types"],"_links":{"self":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts\/4866","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/comments?post=4866"}],"version-history":[{"count":0,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts\/4866\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/media\/4892"}],"wp:attachment":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/media?parent=4866"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/categories?post=4866"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/tags?post=4866"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}