<jupyter_start><jupyter_text>Price Analysis **TODO:** we have 3 analysis to do to include in the final presentation, these are:* Sales range analysis, we need to know how the prices to reailers are behaving, something like if the discounts that `SnaCo` is making to some clients are worth it or not.* Trends of products in time per client or per distributor, this will tell us a strategy to offert this kind of products to some tipe of clients, like for example a regression to see the slope and if we have negative slope we can implement a promotion for this product.* Devolutions problem, how can we analyze this? what clients are returning products and how this represents a problem for the company? how we can present this?* The problem with offices seling product not in their local warehouse, how costs of shipping could impact the revenue of the company?<jupyter_code>import datetime
import random
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import numpy as np

random.seed(42)
dateparse = lambda x: pd.datetime.strptime(x, '%d/%m/%Y')

snacks_df = pd.read_csv('./datasets/clean_snacks.csv', dtype={ 'client_id': str }, parse_dates=['date'], date_parser=dateparse)
snacks_df['product_id'] = snacks_df['product_id'].astype(str)
snacks_df.head(1)<jupyter_output><empty_output><jupyter_text>We want to count the products to leave only those product that have a good amount of records, select some of those at random and start making the prices analysis for those.<jupyter_code># We do not care on devolution records so we are going to analyze units > 0
snacks_df = snacks_df[snacks_df['units'] > 0]

product_grouping = (
    snacks_df.groupby('product_id')['units']
        .count()
        .reset_index()
        .rename(columns={'units': 'count'})
        .sort_values('count', ascending=False)
)

# we are going to select only products with more than 10k records, we don't know the exact number to consider as
# a good starting point, but we are going to try 10k and see what happens and then adjust this.

products = product_grouping[product_grouping['count'] > 10000]['product_id']
f"Now we have {len(products)} products"<jupyter_output><empty_output><jupyter_text>Now let's select 6 random products from that list and analyze what is happening with prices for those products<jupyter_code>products_sample = products.sample(10)

products_df = snacks_df[snacks_df['product_id'].isin(products_sample)]
f"Now we have {len(products_df)} records"<jupyter_output><empty_output><jupyter_text>Let's calculate the unit price for each sale and then view the distribution of those prices to have a general idea on how this prices behave.<jupyter_code>products_df['unit_price'] = products_df['sale_amount'] / products_df['units']
plt.figure(figsize=(16, 10))

for index, product in enumerate(products_df['product_id'].unique()):
    plt.subplot(5, 5, index + 1)
    sns.distplot(products_df[products_df['product_id'] == product]['unit_price']).set_title(product)
plt.tight_layout()<jupyter_output><empty_output><jupyter_text>We can see a very interesting pattern in here, it seems that all products handle two types of prices, it's like all price units are bimodal.* why is this happening? maybe it's something related to the year? or by distributor or client?* why there is such a huge difference between expectations, it seems like some products are almost 0, but others are selled almost by 1000 pesos? Let's divide by year to see the distribution of the products<jupyter_code>products_df['month'] = products_df['date'].dt.month
products_df['year'] = products_df['date'].dt.year
products_df['yyyymm'] = products_df['date'].dt.to_period('M')
plt.figure(figsize=(16, 5))
sns.boxplot(data=products_df, x='product_id', y='unit_price', hue='year')<jupyter_output><empty_output><jupyter_text>We found something interesting in here, the prices of some products, it seems that the prece haven't change at all in some products between different year, this is strange we expect that year by year the prices increments and we are not seeing that here.<jupyter_code>plt.figure(figsize=(16, 5))
sns.boxplot(data=products_df, x='product_id', y='unit_price', hue='distributor')<jupyter_output><empty_output><jupyter_text>Now we can see that some distributors appear to be receiving different prices, why is that happening? how can we digg a little bit more into that? also we are seeing that prices still present a big variance, why is that?<jupyter_code>plt.figure(figsize=(16, 5))
sns.boxplot(data=products_df, x='product_id', y='unit_price', hue='client')<jupyter_output><empty_output><jupyter_text>This is a very interesting chart, this shows that `SnaCo` have a prefference to sell products with bit discount to some clients, why this is happening? it is really worth it for the company to give discounts to those clients?* The product 52407107 have a very stable price range in all distributors, if we compare in the distribution by year we can see this is one of the products that year by year is incrementing it's price, there are some outliers but overall it presents a very understandable behavior.* product 53407103 have a lot of special prices for `Supertiendas Y Droguerias Olimpica S.A.` but in overall it's presenting a more higher mean on the prices, maybe that is showing that the the strategy of promotions it's working with olimpica, we can validate this against the volume of orders in terms of other clients.* `Cencosud` is a client that is presenting a common price across all products, we can validate this checking the amount of sales amount that cencosud is presenting against other clients. We need to check also if the price strategy is also related with the different offices, maybe some offices handles different prices for products?<jupyter_code>plt.figure(figsize=(16, 5))
sns.boxplot(data=products_df, x='product_id', y='unit_price', hue='office')<jupyter_output><empty_output><jupyter_text>Generally speaking it seems that prices teng to stay the same in every office, it's more relevant the changes that we see in clients, we identify that there are probably policies to prices for some of the clients. If we plot the clients and distributors for how many sales and how many amount of money the sales are producing we can compare it to the price strategy in order to see if the strategy is working.We can select two particular products that presents different policies between clients and see how the sales behaves.<jupyter_code>plt.figure(figsize=(16, 5))

plt.subplot(1, 2, 1)
chart = sns.barplot(x='client', y='units', estimator=sum, data=products_df)
chart.set_xticklabels(chart.get_xticklabels(), rotation=90)
chart.set_title('Units sold')

plt.subplot(1, 2, 2)
chart = sns.barplot(x='client', y='sale_amount', estimator=sum, data=products_df)
chart.set_xticklabels(chart.get_xticklabels(), rotation=90)
chart.set_title('Sales')

plt.show()<jupyter_output><empty_output><jupyter_text>We can see multiple interesting things in here, in the plot of prices distributions in general terms, we can see that `Supertiendas y Droguerias Olimpica S.A.` is buying more products, but the client `<NAME> S.A.` is making more money for the `SnaCo` company, this can be evidence of the prices policy adapted by the company to sell to olimpica is not working, but we cannot conclude this yet, we need to see the sale for each product and see if olimpica si buying similar amounts than exito in order to concluse this.<jupyter_code>plt.figure(figsize=(16, 40))

products = products_df['product_id'].unique()
clients = products_df['client'].unique()

for index, product in enumerate(products):
    client_indexes = list(range(len(clients)))
    data = { 'client': client_indexes, 'units': [], 'sale_amount': [] }
    for client in clients:
        df = products_df[(products_df['client'] == client) & (products_df['product_id'] == product)]
        data['units'].append(df['units'].sum())
        data['sale_amount'].append(df['sale_amount'].sum())
    
    data = pd.DataFrame.from_dict(data)
    plt.subplot(len(products), 2, index * 2 + 1)
    sns.barplot(x='client', y='units', data=data).set_title(f"Product {product}")
    
    plt.subplot(len(products), 2, index * 2 + 2)
    chart = sns.barplot(x='client', y='sale_amount', data=data).set_title(f"Product {product}")

plt.tight_layout()
for index, client in enumerate(clients):
    print(f"Client {client} is index: {index}")<jupyter_output>Client Supertiendas Y Droguerias Olimpica S.A. is index: 0
Client nan is index: 1
Client Almacenes Exito S.A. is index: 2
Client Almacenes La 14 S.A. is index: 3
Client Cencosud Colombia S.A. is index: 4
Client Makro Supermayorista S.A.S is index: 5
Client Colombiana De Comercio S.A. is index: 6
Client Caja Colombiana De Subsidio Familiar Colsubsidio is index: 7<jupyter_text>Focusing on one interesting product that is the `5348106` let's dig in a little bit into the distribution.<jupyter_code>product_id = '53408106'

product_df = products_df[products_df['product_id'] == product_id]
plt.figure(figsize=(10, 4))

client_indexes = list(range(len(clients)))
data = { 'client': client_indexes, 'units': [], 'sale_amount': [] }
for client in clients:
    df = product_df[product_df['client'] == client]
    data['units'].append(df['units'].sum())
    data['sale_amount'].append(df['sale_amount'].sum())

data = pd.DataFrame.from_dict(data)
plt.subplot(1, 2, 1)
sns.barplot(x='client', y='units', data=data).set_title('Sold units')

plt.subplot(1, 2, 2)
chart = sns.barplot(x='client', y='sale_amount', data=data).set_title('Sales revenue')

plt.tight_layout()
clients_map = {}
for index, client in enumerate(clients):
    clients_map[client] = index

product_df['client_index'] = product_df['client'].map(lambda x: clients_map[x])
client_indexes = list(range(len(clients)))
sns.boxplot(x='client_index', y='unit_price', data=product_df)
plt.figure(figsize=(16, 5))
sns.distplot(product_df['unit_price']).set_title('general distribution')

clients = product_df['client'].unique()
plt.figure(figsize=(16, 10))
for index, client in enumerate(clients):
    plt.subplot(4, 2, index + 1)
    sns.distplot(product_df[product_df['client'] == client]['unit_price']).set_title(client)
plt.tight_layout()<jupyter_output>/Users/juanprq/anaconda3/envs/week8/lib/python3.7/site-packages/seaborn/distributions.py:198: RuntimeWarning: Mean of empty slice.
  line, = ax.plot(a.mean(), 0)
/Users/juanprq/.local/lib/python3.7/site-packages/numpy/core/_methods.py:85: RuntimeWarning: invalid value encountered in double_scalars
  ret = ret.dtype.type(ret / rcount)
/Users/juanprq/.local/lib/python3.7/site-packages/numpy/lib/histograms.py:789: RuntimeWarning: invalid value encountered in true_divide
  return n/db/n.sum(), bin_edges
/Users/juanprq/.local/lib/python3.7/site-packages/numpy/core/_methods.py:140: RuntimeWarning: Degrees of freedom <= 0 for slice
  keepdims=keepdims)
/Users/juanprq/.local/lib/python3.7/site-packages/numpy/core/_methods.py:132: RuntimeWarning: invalid value encountered in double_scalars
  ret = ret.dtype.type(ret / rcount)<jupyter_text>How is possible that there are some sales that the unit price is less than 1/4 of the price of other clients? this doesn't make any sense.Let's define a limit for the product, let's examine sales that are under 500 of price and see what we can discover in here.<jupyter_code>limit = 500

small_df = product_df[product_df['unit_price'] < 500]
sns.distplot(small_df['unit_price'])<jupyter_output><empty_output><jupyter_text>How is even possible to have such a low values of prices? at what dates are this happening? and what amount of sales this represents?<jupyter_code>plt.figure(figsize=(16, 5))

plt.subplot(1, 2, 1)
sns.barplot(x='year', y='units', data=small_df.groupby('year')['units'].sum().reset_index())

plt.subplot(1, 2, 2)
sns.barplot(x='year', y='sale_amount', data=small_df.groupby('year')['sale_amount'].sum().reset_index())<jupyter_output><empty_output><jupyter_text>How is even possible to sale 2500 units in 2014 for 40.000$ pesos?<jupyter_code>plt.figure(figsize=(16, 5))

chart = sns.barplot(x='client', y='units', hue='year', data=small_df.groupby(['client', 'year'])['units'].sum().reset_index())
chart.set_xticklabels(chart.get_xticklabels(), rotation=90)
chart.set_title('Clients buying the product very cheap')

plt.show()<jupyter_output><empty_output><jupyter_text>As the evidence of the prices distribution suggest the client `<NAME> S.A.` is the client that is getting the better prices, also `Almacenes La 14 S.A.` (at least for this product). Let's see how much is missing the `SnaCo` company for selling at this prices to `Almacenes la 14 S.A.` and `Supsertiendas y Droguerías Olimpica S.A`.<jupyter_code>mean_price = product_df['unit_price'].mean()

original_sale_amount = small_df['sale_amount'].sum()
predicted_sale_amount = small_df['units'].sum() * mean_price

df = pd.DataFrame({'type': ['Original', 'Predicted'], 'sale_amount': [original_sale_amount, predicted_sale_amount]})

sns.barplot(x='type', y='sale_amount', data=df)
original_sale_amount, predicted_sale_amount<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10996799268404207, 'fraction_numerical': 0.014860539551897577, 'mean_word_length': 5.925065963060686, 'pattern_counts': {'":': 0, '<': 63, '<?xml version=': 0, '>': 64, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '54176', 'license': 'MIT', 'n_tokens_mistral': 4257, 'n_tokens_neox': 4017, 'n_words': 1398, 'path': 'price-analysis.ipynb', 'repo_name': 'andressalazar08/afsr7', 'size': '13159'}