<jupyter_start><jupyter_text>OverviewThis dataset was obtained from  https://data.world/datafiniti/mens-shoe-pricesFind interesting information in this dataset from a marketing point of view.Marketing physical products (like shoes) requires us to find:  - Price per unit  - Number of units soldThese values are then used to decide price elasticity, and from that set our company's pricing policy.This dataset has prices found for particular shoes on internet websites. It does not include the number of units sold, but can give us an idea of the price points of various competitors selling the same shoe.<jupyter_code>from decouple import config
import os
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
dataset_path = os.path.join(config('DATASETS'),
                            'mens_shoe_prices.csv')
df = pd.read_csv(dataset_path)
df.head()
# The mixed-types warning tells us we have work to do if we
# use those columns. Good we don't need them so far.<jupyter_output>/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/IPython/core/interactiveshell.py:3145: DtypeWarning: Columns (39,45) have mixed types.Specify dtype option on import or set low_memory=False.
  has_raised = await self.run_ast_nodes(code_ast.body, cell_name,<jupyter_text>The mixed-type warning tells us we have work to do if we use those columns. Good we don't need them so far.  Understand the dataframe columnsThis involves finding out:  - What data is available  - How much data in each column is missing, and what should we do about it  - Do we want to fill in missing data for any column  - Do we need to change column dtypeAll of this is done with an eye towards getting information out of the dataframe. If we don't need to use a column, there is no need to fix missing values for it.<jupyter_code>df.columns<jupyter_output><empty_output><jupyter_text>Find the columns with 'price' in it's name:<jupyter_code>list(filter(lambda x: 'price' in x, df.columns))
# This gives us a list of price columns we could use.<jupyter_output><empty_output><jupyter_text>Do a quick overview of the columns in the dataframe<jupyter_code>def percent_na(df, colname):
    return df[colname].isna().sum() / df.shape[0]

for colname in df.columns:
    print(f'NA values in {colname:<20}: {percent_na(df, colname): .2}')<jupyter_output>NA values in id                  :  0.0
NA values in asins               :  0.87
NA values in brand               :  0.013
NA values in categories          :  0.0
NA values in colors              :  0.43
NA values in count               :  1.0
NA values in dateadded           :  0.0
NA values in dateupdated         :  0.0
NA values in descriptions        :  0.49
NA values in dimension           :  0.85
NA values in ean                 :  0.48
NA values in features            :  0.28
NA values in flavors             :  1.0
NA values in imageurls           :  0.054
NA values in isbn                :  1.0
NA values in keys                :  0.0
NA values in manufacturer        :  0.65
NA values in manufacturernumber  :  0.22
NA values in merchants           :  0.28
NA values in name                :  0.0
NA values in prices_amountmin    :  0.00052
NA values in prices_amountmax    :  0.0
NA values in prices_availability :  0.99
NA values in prices_color        :  0.96
NA values in prices_c[...]<jupyter_text>We will ignore columns whose fraction of null values is high.It looks like most of these columns are not needed anyway.<jupyter_code>df.merchants.unique()[:100]<jupyter_output><empty_output><jupyter_text>The merchants column looks promising. Maybe we can compare shoe prices between various merchants.<jupyter_code>merchant_names = pd.Series(df.merchants.unique()).str.extract(r'name\"\:\"(.*?)\"\}').loc[:,0].unique()
merchant_names = pd.Series(merchant_names).dropna()
ebay_merchant_names = merchant_names[merchant_names.str.contains('Ebay\.com')]
len(ebay_merchant_names), len(merchant_names)<jupyter_output><empty_output><jupyter_text>There are 1237 ebay merchants, out of a total of 1566 unique merchants.Are there any amazon merchants in the data?<jupyter_code>amazon_merchant_names = merchant_names[merchant_names.str.contains('amazon', case=False)]
amazon_merchant_names<jupyter_output><empty_output><jupyter_text>Let's compare how many item types are being sold by Amazon vs. EBay<jupyter_code>print(f'Number of item types sold by Amazon: {df.merchants.str.contains("amazon", case=False).sum()}')
print(f'Number of item types sold by Ebay: {df.merchants.str.contains("ebay", case=False).sum()}')
for x, y in list(zip(df.merchants.values, df.prices_merchant.values))[:100]:
    print(x, y)
len(df.prices_merchant.values)<jupyter_output><empty_output><jupyter_text>So almost 10x more items were on ebay compared with amazon<jupyter_code>df['prices_merchant'].value_counts(normalize=True, dropna=False)
# These are the names of a list of online merchants.
# We don't need this column.
print(f'min and max: {df[["dateadded"]].min()}, {df[["dateadded"]].max()}')
print(f'min and max: {df[["dateupdated"]].min()}, {df[["dateupdated"]].max()}')
# Looks like the range is not huge - the dateadded has a 3-yr range.
# So, we can ignore inflation.
shoe_brands = df.features.str.extract(r'Brand\"\,\"value\"\:\[\"(.*?)\"\]')
print(f'Number of unique shoe brands: {shoe_brands.loc[:,0].nunique()}')
shoe_brands.loc[:,0].unique()
# Although merchants are missing 28% of the values, we still want to see
# what their price ranges are, so we select them as well.
shoes = df[['name', 'categories', 'prices_amountmin', 'prices_amountmax', 'prices_currency', 'features', 'merchants']]

# This is necessary if we're applying a function to the dataframe's rows.
# Inside the function, we pass in a row.
# If we try to access the row.name value, it returns the index value
# of the row, rather than the name column's value.
# So don't use name as the name of a column. In this case, we're using 'description'.
shoes = shoes.rename(columns = {'name': 'description'})

shoes.merchants = shoes.merchants.str.extract(r'name\"\:\"(.*?)\"\}')

shoes['brands'] = shoes.features.str.extract(r'Brand\"\,\"value\"\:\[\"(.*?)\"\]').loc[:, 0].str.lower()
print(f'Number of unique shoe brands: {shoes.brands.nunique()}') # Looks like we lost some brand names
                                                                 # after we lowercased the names.
                                                                 # Some brands must have had uppercase and lowercase
                                                                 # names.

shoes.head()
shoes.shape
shoes.prices_currency.value_counts(dropna=False, normalize=True)
# 95% of the currency is USD. Let's stick with it
shoes = shoes[shoes.prices_currency == 'USD']
shoes.shape
# Now that we have a dataframe with only USD currency,
# we can remove the prices_currency column
shoes = shoes.drop('prices_currency', axis=1)
shoes.head()
shoes[['description']] # Take a look at how descriptive the name is.
# The 2 through 4 index shoes are not shoes - they're shoe studs.
shoes.loc[19384] # This cannot be a shoe (from it's description)
# It looks like it is sunglasses.
# So this dataset contains things other than shoes.
# We will have to find out how to extract shoes from it.
# Let's see how many of the names have "shoes" or related in them
shoe_or_boot = shoes.description.str.contains("shoe", case=False) | \
                shoes.description.str.contains("boot", case=False) | \
                shoes.description.str.contains("loafer", case=False) | \
                shoes.description.str.contains("sneaker", case=False) | \
                shoes.description.str.contains("trainer", case=False) | \
                shoes.description.str.contains("step", case=False) | \
                shoes.description.str.contains("oxford", case=False) | \
                shoes.description.str.contains("canvas", case=False)

                # We should not consider slippers, sandals, etc as shoes.
                # If we want, we could look at them as well,
                # to see the price ranges of each type of footwear.
                # shoes.description.str.contains("sandal", case=False) | \
                # shoes.description.str.contains("slipper", case=False) | \
                # shoes.description.str.contains("croc", case=False)

print(f'Fraction of names containing shoes: {len(shoes[shoe_or_boot]) / shoes.shape[0]}')
# Half of the dataset items do not have anything to do with shoes
# Let's see what those other items are
other_than_shoes = shoes[~shoe_or_boot][['description']].squeeze().values
other_than_shoes[100:200]
# Compare shoe names to categories
cat_other_than_shoes = shoes[~shoe_or_boot][['categories']].squeeze().values
other_than_shoes_names_cat = list(zip(other_than_shoes, cat_other_than_shoes))
other_than_shoes_names_cat[:100]
# This tells us that we cannot use categories to extract shoes from this dataset.
# So our use of name to extract shoes is valid.
# In fact, categories does not give us anything, so remove it to save
# processing time/memory.
shoes = shoes.drop('categories', axis='columns')
shoes.head()
print(f'Initial shoes shape: {shoes.shape}')
shoes = shoes.dropna(axis='index', how='any') # Drop any row that has NA values in it
print(f'Final shoes shape: {shoes.shape}')
# There are no nulls in our data
shoes.dtypes
# Convert prices to floats
shoes.prices_amountmin = shoes.prices_amountmin.astype('float64')
shoes.prices_amountmax = shoes.prices_amountmax.astype('float64')
shoes.dtypes
diff_amounts = shoes[shoes.prices_amountmin != shoes.prices_amountmax]
diff_amounts.shape[0]
# Only a very small number of columns have differing minimum and maximum amounts
shoes2 = pd.DataFrame({'price_diff': (shoes.prices_amountmax - shoes.prices_amountmin),
                       'amountmin': shoes.prices_amountmin,
                       'amountmax': shoes.prices_amountmax})
shoes2.sort_values('price_diff', ascending=False).head()
shoes2.price_diff.hist(bins=100);
print(f'% of price differences that are zero: {len(shoes2[shoes2.price_diff < 0.01]) / len(shoes2)}')
shoes2[(shoes2.price_diff > 0.01) & \
       (shoes2.price_diff < 80)].price_diff.hist();
# Huh! Seems to be some kind of pattern.
# This is the pattern I see going upto $80 with default number of bins.
#   0-8:   Low
#   9-16:  High
#   17-24: Much lower
# The $8 per column makes since since the default number of bins = 10,
# and we're looking at values from 0 - 80.
# It also makes sense that the histogram should decrease to the right,
# since we don't have as many higher values.
# The pattern does change as you go from 0 - 100,
# and change the number of bins.
# So maybe there is nothing here.
# Our minds try to notice patterns all the time.
# Remove the shoes2 dataframe.
del shoes2
df.features.loc[0]
shoes['categories'] = shoes.features.str.extract(r'Shoe Category\"\,\"value\"\:\[\"(.*?)\"\]').squeeze()
shoes.categories.value_counts(normalize=True, ascending=False)
cat_list = pd.Series(shoes.categories.unique())
cat_list = cat_list.str.replace('\'', '')
print(f'% NaN in shoes categories: {shoes.categories.isnull().sum() / len(shoes)}')
# Since there are so many NaN values,
# maybe we can use the categories feature instead.
df.categories.value_counts(normalize=True, ascending=False)
# The category names combine clothing and shoes etc.
# It doesn't look like a good way to judge what the item is.
# We will stick with our categories based on shoe category
# from the features column.
# Let's check what items have a feature with NaN
# as the shoe category. Maybe they're not shoes.
shoe_categories_nan_names = shoes[shoes.categories.isnull()].description.unique()
shoe_categories_nan_names
# Looks like we missed some shoes that do not have their
# categories labeled in the features column.
# If the category is not labeled, we can check the name
# for the type and create a category using the same type:
# Let's consider the types based on the values in our mapping table below.

# Since we're only considering mens shoes, ignore the rest
ignore_categories = ['girl', 'no category', 'pumps', 'baby', 
                     'youth', 'unisex', 'children']
shoes_not_null = shoes[shoes.categories.notnull()]
ignore_idx = []
for cat in ignore_categories:
    ignore_idx.extend(shoes_not_null[shoes_not_null.categories.str.contains(cat, case=False)].index)
print(f'ignore_idx: {ignore_idx}')

shoes_required = shoes.drop(ignore_idx)  # Only shoes with categories we want
mapping = {   # Unusual shoe types are at the top since a shoe name may contain 'cleated shoe',
              # which matches both cleated and shoe. But we want to use the more precise label,
              # and we will search for those precise labels first.
              # Once it is found, and replaced, we don't compare any further.
            'cleated': 'cleated', 
            'spiked': 'cleated',
            'running': 'running',
            'athletic': 'athletic',
            'training': 'trainer',
            'trainer': 'trainer',
            'step': 'step',
            'skate': 'skate',
            'waterproof boot': 'waterproof boot',
            'skateboarding': 'skateboarding',

            'boat': 'boat',
            'water': 'boat',
            'winter and rain boot': 'winter and rain boot',
            'basketball': 'basketball',
            'oxford': 'oxford',
            'dress': 'dress',
            'fashion': 'dress',
            'dress boot': 'dress boot',
            'leather boot': 'leather boot',
            'work boot': 'work boot',

            'boot': 'boot',
            'casual': 'casual',
            'lifestyle': 'casual',
            'loafer': 'casual',
            'slip-on': 'casual',
            'hiking': 'hiking',
            'slipper': 'slipper',
            'sliipper': 'slipper',
            'flat': 'slipper',
            'flip flop': 'slipper',
            'sandal': 'sandal',
            'sneaker': 'sneaker',
            'shoe': 'shoe',
         }

# We don't need the features column any more
shoes_required = shoes_required.drop('features', axis=1)

shoes_required
mapping_keys = mapping.keys()
mapping_values = mapping.values()

def update_categories(x):
    # Here x is a series of column values for a row.
    if pd.notna(x.categories):
        return x.categories
    
    for key in mapping_keys:
        if key in x.description.lower():
            return mapping[key]

shoes_required.categories = shoes_required.apply(update_categories, axis=1)
shoes_required
# Notice that the values of the updated dataframe above shows
# 'Servus By <NAME> Studs Zsr101blmlg' as the description
# of the item, but we update the categories column to shoe.
# That is because we find 'shoe' in the description of that item.
# This cannot be helped - we can do a better job by removing those
# values by hand. If we were working on this as real data for
# a real company, we would definitely have to do that.
# For now, we will ignore it
# The copy() is done to prevent a setting-from-copy warning.
# This warning means that a view could have been created on the 
# dataframe, and updates to it could update the view, and
# not the actual dataframe. So if you refer to the dataframe
# thinking it has been updated, you will be surprised.
# To prevent surprises, the copy() function will create a
# separate copy of the shoes_required dataframe.
shoes_required = shoes_required[shoes_required.categories.notna()].copy()
shoes_required.shape[0]
def update_values(x):
    for search_value in mapping_values:
        if search_value in x.categories:
            return search_value
    
shoes_required.categories = shoes_required.apply(update_values, axis=1)    
shoes_required.categories.unique()
shoes_required.groupby('categories').describe()
# There is a large difference between the min and max values for each category.
# Also, some max values are very high (2900 for boots, 2800 for oxfords),
# while some max values are low (124 for skating) shoes.
# We should use a log-log scatter plot colored by categories
# to allow us to see all data on one chart.
import seaborn as sns
fig = plt.figure(figsize=(15, 9))
sns.scatterplot(x='prices_amountmin', y='prices_amountmax',
                data=shoes_required, 
                hue='categories');
# The single diagonal line in the plot above is due to
# the prices_amountmin and prices_amountmax being the same.

# It's still very difficult to see the relationships between prices
# for each type of shoe. Let's try a stripplot.
fig, axes = plt.subplots(1, 2, figsize=(15, 11))
# fig.suptitle('Prices per shoe category')
axes[0].set_title('Minimum amount ranges per shoe category')
axes[1].set_title('Maximum amount ranges per shoe category')
sns.stripplot(ax=axes[0], x='prices_amountmin', y='categories', data=shoes_required);
sns.stripplot(ax=axes[1], x='prices_amountmax', y='categories', data=shoes_required);
# It's better, but we cannot compare the min/max prices.
# This is a little better, but we cannot compare the min/max
# prices for a category.
# Let's create a different dataframe where there is only one price column.
# An additional column (price_type) will tell us if this is a min/max price.
# This will allow us to show both min/max prices per category
# next to each other.
df2 = pd.DataFrame(data={'description': shoes_required.description,
                         'prices': shoes_required.prices_amountmin,
                         'categories': shoes_required.categories,
                         'brands': shoes_required.brands,
                         'price_type': ['min']*len(shoes_required)})
df3 = pd.DataFrame(data={'description': shoes_required.description,
                         'prices': shoes_required.prices_amountmax,
                         'categories': shoes_required.categories,
                         'brands': shoes_required.brands,
                         'price_type': ['max']*len(shoes_required)})
shoes_alt = pd.concat([df2, df3], axis=0, join='outer', ignore_index=True)
shoes_alt.tail()
fig = plt.figure(figsize=(14, 8))
fig.suptitle('Min/Max prices per category', 
             y=0.93,
             fontsize='xx-large')
sns.stripplot(data=shoes_alt, x='prices', y='categories', 
              jitter=True,
              hue='price_type', dodge=True, # dodge = True separates points for each hue type.
                                            # This is how we get two rows per price type.
              alpha=0.25);
# Looks like the min/max prices are close together for almost all of the items.
# There are some large prices in the boot and oxford categories.
# Also, leather boots and skates have short price ranges.
# Casual and boots have longer price ranges.
shoes.head()
shoes.brands.value_counts(normalize=True, ascending=False)
# Nike and Puma are leading in the number of items listed.
# Let's compare the price ranges of these two brands.
shoes.head()
selected_brands = shoes_alt[(shoes_alt.brands == 'nike') | (shoes_alt.brands == 'puma')]

fig, axes = plt.subplots(1, 2, figsize=(11, 8))

# Left hand side plot with points dimmed to show
# violin plot peaks
sns.stripplot(ax= axes[0], data=selected_brands,
              x='prices', y='brands',
              jitter=True,
              hue='price_type', dodge=True,
              alpha=0.01);
sns.violinplot(ax= axes[0], data=selected_brands,
              x='prices', y='brands',
              inner=None, color='0.8');
axes[0].axvline(80);
axes[0].axvline(160);

# Right hand side plot with all points shown
sns.stripplot(ax= axes[1], data=selected_brands,
              x='prices', y='brands',
              jitter=True,
              hue='price_type', dodge=True,
              alpha=0.2);
sns.violinplot(ax= axes[1], data=selected_brands,
              x='prices', y='brands',
              inner=None, color='0.8');

# You can see that although Puma has a similar number of items listed for sale,
# the Nike brand item value range is higher. The Nike distribution peak
# is around 80, while the Puma distribution peak is below 80.
# The violinplot ends for Puma around 160, while it goes on for
# some more time after that for Nike.
# This just means that the prices listed online for Nike are higher
# than those listed for Puma.
# Surprisingly, the lower price point for Puma is higher
# than the lower price point for Nike.
plt.figure(figsize=(11, 8))
sns.histplot(selected_brands, x='prices', hue='brands');
# Another way to see the same two Nike/Puma prices<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10602444844883846, 'fraction_numerical': 0.012419032776506113, 'mean_word_length': 3.3147720109266654, 'pattern_counts': {'":': 0, '<': 39, '<?xml version=': 0, '>': 37, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '337260', 'license': 'MIT', 'n_tokens_mistral': 6313, 'n_tokens_neox': 6061, 'n_words': 2160, 'path': 'marketing_shoes.ipynb', 'repo_name': 'samirgadkari/marketing_shoes', 'size': '20541'}