<jupyter_start><jupyter_text>Shopify Data Science Internship ChallengeFor a summary of the answers for question 1, scroll down to the bottom. In this notebook contains exploratory data analysis of sneaker sales from Shopify sneaker shops, as well as SQL queries on orders of food items.   Question 1: Who is buying these expensive shoes?First we will do some exploratory data analysis using the database of Shopify sneaker stores to see how the average order value of $3145.13 was calculated and why it was "naively" done.<jupyter_code># importing libraries #

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
# importing data #

df = pd.read_csv('data.csv')
# checking to see if data was imported properly #

df.head()
# checking for null values #

null_value_bool = df.isnull().values.any()
print("Are there any null values?", null_value_bool)
# checking aggregate values of order_amount and total_items #

agg_list = ["min", "max", "median", "mean", "std"]

df.agg({
    "order_amount" : agg_list,
    "total_items" : agg_list,
    "shop_id" : ["max"]
})<jupyter_output><empty_output><jupyter_text>After loading in the database, a huge disparity was visible between the median and mean of the order amounts... and the standard deviation was ~1300% which is quite high and something that would personally raise red flags. Due to this, the cause of the high standard deviation was investigated.<jupyter_code># checking average sneaker costs #

sneaker_cost_avg = df['order_amount'].sum() / df['total_items'].sum()
print("The average cost of sneakers is:", round(sneaker_cost_avg, 2))<jupyter_output>The average cost of sneakers is: 357.92<jupyter_text>The average sneaker cost was 357.92 which seems a lot more reasonable (although I have never personally purchased sneakers 100+, it is possible to be this cost as I have witnessed quite expensive ones in store). We explored further to see the large order amounts and numbers.<jupyter_code># checking orders above 1000 order_amount #

bool_mask = (df['order_amount'] > 1000)
df_1000 = df[bool_mask]

df_1000
# investigating the high order_amount stores #

expensive_store_array = df_1000['shop_id'].unique()

expensive_store_array
# investigating store 42 #

bool_mask_42 = df_1000['shop_id'] == 42

df_42 = df_1000[bool_mask_42]

df_42.head(10)<jupyter_output><empty_output><jupyter_text>We can see from store 42 that there are repeated order amounts of 704,000 with 2000 total items. This seems like a remarkably large number and is most likely what is causing the high standard deviation. If we look at the other orders, 1056 with 3 items, 1408 with 4, and 1760 with 5, we can see that the sneaker cost is 352 at this store. This also lines up with 704,000 at 2000. However, we see 0 purchases of 2 items so it is possible that there is a reporting error and that whenever a customer purchases 2 of these sneakers at an order amount of 704, it reports 1000x the value. In reality, contacting the store to check if the orders are legitimate would be my first course of action but for the sake of this exercise, since there is no public contact information, we "correct" the values.<jupyter_code># investigating store 78 #

bool_mask_78 = df_1000['shop_id'] == 78

df_78 = df_1000[bool_mask_78]

df_78.head(10)<jupyter_output><empty_output><jupyter_text>Store 78 has extremely large sneaker values which seem unreasonable to me. According to a light google search, sneaker values can approach 10,000+, however most are in limited quantities and are most likely released online and thus would not be purchasable using cash which seems to be a common method of purchase of these sneakers. Thus, since we can not contact the store, we will remove all records from this store for the calculated metrics.<jupyter_code># investigating store 43 #

bool_mask_43 = df_1000['shop_id'] == 43

df_43 = df_1000[bool_mask_43]

df_43.head(10)
# investigating store 54 #

bool_mask_54 = df_1000['shop_id'] == 54

df_54 = df_1000[bool_mask_54]

df_54.head(10)<jupyter_output><empty_output><jupyter_text>Stores 43 and 54 seem reasonable in price and will not be altered.Now we remove store 78 from the records, and alter the values of store 42.<jupyter_code># removing store 78 #

bool_mask_78_new = df['shop_id'] != 78

df = df[bool_mask_78_new]
# altering store 42 values #

df.loc[df['order_amount'] == 704000] = 704
df.loc[df['total_items'] == 2000] = 2<jupyter_output><empty_output><jupyter_text>Now we've fixed all the values for the database and can see the new and improved metrics.<jupyter_code># checking metrics on revised database #

agg_list = ["min", "max", "median", "mean", "std"]

df.agg({
    "order_amount" : agg_list,
    "total_items" : agg_list,
})<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07693924742484759, 'fraction_numerical': 0.04687828463317217, 'mean_word_length': 5.244094488188976, 'pattern_counts': {'":': 0, '<': 28, '<?xml version=': 0, '>': 29, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '203810', 'license': 'MIT', 'n_tokens_mistral': 1591, 'n_tokens_neox': 1377, 'n_words': 611, 'path': 'Shopify.ipynb', 'repo_name': 'psohn/2022-Shopify-Internship-Challenge', 'size': '4757'}