in this article, we’ll explore common techniques we can use to clean CSV data using the python pandas library<\/a>.<\/p>\n\n\n\n We’ll clean data based on the following:<\/p>\n\n\n\n In Pandas, a missing value is usually denoted by You can find the dataset used in this article here<\/a>.<\/p>\n\n\n\n Let’s first see how we can find if there’s a missing value in our data.<\/p>\n\n\n\n #Approach 1: visually<\/strong><\/p>\n\n\n The However, this process could be limited to only medium to small datasets.<\/p>\n\n\n\n #Approach 2<\/strong><\/p>\n\n\n\n We can use We found that our dataset does have some missing values in it, what should we do next to get clean data?<\/p>\n\n\n\n We can either drop the rows and columns containing missing values in them or replace the missing values with appropriate value i.e. mean, median, or mode.<\/p>\n\n\n\n The above code will drop the rows from the dataframe having missing values.<\/p>\n\n\n\n Let’s look at One must be careful when considering dropping the missing values as it might affect the quality of the dataset.<\/p>\n\n\n\n 2. Replacing Missing values<\/strong><\/p>\n\n\n Pandas module has the .fillna() method, which accepts a value that we want to replace in place of NaN values. We just calculated the mean of the column and passed it as an input argument to Outliers can change the course of entire predictions therefore it is essential we detect and remove outliers<\/a>.<\/p>\n\n\n\n Let’s detect outliers in the Votes column in our dataset and filter the outliers using a z-score. <\/p>\n\n\n\n The idea behind this method lies in the fact that values lying 3 standard deviations<\/a> away from the mean will be termed an Outlier.<\/p>\n\n\n The column on which this method is applied should be a numerical variable and not categorical. <\/p>\n\n\n\n By this method values falling below 0.01 quantile and above 0.99 quantiles in the series will be filtered out.<\/p>\n\n\n We can check for any duplicates in a DataFrame using To check duplicate values in a specific column we can provide the column name as an input argument into the Let’s see this in action.<\/p>\n\n\n\n Luckily we have no duplicate values in our data frame, so we will append some values from the data frame itself to create duplicate values.<\/p>\n\n\n Now, CSV data cleaning in Python is easy with pandas and the NumPy module. Always perform data cleaning before running some analysis over it to make sure the analysis is correct.<\/p>\n\n\n\n Effective implementation of Machine learning algorithms or even when doing some data analysis on a dataset, we require clean data. There’s a well-known saying about numerical modeling with data, “Trash in Trash out” we can’t expect decent results when our data isn’t clean. in this article, we’ll explore common techniques we can use to clean […]<\/p>\n","protected":false},"author":16,"featured_media":10991,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[],"class_list":["post-10516","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-examples"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/www.askpython.com\/wp-json\/wp\/v2\/posts\/10516","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.askpython.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.askpython.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.askpython.com\/wp-json\/wp\/v2\/users\/16"}],"replies":[{"embeddable":true,"href":"https:\/\/www.askpython.com\/wp-json\/wp\/v2\/comments?post=10516"}],"version-history":[{"count":0,"href":"https:\/\/www.askpython.com\/wp-json\/wp\/v2\/posts\/10516\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.askpython.com\/wp-json\/wp\/v2\/media\/10991"}],"wp:attachment":[{"href":"https:\/\/www.askpython.com\/wp-json\/wp\/v2\/media?parent=10516"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.askpython.com\/wp-json\/wp\/v2\/categories?post=10516"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.askpython.com\/wp-json\/wp\/v2\/tags?post=10516"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}CSV Data Cleaning Checks<\/h2>\n\n\n\n
1. Cleaning Missing Values in CSV File<\/h2>\n\n\n\n
NaN<\/code> , since it is based on the NumPy package<\/a> it is the special floating-point NaN value particular to NumPy. <\/p>\n\n\n\nFinding Missing Values<\/h3>\n\n\n\n
\nimport pandas as pd\nimport seaborn as sb\nimport matplotlib.pyplot as plt\n\n#importing Dataset\ndf = pd.read_csv('IMDB-Movie-Data.csv')\n\n#Creating the heatmap\nplt.figure(figsize = (8,6))\nsb.heatmap(df.isnull(), cbar=False , cmap = 'magma')\n<\/pre><\/div>\n\n\n![\"Missing](\"https:\/\/www.askpython.com\/wp-content\/uploads\/2020\/11\/Missing-values-using-Heatmap.jpeg\")
isnull()<\/code> method returns boolean values indicating if there’s a missing value in the data. <\/p>\n\n\n\n.sum()<\/code> method after applying .isnull()<\/code>, this will return the sum of missing values within each column in the data frame.<\/p>\n\n\n\nimport pandas as pd\n\n#importing dataset\ndf = pd.read_csv('IMDB-Movie-Data.csv')\n\ndf.isnull().sum()\n<\/pre><\/div>\n\n\n![\"Finding](\"https:\/\/www.askpython.com\/wp-content\/uploads\/2020\/11\/finding-sum-of-missing-values.jpg\")
Cleaning Missing Values from Data<\/h3>\n\n\n\n
\n#imporing pandas\nimport pandas as pd\n\n#Importing dataset\ndf = pd.read_csv('IMDB-Movie-Data.csv')\n\n#Size of original dataset\nprint(df.shape)\n\n#Dropping the missing rows.\ndf_dropped = df.dropna(how = 'any')\n\n<\/pre><\/div>\n\n\n.dropna()<\/code> method in detail:<\/p>\n\n\n\n\nimport pandas as pd\n\n#importing the dataset\ndf = pd.read_csv('IMDB-Movie-Data.csv')\n\n#Creating a copy of dataframe\ndf_new = df\n\ndf_new['Metascore'] = df_new['Metascore'].fillna((df_new['Metascore'].mean()))\n\n#printing the dataframes after replacing null values\nprint(df_new.isna().sum())\nprint(df.isna().sum())\n<\/pre><\/div>\n\n\n![\"Before](\"https:\/\/www.askpython.com\/wp-content\/uploads\/2020\/11\/before-and-after-filling-null-values.jpg\")
fillna()<\/code> method.<\/p>\n\n\n\n2. Dealing with Outliers<\/h2>\n\n\n\n
Using Z-Score<\/strong><\/h3>\n\n\n\n
\n#importing required modules\nimport pandas as pd\nimport numpy as np\nfrom scipy import stats\n\n#importing dataset\ndf = pd.read_csv('IMDB-Movie-Data.csv')\n\n#filtering outliers\ndf_new = df[(np.abs(stats.zscore(df.Votes)) < 3)]\n<\/pre><\/div>\n\n\nUsing Quantiles<\/strong><\/h3>\n\n\n\n
\n#importing required modules\nimport pandas as pd\nimport numpy as np\nfrom scipy import stats\n\n#importing dataset\ndf = pd.read_csv('IMDB-Movie-Data.csv')\n\n#Selecting limits\nq_low = df["Votes"].quantile(0.01)\nq_hi = df["Votes"].quantile(0.99)\n\n#filtering outliers\ndf_filtered = df[(df["Votes"] < q_hi) & (df["Votes"] > q_low)]\n\n\n<\/pre><\/div>\n\n\n3. Dealing with Duplicate entries<\/h2>\n\n\n\n
.duplicated()<\/code><\/strong> method. This returns a Pandas Series and not a DataFrame. <\/p>\n\n\n\n.duplicated( )<\/code> method.<\/p>\n\n\n\n\n#Import the required modules\nimport pandas as pd\nimport numpy as np\n\n#importing and appending similar rows to of the dataframe\ndf = pd.read_csv('IMDB-Movie-Data.csv')\ndf1 = df.append(df.iloc[20:30,:])\n\ndf1.duplicated().sum()\n<\/pre><\/div>\n\n\nOutput<\/strong>:\n10<\/pre>\n\n\n\n
.drop_duplicates()<\/code> method is used to drop the duplicate values from the dataframe.<\/p>\n\n\n\n#Importing the required modules\n#Import the required modules\nimport pandas as pd\nimport numpy as np\n\n#importing and appending similar rows to of the dataframe\ndf = pd.read_csv('IMDB-Movie-Data.csv')\ndf1 = df.append(df.iloc[20:30,:])\n\n#dropping the duplicates\ndf1 = df1.drop_duplicates()\n\n#checking the duplicates \ndf1.duplicated().sum()\n<\/pre><\/div>\n\n\noutput<\/strong>:\n0<\/pre>\n\n\n\n
Summary<\/h2>\n\n\n\n
What’s Next?<\/h2>\n\n\n\n
Resources<\/h2>\n\n\n\n