Pandas - The Donor Data Debacle

Let's connect! Send me a connection invitation. I regularly share Jupyter Notebooks on Pandas and would love to expand my network.

Explore my profile: Head to my profile to see more about my work, skills, and experience.

If you're feeling generous: Repost this article with your network and help spread the word!

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.

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!

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.

<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.

<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.

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

6666 rows × 4 columns

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

<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.

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.

<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...

<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.

<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), datetime64[ns](1), 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

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

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.

<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), datetime64[ns](1), 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

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.

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