Note

  • This tutorial is also available on nbviewer, offering an alternative platform for your learning convenience.
  • Pandas Ninja: Take your skills to the next level with comprehensive Jupyter Notebook tutorials covering dates, group by, plotting, pivot tables, and more. Includes specialized tutorials for Excel and SQL developers, helping you master data analysis with Pandas.

Description:

You’re a data analyst for a non-profit organization, and you’ve been tasked with cleaning up a messy dataset of donations. The data is a bit of a disaster, with missing values, duplicates, and inconsistent formatting. Your mission is to use your Pandas skills to wrangle the data into shape.

Tasks:

  • Clean up the Mess: Remove duplicates, handle missing values, and ensure data types are correct.
  • Standardize the data: Normalize the ‘Donation Amount’ column and convert the date column to a standard format.
  • Data quality check: Identify and correct any inconsistent or invalid data.
# import libraries
import pandas as pd
import numpy as np
import sys
import re
 
print('Python version ' + sys.version)
print('Pandas version ' + pd.__version__)
print('Numpy version ' + np.__version__)
Python version 3.11.7 | packaged by Anaconda, Inc. | (main, Dec 15 2023, 18:05:47) [MSC v.1916 64 bit (AMD64)]
Pandas version 2.2.1
Numpy version 1.26.4

The Data

The columns below represent information about individual donations, the date they were made, and the campaign that drove the donation. The goal is to clean, transform, and prepare this data for analysis.

Here’s a breakdown of what each column in the sample data represents:

  • Donor ID: A unique identifier for each donor
  • Donation Amount: The amount donated by each donor ( initially in a mix of numeric and string formats, requiring cleanup)
  • Date: The date each donation was made
  • Campaign: The marketing campaign or channel that led to the donation

Important Note about the Donation Amount Column:

The logic below will generate a mix of:

  • Numeric values (e.g., 10.50, 500.00)
  • String values with words (e.g., “10 thousand”, “5 dollars and 25 cents”)
  • String values with currency symbols (e.g., ”$50”, ”$1000“)

Your task will be to clean up this column by converting all values to a standard numeric format, handling the various string formats, and dealing with any potential errors or inconsistencies. Good luck!

# set the seed
np.random.seed(0)
 
# synthetic data
data = {
'donor_id': np.random.randint(1, 1000, 10000),
'date': np.random.choice(pd.date_range('2022-01-01', periods=365), 10000),
'campaign': np.random.choice(['Email', 'Social Media', 'Event'], 10000),
'donation_amount': np.random.choice([
	np.random.uniform(10, 1000), # numeric value
	f'{np.random.randint(1, 100)} thousand', # string value (e.g., "10 thousand")
	f'{np.random.randint(1, 10)} dollars and {np.random.randint(1, 100)} cents', # string value (e.g., "5 dollars and 25 cents")
	f'${np.random.randint(1, 100)}', # string value with currency symbol (e.g., "$50")
	], 10000)
}
 
# create dataframe
df = pd.DataFrame(data)
 
## introduce some messiness ##
 
# make the column the wrong datatype
df['donor_id'] = data['donor_id'].astype(str)
 
# missing values
df.loc[df.index % 3 == 0, 'donation_amount'] = np.nan
 
# messy is my middle name
df['date'] = data['date'].astype(str)
df.loc[df.index % 5 == 0, 'date'] = 'Invalid Date'
 
# the marketing manager is not going to be happy :)
df.loc[df.index % 7 == 0, 'campaign'] = 'Unknown'
 
df
donor_iddatecampaigndonation_amount
0685Invalid DateUnknownNaN
15602022-03-07T00:00:00.000000000Email6 dollars and 98 cents
26302022-11-08T00:00:00.000000000Social Media76 thousand
31932022-03-25T00:00:00.000000000EmailNaN
48362022-04-07T00:00:00.000000000Email$81
9995426Invalid DateSocial Media$81
99968912022-04-18T00:00:00.000000000UnknownNaN
99977782022-08-24T00:00:00.000000000Event$81
99989742022-10-07T00:00:00.000000000Email$81
9999742022-07-09T00:00:00.000000000EventNaN
10000 rows × 4 columns

Let’s start by looking at the datatypes.

As you can expect, Pandas is treating all of the columns as strings. Let the clean up process begin.

df.info()

<class ‘pandas.core.frame.DataFrame’> RangeIndex: 10000 entries, 0 to 9999 Data columns (total 4 columns):

Column Non-Null Count Dtype

`--- ------ -------------- ----- 0 donor_id 10000 non-null object 1 date 10000 non-null object 2 campaign 10000 non-null object 3 donation_amount 6666 non-null object dtypes: object(4) memory usage: 312.6+ KB

Clean up the Mess:

Remove duplicates, handle missing values, and ensure data types are correct.

If we assume that we will not be able to get the correct donation amounts, we might as well remove those rows from the data.

df = df.dropna()
 
df.info()

<class ‘pandas.core.frame.DataFrame’> Index: 6666 entries, 1 to 9998 Data columns (total 4 columns):

Column Non-Null Count Dtype

`--- ------ -------------- ----- 0 donor_id 6666 non-null object 1 date 6666 non-null object 2 campaign 6666 non-null object 3 donation_amount 6666 non-null object dtypes: object(4) memory usage: 260.4+ KB

The marketing manager told us to replace any missing dates with ‘1970-01-01’ so we can identify these and deal with them later.

# identify the invalid dates
mask = df['date'] == 'Invalid Date'
 
df[mask].head()
donor_iddatecampaigndonation_amount
5764Invalid DateSocial Media77.55500350452421
10278Invalid DateEvent76 thousand
20487Invalid DateEvent77.55500350452421
25850Invalid DateEmail76 thousand
35710Invalid DateUnknown6 dollars and 98 cents

Here is where we set the dates to 1970-01-01.

df.loc[mask,'date'] = '1970-01-01'
 
df
donor_iddatecampaigndonation_amount
15602022-03-07T00:00:00.000000000Email6 dollars and 98 cents
26302022-11-08T00:00:00.000000000Social Media76 thousand
48362022-04-07T00:00:00.000000000Email$81
57641970-01-01Social Media77.55500350452421
73602022-10-20T00:00:00.000000000Unknown76 thousand
99923082022-01-13T00:00:00.000000000Email77.55500350452421
99946942022-07-21T00:00:00.000000000Event6 dollars and 98 cents
99954261970-01-01Social Media$81
99977782022-08-24T00:00:00.000000000Event$81
99989742022-10-07T00:00:00.000000000Email$81
6666 rows × 4 columns

Although we successfully converted the strings into dates, the date column remains in string format.

df.info()

<class ‘pandas.core.frame.DataFrame’> Index: 6666 entries, 1 to 9998 Data columns (total 4 columns):

Column Non-Null Count Dtype

`--- ------ -------------- ----- 0 donor_id 6666 non-null object 1 date 6666 non-null object 2 campaign 6666 non-null object 3 donation_amount 6666 non-null object dtypes: object(4) memory usage: 260.4+ KB

Convert string column to a datetime object.

# `format='mixed'`, the format will be inferred for each element individually as the 1970 dates do not have the same format as the rest
 
pd.to_datetime(df['date'], format='mixed')

1 2022-03-07 2 2022-11-08 4 2022-04-07 5 1970-01-01 7 2022-10-20 …
9992 2022-01-13 9994 2022-07-21 9995 1970-01-01 9997 2022-08-24 9998 2022-10-07 Name: date, Length: 6666, dtype: datetime64[ns]

This morning, for some reason I can’t get these datatypes to behave… the code below did not work.

# convert to date object
df.loc[:,'date'] = pd.to_datetime(df['date'], format='mixed')
 
df.info()

<class ‘pandas.core.frame.DataFrame’> Index: 6666 entries, 1 to 9998 Data columns (total 4 columns):

Column Non-Null Count Dtype

`--- ------ -------------- ----- 0 donor_id 6666 non-null object 1 date 6666 non-null object 2 campaign 6666 non-null object 3 donation_amount 6666 non-null object dtypes: object(4) memory usage: 260.4+ KB

We can also take care of the Donor ID pretty easily.

This also did not work…

df.loc[:,'donor_id'] = df.loc[:,'donor_id'].astype(int)
 
df.info()

<class ‘pandas.core.frame.DataFrame’> Index: 6666 entries, 1 to 9998 Data columns (total 4 columns):

Column Non-Null Count Dtype

`--- ------ -------------- ----- 0 donor_id 6666 non-null object 1 date 6666 non-null object 2 campaign 6666 non-null object 3 donation_amount 6666 non-null object dtypes: object(4) memory usage: 260.4+ KB

This did the trick for me to get the date types to be represented correctly.

df = df.convert_dtypes()
 
df.info()

<class ‘pandas.core.frame.DataFrame’> Index: 6666 entries, 1 to 9998 Data columns (total 4 columns):

Column Non-Null Count Dtype

`--- ------ -------------- -----
0 donor_id 6666 non-null Int64
1 date 6666 non-null datetime64[ns] 2 campaign 6666 non-null string
3 donation_amount 6666 non-null string
dtypes: Int64(1), datetime64ns, string(2) memory usage: 266.9 KB

Donation Amount Cleanup

  • Remove the dollar sign
  • Apply a custom function to convert the values to a numeric format
# remove dollar sign
df.loc[:,'donation_amount'] = df.loc[:,'donation_amount'].apply(lambda x:x.replace("$",""))
 
df.loc[:,'donation_amount'].head()

1 6 dollars and 98 cents 2 76 thousand 4 81 5 77.55500350452421 7 76 thousand Name: donation_amount, dtype: string

def clean_column(value):
	''' identify pattern and clean up
	patterns: "10 thousand", "5 dollars and 25 cents"
	'''
	
	pattern1 = r'\d+ thousand'
	pattern2 = r'\d+ dollars and \d+ cents'
	
	if re.search(pattern1, value):
		# remove all non numeric characters from the string
		return str(int(re.sub(r'[^\d]', '', value)) * 1000)
	
	elif re.search(pattern2, value):
		# remove all non numeric characters from the strings
		dollars = re.sub(r'[^\d]', '', value.split('and')[0])
		cents = re.sub(r'[^\d]', '', value.split('and')[1])
		return dollars + "." + cents
	
	else:
		return value
 
df.loc[:,'donation_amount'] = df['donation_amount'].apply(clean_column)
 
df['donation_amount'].head()

1 6.98 2 76000 4 81 5 77.55500350452421 7 76000 Name: donation_amount, dtype: string

Now let’s fix the datatype for the donation amount.

df['donation_amount'] = df.loc[:,'donation_amount'].astype(float)
 
df.info()

<class ‘pandas.core.frame.DataFrame’> Index: 6666 entries, 1 to 9998 Data columns (total 4 columns):

Column Non-Null Count Dtype

`--- ------ -------------- -----
0 donor_id 6666 non-null Int64
1 date 6666 non-null datetime64[ns] 2 campaign 6666 non-null string
3 donation_amount 6666 non-null float64
dtypes: Int64(1), datetime64ns, float64(1), string(1) memory usage: 266.9 KB

OK, so we have taken care of a lot here.

  • The donor_id column is now in integer format
  • The date column is now in the correct format
  • The donation_amount column has been successfully cleaned up and converted to the correct numeric format
# let's take a peek at the data
df.head(20)
donor_iddatecampaigndonation_amount
15602022-03-07Email6.980000
26302022-11-08Social Media76000.000000
48362022-04-07Email81.000000
57641970-01-01Social Media77.555004
73602022-10-20Unknown76000.000000
8102022-06-18Social Media81.000000
102781970-01-01Event76000.000000
117552022-02-02Event76000.000000
136002022-09-21Social Media6.980000
14712022-05-25Unknown6.980000
166012022-12-29Event76000.000000
173972022-01-31Event81.000000
197062022-12-05Social Media76000.000000
204871970-01-01Event77.555004
22882022-07-05Social Media6.980000
231752022-07-24Email81.000000
258501970-01-01Email76000.000000
266782022-06-20Event76000.000000
288462022-05-20Unknown77.555004
29732022-08-31Email6.980000
df.describe()
donor_iddatedonation_amount
count6666.066666666.000000
mean501.1923192012-01-04 18:13:04.15841587219297.706620
min1.01970-01-01 00:00:006.980000
25%255.02022-01-25 00:00:0077.555004
50%499.02022-05-24 00:00:0077.555004
75%755.02022-09-13 00:00:0076000.000000
max999.02022-12-31 00:00:0076000.000000
std288.740445NaN33034.244560

Data Gaze

I am going to recommend you get this data into Microsoft Excel and do a quick glance. Excel does a much better job at letting you analyze the data on your nice and big monitor.

df.to_clipboard()

If you find a better way to update datatypes, please share it with me.