Categories: Python Pandas Tutorial
Tags:

Audience Analysis Using Python Pandas: Part 1 – EDA, Missing Values, fillna(), dropna() & interpolate()

This Audience Analysis Using Python Pandas project is for beginners to learn EDA and How to handle missing values.

Video Tutorial is Here: https://youtu.be/NHYwNUHQHa8

Video Tutorial Chapter is here: https://colab.research.google.com/drive/1AAlnavOnUgCEi20hIVXM_RYxohDh4R1l?usp=sharing#scrollTo=YbbejNg6MoY3

1. Introduction

In this project, we will perform Audience Analysis using Python Pandas.

Our dataset contains 50 audience records and 8 columns:

  • Customer_ID
  • Age
  • Gender
  • Profession
  • City
  • Monthly_Income
  • Monthly_Saving
  • Monthly_Expenses

The dataset also contains 5 missing values. We will use these missing values to understand different data-cleaning techniques.

Get the Dataset here: https://github.com/slidescope/audience-analysis-dataset-for-pandas-fillna-write_to_excel

In this chapter, we will:

  1. Import Pandas
  2. Read the Excel dataset
  3. Perform basic EDA
  4. Check missing values
  5. Identify rows containing NaN
  6. Understand fillna()
  7. Understand dropna()
  8. Understand interpolate()
  9. Create a cleaned dataset
  10. Save the cleaned dataset to Excel

2. Import Pandas

First, import the Pandas library.

import pandas as pd

Pandas is one of the most important Python libraries for data analysis.

3. Read the Excel Dataset

Our dataset is stored in an Excel file named:

audience_analysis_clustering_dataset.xlsx

We can read it using pd.read_excel().

df = pd.read_excel("audience_analysis_clustering_dataset.xlsx")

Let’s display the dataset.

df

The Excel data is now stored inside the Pandas DataFrame called df.

4. Check the First Five Rows

The head() function displays the first five rows.

df.head()

We can also specify how many rows we want to see.

df.head(10)

This displays the first 10 audience records.

5. Check the Last Five Rows

The tail() function displays the last five rows.

df.tail()

We can also display the last 10 rows.

df.tail(10)

6. Check Dataset Shape

Now let’s check the number of rows and columns.

df.shape

Output:

(50, 8)

This means our dataset contains:

50 rows and 8 columns.

7. Check Dataset Information

The info() function gives us important information about our dataset.

df.info()

It shows:

  • Number of entries
  • Column names
  • Non-null values
  • Data types
  • Memory usage

This is particularly useful for checking whether Pandas has correctly identified our numerical and categorical columns.

8. Check Missing Values

Now we will check whether our dataset contains missing values.

df.isnull()

This returns:

  • True → value is missing
  • False → value is present

However, the output can be difficult to read.

Instead, let’s count missing values in every column.

df.isnull().sum()

This gives us a much cleaner result.

For our dataset, we have 5 missing values in total.

9. Count Total Missing Values

To find the total number of missing values across the entire DataFrame:

df.isnull().sum().sum()

Output:

5

So our Audience Analysis dataset contains five missing values.

10. Find the Rows Containing Missing Values

We can identify the actual rows containing missing values.

df[df.isnull().any(axis=1)]

This is extremely useful because we can now inspect which audience records contain missing information.

11. Handling Missing Values with fillna()

Now we move from EDA to data cleaning.

The first technique is fillna().

fillna() replaces missing values with a value that we specify.

For example, suppose we want to replace a missing value in Monthly_Income with the mean income.

First, calculate the mean:

df["Monthly_Income"].mean()

Then use fillna():

df["Monthly_Income"] = df["Monthly_Income"].fillna(
    df["Monthly_Income"].mean()
)

Now the missing income value has been replaced by the average income.

12. Using Median Instead of Mean

Mean is not always the best choice.

If a column contains extreme values, the median can sometimes be more appropriate.

For example:

median_income = df["Monthly_Income"].median()

df["Monthly_Income"] = df["Monthly_Income"].fillna(median_income)

For audience income data, comparing mean and median is useful before deciding which value should be used.

13. Fill Missing Categorical Values

We also have categorical columns such as:

  • Gender
  • Profession
  • City

For categorical data, we can use the most frequently occurring value, also called the mode.

For example:

df["Profession"] = df["Profession"].fillna(
    df["Profession"].mode()[0]
)

Here:

df["Profession"].mode()[0]

returns the most frequently occurring profession.

We can use the same approach for City:

df["City"] = df["City"].fillna(
    df["City"].mode()[0]
)

14. Fill Missing Age

Age is a numerical column.

We can use the median to fill the missing age:

df["Age"] = df["Age"].fillna(
    df["Age"].median()
)

Now the missing age has been replaced with the median age.

15. Handling Missing Values with dropna()

Another approach is dropna().

Instead of replacing missing values, dropna() removes rows containing missing values.

df_drop = df.dropna()

Let’s check the shape:

df_drop.shape

Because our original dataset contains five missing values, removing rows containing missing data will reduce the number of records.

This method can be useful when only a small number of records are incomplete and removing them will not negatively affect the analysis.

However, we should not automatically delete missing data.

16. Drop Rows Based on a Specific Column

We can also remove rows only when a particular column contains a missing value.

For example:

df_drop_income = df.dropna(subset=["Monthly_Income"])

This removes only the rows where Monthly_Income is missing.

This gives us more control than simply using dropna() on the entire DataFrame.

17. Handling Missing Values with interpolate()

The third technique is interpolate().

Interpolation estimates a missing numerical value using surrounding values.

For example:

df_interpolated = df.copy()

df_interpolated["Monthly_Saving"] = (
    df_interpolated["Monthly_Saving"].interpolate()
)

We can then check whether the missing value has been filled:

df_interpolated["Monthly_Saving"].isnull().sum()

Interpolation is mainly useful for numerical data.

We would not normally use it for columns such as:

Gender
Profession
City

because those are categorical values.

18. Comparing the Three Methods

We now have three different approaches.

fillna()

Replace missing values.

df["Age"] = df["Age"].fillna(df["Age"].median())

dropna()

Remove records containing missing values.

df_clean = df.dropna()

interpolate()

Estimate missing numerical values based on surrounding observations.

df["Monthly_Saving"] = df["Monthly_Saving"].interpolate()

The important point is that there is no universal method for missing values.

The correct approach depends on the dataset and the purpose of the analysis.

19. Create a Clean Copy of Our Dataset

Rather than modifying our original DataFrame while experimenting, it is better to create a copy.

df_clean = df.copy()

Now we can clean df_clean while keeping the original dataset available for comparison.

20. Clean Numerical Missing Values

For our numerical columns, we can use the median.

df_clean["Age"] = df_clean["Age"].fillna(
    df_clean["Age"].median()
)

df_clean["Monthly_Income"] = df_clean["Monthly_Income"].fillna(
    df_clean["Monthly_Income"].median()
)

For Monthly_Saving, we can demonstrate interpolation:

df_clean["Monthly_Saving"] = df_clean["Monthly_Saving"].interpolate()

21. Clean Categorical Missing Values

For categorical columns, we can use the mode.

df_clean["Profession"] = df_clean["Profession"].fillna(
    df_clean["Profession"].mode()[0]
)

df_clean["City"] = df_clean["City"].fillna(
    df_clean["City"].mode()[0]
)

22. Verify the Cleaned Dataset

Now let’s check the missing values again.

df_clean.isnull().sum()

And check the total:

df_clean.isnull().sum().sum()

The total should now be:

0

This means our cleaned dataset no longer contains missing values.

23. Save the Cleaned Dataset to Excel

Now we can save the cleaned DataFrame to a new Excel file.

df_clean.to_excel(
    "audience_analysis_cleaned.xlsx",
    index=False
)

The index=False parameter prevents Pandas from creating an additional index column in the Excel file.

24. Save With a Different Sheet Name

We can also specify the Excel sheet name.

df_clean.to_excel(
    "audience_analysis_cleaned.xlsx",
    sheet_name="Cleaned_Audience_Data",
    index=False
)

Now the Excel workbook will contain a sheet named:

Cleaned_Audience_Data

25. Final Code

Here is the complete practical code from this chapter:

import pandas as pd

# Read Excel file
df = pd.read_excel("audience_analysis_clustering_dataset.xlsx")

# Basic EDA
print(df.head())
print(df.tail())
print(df.shape)
df.info()

# Missing values
print(df.isnull())
print(df.isnull().sum())
print(df.isnull().sum().sum())

# Display rows containing missing values
print(df[df.isnull().any(axis=1)])

# Create clean copy
df_clean = df.copy()

# Numerical columns
df_clean["Age"] = df_clean["Age"].fillna(
    df_clean["Age"].median()
)

df_clean["Monthly_Income"] = df_clean["Monthly_Income"].fillna(
    df_clean["Monthly_Income"].median()
)

# Interpolation
df_clean["Monthly_Saving"] = df_clean["Monthly_Saving"].interpolate()

# Categorical columns
df_clean["Profession"] = df_clean["Profession"].fillna(
    df_clean["Profession"].mode()[0]
)

df_clean["City"] = df_clean["City"].fillna(
    df_clean["City"].mode()[0]
)

# Check missing values after cleaning
print(df_clean.isnull().sum())
print(df_clean.isnull().sum().sum())

# Save cleaned dataset
df_clean.to_excel(
    "audience_analysis_cleaned.xlsx",
    sheet_name="Cleaned_Audience_Data",
    index=False
)

Conclusion

In this chapter, we started our Audience Analysis project using Python Pandas.

We loaded the Excel dataset using pd.read_excel(), performed basic EDA using head(), tail(), shape, and info(), and identified missing values using isnull().

We then explored three different approaches to missing data: fillna(), dropna(), and interpolate().

Finally, we created a cleaned DataFrame and exported it back to Excel using to_excel().

The important lesson is not simply remembering these functions. It is understanding which missing-value technique makes sense for the type of data we are working with.

Our cleaned audience dataset is now ready for the next stage of the Audience Analysis project.