{"id":5635,"date":"2017-05-30T05:57:44","date_gmt":"2017-05-30T09:57:44","guid":{"rendered":"https:\/\/qxf2.com\/blog\/?p=5635"},"modified":"2018-04-03T10:07:55","modified_gmt":"2018-04-03T14:07:55","slug":"cleaning-data-python-pandas","status":"publish","type":"post","link":"https:\/\/qxf2.com\/blog\/cleaning-data-python-pandas\/","title":{"rendered":"Cleaning data with Python"},"content":{"rendered":"

I am sharing some tips and tricks on cleaning data and restructuring the data you are using for testing.<\/p>\n

Why this post?<\/h3>\n

Qxf2 works with many data intensive applications. I’ve noticed a pattern – my colleagues hit very similar data related problems across many different projects. That got me thinking critically about test data. I was thrilled to stumble upon an excellent paper on Tidy Data<\/a> that has helped me understand and articulate the problems with poorly structured data better. I’m sure a lot of people in the world of data science already know about this paper. But this blog is geared towards testers and I don’t know of too many testers who have been exposed to the idea of clean data. So I thought it would be worth writing a post to expose testers to the concept of tidy data. In addition to tidying data, I thought I would give you some examples of cleaning operations we have used on various projects. <\/p>\n

\n

Should QA think about designing data?<\/h3>\n

Yes. We think so. It has worked very well for us. Qxf2 has been testing many data intensive applications. My colleagues have tested survey applications, data querying and visualization tools, healthcare algorithms, patient data, etc. We work with startups and early stage software. So, usually, we have the opportunity to study the data carefully and design our own data sets for testing purposes. This practice of designing rich data sets to enable testing, as opposed to using what data exists on production, has helped our engineering colleagues move faster and made our applications and algorithms be more robust to mistakes in data. A couple of technical auditors (technical audits happen when our clients seek investments\/acquisitions) have praised QA’s approach to using designed test data. Designed test data also helps when testing the UI and makes for a good demo. So, usually, the time you spend designing good data is worth it. <\/p>\n

\n

How does a tester benefit from this post?<\/h3>\n

This post will help you think deeper about the data being used for testing. I think you can use the tips and tricks in this post in a few ways:
\na) If you are completely new to working with data, this post is a good starting point
\nb) When you are testing parsers, you can purposely create ‘dirty’ data to ensure the parser remains robust and that the error messages are human-friendly.
\nc) When you are testing algorithms, you can design test data so that you test not only the key pieces of the algorithm but also test the algorithm’s validity against missing pieces of data, incorrect data, updated data, etc.
\nd) If you are scraping data and using it for your testing, you will learn to structure your data better<\/p>\n

\n

Setup for data cleaning<\/h3>\n

I’ve tried my best to keep this blog post self-contained. I’ve created a simple data set with some common mistakes in it. By the end of this post, we would have cleaned this data set. You can download patient_heart_rate.csv over here<\/a>. I am using Python Pandas<\/a> to clean the data. But you are welcome to use just plain old Python and write your own code instead of using my snippets. The goal is to learn about the common difficulties with cleaning data rather than to learn the Python code used to clean the data.<\/p>\n

\n

Get to know the data<\/h3>\n

In any process involving data, before we start working, our first goal should always be understanding what the data is and what we want to achieve. This process involves looking at the data and trying to understand the columns\/rows of the data, no of records, the format of the data, semantic errors, missing entries, or inconsistent formatting, so it needs to be “cleaned” prior to analysis.<\/p>\n

In this post, we will summarize some data tidying examples using Python libraries. For this purpose, we have created a patient_heart_rate.csv dataset as an example. The data set has been kept small enough for you to be able to grok it all at once. The data is in csv format. Each row in the dataset has data about different individuals and their heart rate details for different time intervals. The columns contain information such as individual’s Age, Weight, Sex and Heart Rates taken at different time intervals. <\/p>\n

Here is how our dataset looks:
\n\"\"<\/a><\/p>\n\n

Problems in our dataset<\/h3>\n

For illustration purpose in the above dataset we have created each row with a problem as listed below:<\/p>\n

1. Missing headers in the csv file
\n2. Multiple variables are stored in one column
\n3. Column data contains inconsistent unit values
\n4. Missing values
\n5. An empty row in the data
\n6. Duplicate records in the data
\n7. Non-ASCII characters
\n8. Column headers are values and not variable names<\/p>\n

\n

Cleaning Data<\/h3>\n

The following sections illustrate each problem in detail and show how to tidy them using Python Pandas library. <\/p>\n

Problem 1 – Missing headers in the csv file<\/strong>
\nIf you look at the data file you notice that there is no header (See Fig 1). Pandas is smart enough to figure out that the first line of the file is the header. We can still read the file if the csv file doesn’t have a header by manually providing the headers. Have a look at the below code.<\/p>\n

\r\nimport pandas as pd\r\n\r\ncolumn_names= [\"Id\", \"Name\", \"Age\", \"Weight\",'m0006','m0612','m1218','f0006','f0612','f1218']\r\ndf = pd.read_csv(\"patient_heart_rate.csv\", names = column_names)\r\ndf.head()\r\n<\/pre>\n
As shown above we can declare our own headers. We read the data in the same way, except this time we pass in a new variable names = column_names, to tell Pandas to use our headers, as the original file has none. See Fig 2<\/p>\n
\"\"<\/a>
Fig 2 – Added headers to the csv file<\/figcaption><\/figure>\n
\n
Problem 2 – Multiple variables are stored in one column<\/strong>
\nIn this dataset patient_heart_rate, the Name column contains two variables Lastname and Firstname(See Fig 3). To respect the tidy principles, we want to isolate each variable in a single column.<\/p>\n
import pandas as pd\r\ndf = pd.read_csv(\"patient_heart_rate.csv\")\r\nprint (df)<\/pre>\n
\"\"<\/a>
Fig 3 \u2013 Name column having First and Last Name<\/figcaption><\/figure>\n
Technically, separating those two variables can be accomplished using the split function.
\nWe used the vectorised str.split with expand=True, this will unpack the list into the new columns as shown below.<\/p>\n
df[['Firstname','Lastname']] = df['Name'].str.split(expand=True)\r\ndf = df.drop('Name', axis=1)\r\nprint (df)<\/pre>\n
Once you execute the above code, the data looks like this(Fig 4)<\/p>\n
\"\"<\/a>
Fig 4 \u2013 First Name and Last Name as separate columns<\/figcaption><\/figure>\n
\n
Problem 3 \u2013 Column data contains inconsistent unit values<\/strong>
\nIf you observe in the dataset, the way the measurement unit was written for Weight column is not consistent. The second and seventh row contains data for inconsistent unit values (kgs,lbs). See Fig 5<\/p>\n
\"\"<\/a>
Fig 5 \u2013 Weight column not consistent(shown in kgs and lbs)<\/figcaption><\/figure>\n
The weight column is sometimes spelled as kgs, lbs etc., In order to resolve this we have written a python code to convert the measuring unit and represent weight column in kgs<\/p>\n
import pandas as pd\r\n#Read the data set\r\ndf = pd.read_csv(\"patient_heart_rate.csv\")\r\n#Get the Weight column\r\nweight = df['Weight']\r\n\r\nfor i in range (0 ,len(weight)):    \r\n    x= str(weight[i])\r\n    #Incase lbs is part of observation remove it\r\n    if \"lbs\" in x[-3:]:\r\n        #Remove the lbs from the value\r\n        x = x[:-3:]\r\n        #Convert string to float\r\n        float_x = float(x)\r\n        #Covert to kgs and store as int\r\n        y =int(float_x\/2.2)\r\n        #Convert back to string\r\n        y = str(y)+\"kgs\"\r\n        weight[i]= y\r\nprint (df)<\/pre>\n
This results in a tidy dataset. See Fig 6<\/p>\n
\"\"<\/a>
Fig 6 \u2013 Weight column represented in kgs<\/figcaption><\/figure>\n
\n
Problem 4 – Missing values<\/strong>
\nThere are few missing values in the Age, Weight and Heart Rate values in the dataset. It is quite common for some values to be missing from datasets. This typically means that a piece of information was simply not collected. There are several options for handling missing data. But we encourage testers to talk to your subject matter experts<\/strong> about your missing data. Typical ways your subject matter experts will suggest handling missing values are:
\na. Deletion:<\/strong> Remove records with missing values
\nb. Dummy substitution:<\/strong> Replace missing values with a dummy but valid value: e.g.: 0 for numerical values.
\nc. Mean substitution:<\/strong> Replace the missing values with the mean.
\nd. Frequent substitution:<\/strong> Replace the missing values with the most frequent item.
\ne. Improve the data collector:<\/strong> Your business folk will talk to the clients and inform them about why it is worth fixing the problem with the data collector.<\/p>\n
\n
Problem 5 – An empty row in the data<\/strong>
\nIf you observe in the dataset, you can see that there is a Empty row. If you read this file with Pandas library, and look at the content of your dataframe, you have a empty row that has been filled with NaNs.See Fig 7<\/p>\n
import pandas as pd\r\ndf = pd.read_csv(\"patient_heart_rate.csv\")\r\nprint(df)\r\n<\/pre>\n
\"\"<\/a>
Fig 7 \u2013 Record with Empty Lines<\/figcaption><\/figure>\n
There is no option to ignore the row in the function read_csv(), so we need to handle this using dropna() method as shown in below code.<\/p>\n
df.dropna(how=\"all\", inplace=True)<\/pre>\n
After executing this code, the empty row is removed. See Fig 8<\/p>\n
\"\"<\/a>
Fig 8 \u2013 Empty Lines Removed<\/figcaption><\/figure>\n
\n
Problem 6 \u2013 Duplicate records in the data<\/strong>
\nSometimes, in the dataset there may be some duplicate records. In our dataset there is a record which is repeated in 6th and 9th rows. See Fig 9 for duplicate records.<\/p>\n
 import pandas as pd\r\n#Read the data set\r\ndf = pd.read_csv(\"patient_heart_rate.csv\")\r\nprint (df)<\/pre>\n
\"\"<\/a>
Fig 9 \u2013 Duplicate Records<\/figcaption><\/figure>\n
First, check if you have duplicate records. If duplicate records exist, then you can use the Pandas function drop_duplicates() to remove the duplicate records.<\/p>\n
\r\n#Use drop_duplicates method to remove duplicate records. Using subset helps to consider certain columns for identifying duplicates.\r\ndf = df.drop_duplicates(subset=['Firstname','Lastname'])\r\nprint (df)\r\n<\/pre>\n
Once this code is executed, the duplicate records get deleted. See Fig 10<\/p>\n
\"\"<\/a>
Fig 10 \u2013 Duplicate Records Removed<\/figcaption><\/figure>\n
\n
Problem 7 – Non-ASCII characters<\/strong>
\nThere are few non-ASCII characters in the Firstname and Lastname columns in the data. See Fig 11 below<\/p>\n
\"\"<\/a>
Fig 11 – Non-ASCII characters in the data<\/figcaption><\/figure>\n
There are multiple ways how you can handle non-ASCII characters. You can remove them, replace them with other characters, just post a warning, etc. In this example, we are removing them.<\/p>\n
\r\nimport pandas as pd\r\ndf = pd.read_csv(\"patient_heart_rate.csv\")\r\ndf.Firstname.replace({r'[^\\x00-\\x7F]+':''}, regex=True, inplace=True)\r\ndf.Lastname.replace({r'[^\\x00-\\x7F]+':''}, regex=True, inplace=True)\r\nprint (df)\r\n<\/pre>\n
After executing this code, the non-ASCII characters are replaced with blank. See Fig 12 below<\/p>\n
\"\"<\/a>
Fig 12 \u2013 After removing non-ASCII characters<\/figcaption><\/figure>\n
\n
Problem 8 – Column headers are values and not variable names<\/strong>
\nThe columns headers are composed of the values for Sex and Hour range variables for each individual row. Corresponding to each ‘m’ column for males, there is also an ‘f’ column for females, f0006, f0612 and so on as shown in the Fig 13.
\n
\"\"<\/a>
Fig 13 \u2013 Column headers are values<\/figcaption><\/figure><\/p>\n
A tidy version of this dataset is one in which we need to remove the different values from the header and unpivot them into rows. Here, the Sex and Hour range would not be columns headers but rather row values in Sex and Time columns. In order to tidy this dataset, we need to “melt” the data. We\u2019ll first “melt” the Sex + Hour Range columns into a single one. The pandas library has a built-in function that allows doing just that. It \u201cunpivots\u201d a DataFrame from a wide format to a long format. Once we have that single column, we\u2019ll derive three columns from it: sex, time_lower and time_upper. With those, we\u2019ll be able to build a tidy dataset.<\/p>\n
#Melt the Sex + time range columns in single column\r\ndf = pd.melt(df,id_vars=['Id','Age','Weight','Firstname','Lastname'], value_name=\"PulseRate\",var_name=\"sex_and_time\").sort_values(['Id','Age','Weight','Firstname','Lastname'])\r\n\r\n# Extract Sex, Hour lower bound and Hour upper bound group\r\ntmp_df = df[\"sex_and_time\"].str.extract(\"(\\D)(\\d+)(\\d{2})\",expand=True)\r\n\r\n# Name columns\r\ntmp_df.columns = [\"Sex\", \"hours_lower\", \"hours_upper\"]\r\n\r\n# Create Time column based on \"hours_lower\" and \"hours_upper\" columns\r\ntmp_df[\"Time\"] = tmp_df[\"hours_lower\"] + \"-\" + tmp_df[\"hours_upper\"]\r\n\r\n# Merge \r\ndf = pd.concat([df, tmp_df], axis=1)\r\n\r\n# Drop unnecessary columns and rows\r\ndf = df.drop(['sex_and_time','hours_lower','hours_upper'], axis=1)\r\ndf = df.dropna()\r\ndf.to_csv('outputcleanup.csv',index=False)\r\nprint (df.head())\r\n<\/pre>\n
Once you execute the above code, the data looks like this<\/p>\n
Fig.14 shows the results of melting the patient_heart_rate dataset with row values in Sex and Time columns.
\n
\"\"<\/a>
Fig 14 – Melting the patient_heart_rate dataset with row values in Sex and Time columns.<\/figcaption><\/figure><\/p>\n
Fig.15 shows the results of splitting the single column into two real variables: Time and Sex<\/p>\n
\"\"<\/a>
Fig 15 – Tidy data after Splitting the single column into two variables: Time and Sex<\/figcaption><\/figure>\n
\n
 Problems I have not covered<\/h3>\n
There were two important and common problems that I could not do justice in this already long post:
\na) handling dates
\nb) correcting character encodings (a problem you hit when you scrape data off the web)
\nI want to cover them as part of a new post on data scraping. If you hit these problems, before I publish my other post, I’d encourage you to Google about them.<\/p>\n
\n
In this post, you have got the essence of tidying data using Python Pandas. Data cleaning is not a glamorous job but is critical in data-intensive applications. I hope I have convinced you, the tester, that it is not so scary. Depending on your requirements and analysis you can tidy your data with simple methods as shown in this post. <\/p>\n
If you are a startup finding it hard to hire technical QA engineers, learn more about Qxf2 Services<\/a>.<\/strong><\/p>\n
\n
References:<\/h3>\n
I stongly recommend you spend some time on the references below. I learnt so much from them!
\n1. Tidy data<\/a>: Read this paper if you are serious about testing. The entire Qxf2 team highly, highly<\/strong> recommends it.
\n2. Engineering data analysis<\/a>: This is an excellent talk on data and how it can be used. Talk is by the author of the tidy data paper.
\n3. Tidy data and Python<\/a>: The tidy data paper was written with R snippets. This post is for Pythonistas.
\n4. Data cleansing<\/a>: Wiki has good thoughts on what are attributes of high quality data <\/p>\n
\n