Data Importing and Validation The original dataset has 15000 rows and 8 columns. The week column contains integer values from 1-6 corresponding to each week and 0 null values. No changes to this column were necessary. The sales_method column contains 5 different unique values (Email, Call, Email + Call, em + call, email) and 0 null values. The column should only contain 3 unique values corresponding to each type of sales method. Upon inspection of the values in this column simple mistakes such as different capitalization and use of abbrevations created two extra sets of values. Changes were made and after cleaning the column only contains the following 3 different value types corresponding to each alternate sales method: Email, Call, Email + Call The customer_id column contains unique identifiers for each customer and had no missing values. No changes to this column were required. The revenue column had 1074 missing values. I decided that the best approach to handle these missing values would be to replace them by the mean revenue corresponding to each type of sales_method. The years_as_customer column has 0 missing values, however we had 2 rows with invalid values. Since our company was founded in 1984, the maximum amount of years we could have any given customer is 39. Two rows had values with 47 and 63, which we know is not possible. I assume this was just a mistake in data entry, and I will replace these two values with 39. The nb_site_visits column has 0 missing values and all data is valid. No changes needed to be made to this column The state The column has 0 missing values and 50 unique values corresponding to each state. No changes need to be made to this column Finally after all the data validation and cleaning I checked for any duplicate rows and found none. After cleaning and validating the data our dataset contains 15000 rows and 8 columns with 0 missing values.
# library imports
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
plt.style.use('fivethirtyeight')
%matplotlib inline# import data
sales_data = pd.read_csv('product_sales.csv')
sales_data.head()sales_data.shape
sales_data.info()
sales_data.describe()
# Check the value counts to ensure there are only 3 unique values
print(sales_data['sales_method'].value_counts())# Define a mapping dictionary to correct the inconsistent values
sales_method_mapping = {
'Email': 'Email',
'Call': 'Call',
'Email + Call': 'Email + Call',
'em + call': 'Email + Call',
'email': 'Email'
}
# Apply the mapping to the 'sales_method' column
sales_data['sales_method'] = sales_data['sales_method'].map(sales_method_mapping)
# Check the value counts to ensure there are only 3 unique values
print(sales_data['sales_method'].value_counts())# find mean revenue for each sales method
mean_revenue_by_sales_method = sales_data.groupby('sales_method')['revenue'].mean()
print(mean_revenue_by_sales_method)def replace_null_revenue(row):
"""
Replaces null (NaN) values in the 'revenue' column of a pandas DataFrame with the mean (or median) revenue
for the corresponding 'sales_method' group.
Parameters:
-----------
row : pandas Series
A single row of a pandas DataFrame containing the 'revenue' and 'sales_method' columns.
Returns:
--------
float
The value of the 'revenue' column for the given row, either the original value if it is not null, or
the mean (or median) revenue for the corresponding 'sales_method' group if it is null.
"""
if pd.isnull(row['revenue']):
return mean_revenue_by_sales_method[row['sales_method']]
else:
return row['revenue']
# apply function to the revenue column
sales_data['revenue'] = sales_data.apply(replace_null_revenue, axis=1)
# check for any null values in the revenue column
print(sales_data['revenue'].isnull().sum())sales_data[sales_data['years_as_customer'] > 39]
# find all values > 39 and replace with 39
sales_data.loc[sales_data['years_as_customer'] > 39, 'years_as_customer'] = 39# check to see if replacement worked
sales_data[sales_data['years_as_customer'] > 39]