Cleaning Up Currency Data
Mon 28 October 2019
Last updated
Mon 28 October 2019
Last updated
The other day, I was using pandas to clean some messy Excel data that included several thousand rows of inconsistently formatted currency values. When I tried to clean it up, I realized that it was a little more complicated than I first thought. Coincidentally, a couple of days later, I followed a twitter thread which shed some light on the issue I was experiencing. This article summarizes my experience and describes how to clean up messy currency fields and convert them into a numeric value for further analysis. The concepts illustrated here can also apply to other types of pandas data cleanup tasks.
Here is a simple view of the messy Excel data:
In this example, the data is a mixture of currency labeled and non-currency labeled values. For a small example like this, you might want to clean it up at the source file. However, when you have a large data set (with manually entered data), you will have no choice but to start with the messy data and clean it in pandas.
Before going further, it may be helpful to review my prior article on data types. In fact, working on this article drove me to modify my original article to clarify the types of data stored in object
columns.
Letâs read in the data:
Customer | Sales | |
0 | Jones Brothers | 500 |
1 | Beta Corp | $1,000.00 |
2 | Globex Corp | 300.1 |
3 | Acme | $750.01 |
4 | Initech | 300 |
5 | Hooli | 250 |
Iâve read in the data and made a copy of it in order to preserve the original.
One of the first things I do when loading data is to check the types:
Not surprisingly the Sales
column is stored as an object. The â$â and â,â are dead giveaways that the Sales
column is not a numeric column. More than likely we want to do some math on the column so letâs try to convert it to a float.
In the real world data set, you may not be so quick to see that there are non-numeric values in the column. In my data set, my first approach was to try to use astype()
The traceback includes a ValueError
and shows that it could not convert the $1,000.00 string to a float. Ok. That should be easy to clean up.
Letâs try removing the â$â and â,â using str.replace
:
Hmm. That was not what I expected. For some reason, the string values were cleaned up but the other values were turned into NaN
. Thatâs a big problem.
To be honest, this is exactly what happened to me and I spent way more time than I should have trying to figure out what was going wrong. I eventually figured it out and will walk through the issue here so you can learn from my struggles!
The twitter thread from Ted Petrou and comment from Matt Harrison summarized my issue and identified some useful pandas snippets that I will describe below.
Basically, I assumed that an object
column contained all strings. In reality, an object column can contain a mixture of multiple types.
Letâs look at the types in this data set.
Ahhh. This nicely shows the issue. The apply(type)
code runs the type
function on each value in the column. As you can see, some of the values are floats, some are integers and some are strings. Overall, the column dtype
is an object.
Here are two helpful tips, Iâm adding to my toolbox (thanks to Ted and Matt) to spot these issues earlier in my analysis process.
First, we can add a formatted column that shows each type:
Customer | Sales | Sales_Type | |
0 | Jones Brothers | 500 | int |
1 | Beta Corp | $1,000.00 | str |
2 | Globex Corp | 300.1 | float |
3 | Acme | $750.01 | str |
4 | Initech | 300 | int |
5 | Hooli | 250 | int |
Or, here is a more compact way to check the types of data in a column using value_counts()
:
I will definitely be using this in my day to day analysis when dealing with mixed data types.
To illustrate the problem, and build the solution; I will show a quick example of a similar problem using only python data types.
First, build a numeric and string variable.
This example is similar to our data in that we have a string and an integer. If we want to clean up the string to remove the extra characters and convert to a float:
Ok. Thatâs what we want.
What happens if we try the same thing to our integer?
Thereâs the problem. We get an error trying to use string functions on an integer.
When pandas tries to do a similar approach by using the str
accessor, it returns an NaN
instead of an error. Thatâs why the numeric values get converted to NaN
.
The solution is to check if the value is a string, then try to clean it up. Otherwise, avoid calling string functions on a number.
The first approach is to write a custom function and use apply
.
This function will check if the supplied value is a string and if it is, will remove all the characters we donât need. If it is not a string, then it will return the original value.
Here is how we call it and convert the results to a float. I also show the column with the types:
Customer | Sales | Sales_Type | |
0 | Jones Brothers | 500.00 | float |
1 | Beta Corp | 1000.00 | float |
2 | Globex Corp | 300.10 | float |
3 | Acme | 750.01 | float |
4 | Initech | 300.00 | float |
5 | Hooli | 250.00 | float |
We can also check the dtypes
:
Or look at the value_counts
:
Ok. That all looks good. We can proceed with any mathematical functions we need to apply on the sales column.
Before finishing up, Iâll show a final example of how this can be accomplished using a lambda function:
The lambda function is a more compact way to clean and convert the value but might be more difficult for new users to understand. I personally like a custom function in this instance. Especially if you have to clean up multiple columns.
The final caveat I have is that you still need to understand your data before doing this cleanup. I am assuming that all of the sales values are in dollars. That may or may not be a valid assumption.
If there are mixed currency values here, then you will need to develop a more complex cleaning approach to convert to a consistent numeric format. Pyjanitor has a function that can do currency conversions and might be a useful solution for more complex problems.
After I originally published the article, I received several thoughtful suggestions for alternative ways to solve the problem. The first suggestion was to use a regular expression to remove the non-numeric characters from the string.
This approach uses pandas Series.replace. It looks very similar to the string replace approach but this code actually handles the non-string values appropriately.
Regular expressions can be challenging to understand sometimes. However, this one is simple so I would not hesitate to use this in a real world application. Thanks to Serg for pointing this out.
The other alternative pointed out by both Iain Dinwoodie and Serg is to convert the column to a string and safely use str.replace.
First we read in the data and use the dtype
argument to read_excel
to force the original column of data to be stored as a string:
We can do a quick check:
Then apply our cleanup and type conversion:
Since all values are stored as strings, the replacement code works as expected and does not incorrectly convert some values to NaN.
The pandas object
data type is commonly used to store strings. However, you can not assume that the data types in a column of pandas objects
will all be strings. This can be especially confusing when loading messy currency data that might include numeric values with symbols as well as integers and floats.
It is quite possible that naive cleaning approaches will inadvertently convert numeric values to NaN
. This article shows how to use a couple of pandas tricks to identify the individual types in an object column, clean them and convert them to the appropriate numeric value.
I hope you have found this useful. If you have any other tips or questions, let me know in the comments.
3-Nov-2019: Updated article to include a link to the data and highlight some alternative solutions provided in the comments.
Reference : https://pbpython.com/currency-cleanup.html