Pharmacy Sales Data: A Pandas Project

Introduction

This week, I worked on a pharmacy sales dataset using pandas. The dataset had 6,150 rows and 26 columns, covering orders, products, customers, payments and deliveries.

While cleaning the data, I found duplicate rows, missing values and inconsistent labels for cities and payment methods. I also identified numerical values that needed further investigation, including a customer age recorded as 999.

In this article, I’ll walk through the cleaning steps I took and explain the decisions behind them.

Understanding the Dataset

I loaded the CSV file into a pandas DataFrame and previewed the first five rows:

import pandas as pd

df = pd.read_csv("data/pharmacy_sales_raw.csv")
df.head()

I also used df.sample(6) to look at six randomly selected rows beyond the initial preview.

Before changing anything, I checked the dataset’s size, column types, missing values and summary statistics:

df.shape
df.info()
df.isna().sum()
df.describe()

I ran these checks separately in my notebook to see each result.

The order and delivery dates were stored as text, and so was unit_price_kes, which I expected to contain numeric prices. Several columns also had missing values.

The numerical summary showed values worth investigating: customer ages reached 999, quantities included negative numbers, and the highest discount was 150%.

These checks gave me a starting point for the cleaning.

Removing Duplicate Rows

The first cleaning step was checking for duplicate rows:

df.duplicated().sum()

This returned 120, meaning there were 120 extra copies of rows. I removed them and checked the result:

df = df.drop_duplicates()

df.duplicated().sum()

The second check returned 0, leaving 6,030 rows.

However, a separate check on the order IDs returned 30:

df['order_id'].duplicated().sum()

The two checks looked at different things. df.duplicated() compared every column, while the order ID check compared only order_id. The result of 30 counted repeated occurrences beyond the first occurrence of each ID.

The remaining repeated IDs belonged to rows that differed in at least one other column. I left them for further investigation rather than deleting them based on the ID alone. I needed to establish whether each row represented a whole order or an individual item within an order.

Cleaning Inconsistent Labels

The city column had 12 unique labels, but some referred to the same place. Nairobi appeared as Nairobi, NAIROBI, nairobi, NRB and another version with a trailing space.

Leaving these unchanged would split Nairobi’s sales into separate groups during analysis. I standardized them using replace():

df['city'] = df['city'].replace({
    'NAIROBI': 'Nairobi',
    'NRB': 'Nairobi',
    'nairobi': 'Nairobi',
    'Nairobi ': 'Nairobi',
    'kisumu': 'Kisumu',
    'mombasa': 'Mombasa',
    'MSA': 'Mombasa'
})

df['city'].unique()

After this, the column contained five city names: Nairobi, Eldoret, Mombasa, Kisumu and Nakuru.

I found a similar issue in the payment methods. M-Pesa had several spellings, while cash and card payments had inconsistent capitalization.

payment_mapping = {
    'CARD': 'Card',
    'card': 'Card',
    'Mpesa': 'M-Pesa',
    'mpesa': 'M-Pesa',
    'MPESA': 'M-Pesa',
    'M-PESA': 'M-Pesa',
    'CASH': 'Cash',
    'cash': 'Cash',
    'Visa': 'Card'
}

df['payment_method'] = (
    df['payment_method'].replace(payment_mapping)
)

I grouped Visa under Card because I was working with broad payment methods. That meant the cleaned column no longer distinguished Visa from other card payments.

Checking the unique values after each change helped me confirm which labels had been fixed and which still needed attention.

Cleaning Promo Codes

For promo codes, I removed spaces and hyphens, then converted the codes to uppercase:

df['promo_code'] = (
    df['promo_code']
    .str.strip()
    .replace(r'[\s-]', '', regex=True)
    .str.upper()
)

In the regex pattern, \s matched whitespace, while the hyphen matched a literal -. Replacing these with '' removed them. The .str.strip() step removed leading and trailing whitespace, although the regex also covered those spaces.

For example, save-10 would become SAVE10.

This cleaning step assumed that spaces, hyphens and letter case didn’t distinguish different promotions. Removing those characters would have been a problem if they were meaningful parts of the codes.

Afterwards, I checked the results:

df['promo_code'].unique()

The output contained 30 non-missing codes. Missing values remained missing. I still needed to establish whether those entries meant no promotion had been used or the information hadn’t been recorded.

Converting the Date Columns

I converted the order and delivery dates from text to datetime values:

df['order_date'] = pd.to_datetime(
    df['order_date'],
    format='mixed'
)

df['delivery_date'] = pd.to_datetime(
    df['delivery_date'],
    format='mixed'
)

I used format='mixed' to let pandas infer the format for each value rather than apply one fixed format to the entire column. This required care with ambiguous dates, where the day and month could be interpreted in either order.

After conversion, I checked the column types again:

df[['order_date', 'delivery_date']].info()

Both columns now had datetime types. Missing delivery dates appeared as NaT, pandas’ marker for a missing datetime value.

The conversion changed how the dates were stored, but it didn’t resolve the missing entries or confirm that every date was correct.

Investigating Unusual Customer Ages

The customer age column stood out because its highest value was 999. I used a boxplot and the interquartile range (IQR) to examine the distribution more closely:

age_q1 = df['customer_age'].quantile(0.25)
age_q3 = df['customer_age'].quantile(0.75)

age_iqr = age_q3 - age_q1

lower_bound = age_q1 - (1.5 * age_iqr)
upper_bound = age_q3 + (1.5 * age_iqr)

The lower and upper boundaries were 4.5 and 64.5. These were statistical boundaries, not limits on valid customer ages. A 70-year-old customer could reasonably buy from a pharmacy, even if their age fell beyond the upper boundary.

I inspected the rows above that boundary:

df[df['customer_age'] > upper_bound]

In my initial cleaning attempt, I then applied this filter:

df = df[df['customer_age'] <= 100]

On review, this step needed more care. It excluded rows with missing ages as well as ages above 100. An age above 100 wasn’t automatically invalid, and a missing age didn’t make the rest of a sales record unusable.

I identified this as a step to revisit. Flagging questionable ages for review would allow me to investigate them without immediately removing the entire order.

Conclusion

Working through this dataset helped me understand why cleaning needed more than code that ran without errors. I removed 120 duplicate rows, standardized inconsistent labels and converted the order and delivery dates from text to datetime values.

Some decisions needed more care. Repeated order IDs weren’t the same as duplicate rows, and an unusual customer age wasn’t enough reason to discard a sales record. Reviewing my age filter also showed how easily I could remove rows I hadn’t intended to lose.

My biggest takeaway was to check the result of each change. A column could look cleaner while useful information had been lost. I needed to account for both.