<jupyter_start><jupyter_text>The data frames `Customers`, `Employees`, `Offices`, `OrderDetails`, `Orders`, `Payments`, `ProductLines`, and `Products` contain data of the corresponding tables in the [ClassicModels database](https://www.richardtwatson.com/dm6e/Reader/ClassicModels.html).The entity relationship diagram is shown here ![ERD](figures/ClassicModels.png) Using Pandas merge and join operations, answer the following questions:*One to many relationship*- Report the account representative for each customer.- Report total payments for Atelier graphique.- Report the total payments by date- Report the products that have not been sold.- List the amount paid by each customer.- How many orders have been placed by Herkku Gifts?- Who are the employees in Boston?- Report those payments greater than \\$100,000. Sort the report so the customer who made the highest payment appears first.- List the value of 'On Hold' orders.- Report the number of orders 'On Hold' for each customer.*Many to many relationship*- List products sold by order date.- List the order dates in descending order for orders for the 1940 Ford Pickup Truck.- List the names of customers and their corresponding order number where a particular order from that customer has a value greater than $25,000?- Are there any products that appear on all orders?- List the names of products sold at less than 80% of the MSRP.- Reports those products that have been sold with a markup of 100% or more (i.e.,  the priceEach is at least twice the buyPrice)- List the products ordered on a Monday.- What is the quantity on hand for products listed on 'On Hold' orders?<jupyter_code>Customers = pd.read_csv('data/ClassicModels_Customers.csv', sep=';')
Customers.head(1)
Employees = pd.read_csv('data/ClassicModels_Employees.csv', sep=';')
Employees.head(1)
Offices = pd.read_csv('data/ClassicModels_Offices.csv', sep=';')
Offices.head(1)
OrderDetails = pd.read_csv('data/ClassicModels_OrderDetails.csv', sep=';')
OrderDetails.head(1)
Orders = pd.read_csv('data/ClassicModels_Orders.csv', sep=';')
Orders.head(1)
Payments = pd.read_csv('data/ClassicModels_Payments.csv', sep=';')
Payments.head(1)
ProductLines = pd.read_csv('data/ClassicModels_ProductLines.csv', sep=';')
ProductLines.head(1)
Products = pd.read_csv('data/ClassicModels_Products.csv', sep=';')
Products.head(1)
# Report the account representative for each customer.

pd.merge(Customers, Employees, 
         left_on = 'salesRepEmployeeNumber', 
         right_on = 'employeeNumber', how='inner')[['customerName', 
                                                    'firstName', 'lastName']]

# Report total payments for Atelier graphique.

pd.merge(Customers, Payments).groupby('customerName')['amount'].sum().loc['Atelier graphique']
# Report total payments for Atelier graphique.

df_join = Customers.join(
    Orders, rsuffix = "_order").join(
    OrderDetails, rsuffix="_details")[Customers['customerName']=='<NAME>']

df_join['totalPrice'] = df_join['quantityOrdered'] * df_join['priceEach'] 
df_join['totalPrice'].sum()
# Report the total payments by date

Payments.groupby('paymentDate')['amount'].sum()
# Report the total payments by date
months=Payments['paymentDate'].str[:7]
Payments.groupby(months)['amount'].sum()
# Report the products that have not been sold.
#     same as products with no orders
df_join = pd.merge(Products, OrderDetails, how='left')
df_join[df_join['orderNumber'].isnull()]
# List the amount paid by each customer.
pd.merge(Payments, Customers).groupby('customerName')['amount'].sum()
# How many orders have been placed by Herkku Gifts?

df_join = pd.merge(Customers, Orders)
df_join[df_join['customerName']=='<NAME>'].count()['orderNumber']
# Who are the employees in Boston?
df_join = pd.merge(Employees, Offices)
df_join[df_join['city']=='Boston']
# Report those payments greater than $100,000. Sort the report so the customer who made the highest payment appears first.
pd.merge(Customers, Payments[Payments['amount'] > 100000]).sort_values(by='amount', ascending = False)
# List the value of 'On Hold' orders.
df_join = pd.merge(Orders[Orders['status']=='On Hold'], OrderDetails)
df_join['total'] = df_join['priceEach'] * df_join['quantityOrdered']
df_join['total'].sum()
# Report the number of orders 'On Hold' for each customer
pd.merge(Orders[Orders['status']=='On Hold'], Customers).groupby('customerName')['orderNumber'].count()
# List products sold by order date
pd.merge(pd.merge(Orders, OrderDetails), Products).sort_values(by='orderDate')
# List the order dates in descending order for orders for the 1940 Ford Pickup Truck
pd.merge(
    pd.merge(
        Products[Products.productName=='1940 Ford Pickup Truck'], OrderDetails
            ), Orders
        ).sort_values('orderDate')['orderDate']
# List the names of customers and their corresponding order number where a particular order from that customer has a value greater than $25,000?
# first, calculate amount
OrderDetails['amount'] = OrderDetails.quantityOrdered * OrderDetails.priceEach
# calculate total per order
order_sum = pd.merge(
    Orders, OrderDetails
    ).groupby('orderNumber')[['amount', 'customerNumber']].aggregate(
        {'amount':'sum', 'customerNumber':'first'})
# filter
high_orders = order_sum[order_sum.amount > 25000]
# join with customers
Customers.merge(high_orders.reset_index())[['customerName', 'orderNumber', 'amount']]
# Are there any products that appear on all orders?
number_of_orders = len(Orders)
j = pd.merge(Products, OrderDetails)
product_orders = j.groupby('productName')['orderNumber'].count()
product_orders[product_orders==number_of_orders]
# List the names of products sold at less than 80% of the MSRP
j = pd.merge(Products, OrderDetails)
j['markup'] = j['priceEach'] / j['MSRP']
set(j[j.markup < 0.8]['productName'])
# Reports those products that have been sold with a markup of 100% or more 
# (i.e., the priceEach is at least twice the buyPrice)
j['markup2'] = j['priceEach'] / j['buyPrice']
set(j[j['markup2'] > 2]['productName'])
global t 
def c(x):
    t = x
    return x
# List the products ordered on a Monday.
Orders['weekday'] = Orders.orderDate.apply(lambda x: pd.to_datetime(x).day_name())
pd.merge(pd.merge(Products, OrderDetails), 
         Orders[Orders.weekday=='Monday'])[['productName', 'orderDate', 'weekday']]

# What is the quantity on hand for products listed on 'On Hold' orders?
pd.merge(Products, pd.merge(Orders[Orders.status=='On Hold'], OrderDetails)
    ).groupby('productName')['quantityInStock'].sum()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12862816987473266, 'fraction_numerical': 0.010693553315001528, 'mean_word_length': 5.507952286282306, 'pattern_counts': {'":': 0, '<': 8, '<?xml version=': 0, '>': 10, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '431238', 'license': 'MIT', 'n_tokens_mistral': 2002, 'n_tokens_neox': 1893, 'n_words': 622, 'path': '20-warmup-solution_joining_datasets_merge_join.ipynb', 'repo_name': 'hanisaf/advanced-data-management-and-analytics', 'size': '6563'}