<jupyter_start><jupyter_text>Data Analysis 2 In this analysis we will find the three best selling products grouped by the number of days it takes to manufacture them First we connect to the cloud database, run an SQL query for the necessary data and store it in pandas dataframes.This analysis requires three tables: ``SalesOrderDetail``, ``SpecialOfferProduct`` and ``Product``. We could join the tables in a single SQL query, but for simplicity's sake let's merge them with pandas.<jupyter_code>import pandas.io.sql as sqlio
import psycopg2
from config import config

params = config()
conn = psycopg2.connect(**params)
sql1 = 'SELECT * FROM \"SalesOrderDetail\";'
sql2 = 'SELECT * FROM \"SpecialOfferProduct\";'
sql3 = 'SELECT * FROM \"Product\";'
salesOrderDetail = sqlio.read_sql_query(sql1, conn)
specialOfferProduct = sqlio.read_sql_query(sql2, conn)
product = sqlio.read_sql_query(sql3, conn)
conn.close()<jupyter_output><empty_output><jupyter_text>Let's inspect the dataframes.<jupyter_code>from IPython.display import display

display(salesOrderDetail.head(3))
display(specialOfferProduct.head(3))
display(product.head(3))<jupyter_output><empty_output><jupyter_text>Now let's merge them with pandas.<jupyter_code>import pandas as pd

df1 = pd.merge(salesOrderDetail, specialOfferProduct, how = "inner", on = ['ProductID', 'SpecialOfferID'], suffixes = ('_sod', '_sop'))
df1 = pd.merge(df1, product, how = "inner", on = 'ProductID', suffixes = ('', '_p'))<jupyter_output><empty_output><jupyter_text>Here is the resulting dataframe.<jupyter_code>display(df1.head(3))<jupyter_output><empty_output><jupyter_text>As we only need to know the products' name, how much of them were sold and how long they take to manufacture, we can drop most columns to make data visualization easier. At the same time, we'll create two dataframes, one storing the order quantity for each product and the other storing how long it takes to manufacture each product.<jupyter_code>dfOQ = df1[['Name', 'OrderQty']].groupby('Name').sum()
dfDTM = df1[['Name', 'DaysToManufacture']].groupby('Name').max()<jupyter_output><empty_output><jupyter_text>Now let's merge the two dataframes into one dataframe that stores how much each product was ordered and how long it takes to be manufactured, sorted by descending order of order quantity.<jupyter_code>df2 = pd.merge(
    dfOQ,
    dfDTM,
    how = "inner",
    on = 'Name',
    suffixes = ('_oq', '_dtm')
).sort_values('OrderQty', ascending = False)
df2['Name'] = df2.index
df2 = df2[['OrderQty', 'DaysToManufacture']].reset_index()<jupyter_output><empty_output><jupyter_text>Let's inspect the result.<jupyter_code>display(df2.head(3))<jupyter_output><empty_output><jupyter_text>Now we group by days to manufacture and store only the top three results for each time range.<jupyter_code>df3 = df2.groupby('DaysToManufacture').head(3).sort_values('DaysToManufacture').reset_index(drop = True)<jupyter_output><empty_output><jupyter_text>Let's see the resulting dataframe.<jupyter_code>display(df3)<jupyter_output><empty_output><jupyter_text>There we have it, the three best selling products by days to manufacture. Let's plot a graph to better visualize the relationship between time to manufacture and number of sales.<jupyter_code>import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

g = sns.lineplot(x = 'DaysToManufacture', y = 'OrderQty', data = df3)
g.set(title = 'Order Quantity by Days to Manufacture', xlabel = 'Days to Manufacture', ylabel = 'Order Quantity')
plt.show()<jupyter_output><empty_output><jupyter_text>Finally, let's save the resulting dataframe to a ``.csv`` file.<jupyter_code>df3.to_csv('results/analysis2.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11230810665230272, 'fraction_numerical': 0.008887691893347698, 'mean_word_length': 6.786163522012578, 'pattern_counts': {'":': 0, '<': 45, '<?xml version=': 0, '>': 45, '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': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '593352', 'license': 'Apache-2.0', 'n_tokens_mistral': 1224, 'n_tokens_neox': 1178, 'n_words': 359, 'path': 'project/Analysis2.ipynb', 'repo_name': 'WWMarin/Cloud-Data-Ingestion-and-Analysis', 'size': '3713'}