Task $1$: Aggregate Sales by Category

In this task, you will be working with the 'HOP$\_$RBC$\_$Merchandise.xlsx$\text{'}.$ Read the raw data into a Pandas DataFrame named 'sales$\_$df$\text{'}.$ Next, use 'sales$\_$df$\text{'}$ to aggregate the total sales amount for each 'Merchandise$\_$Category'. In other words, you are to aggregate the sales by 'Merchandise$\_$Category'. The results should be stored in a Pandas DataFrame named 'category$\_$sales$\_$df$\text{'}.$ Print the DataFrame as the list step.

Note: if you want to rename the aggregated column then use this method:

category$\_$sales$\_$df$.$rename$($columns$=\{\text{'}$Sale$\_$Amount': 'Total$\_$Sales$\_$Amount'$\})$

insert your code here

Task $2$: Count Items Sold by Category

In this task, you will continue working with the Merchandise data, but this time your focus will be on counting the number of sales transactions for each 'Merchandise$\_$Category'. In essence, you are tasked with aggregating the number of items sold by 'Merchandise$\_$Category'. The results of this aggregation should be stored in a Pandas DataFrame named 'category$\_$num$\_$items$\_$sold$\_$df$\text{'}.$ As the final step, you will print this DataFrame to display the number of items sold in each merchandise category.

insert your code here

Task $3$: Merge the Total Sales and Item Count DataFrames

In this task, you will build upon your previous work. Your objective is to merge two DataFrames that you have previously created: one containing the total sales amount for each 'Merchandise$\_$Category and the other containing the number of items sold in each category. The resulting DataFrame should have three columns: 'Merchandise$\_$Category', 'Total$\_$Sales$\_$Amount', 'and Number$\_$of$\_$Items$\_$Sold'.

insert your code here

Task $4$: Create Categories $($i$.$e$.$ bins$)$ Based on Sales Amount

In this task, you will be enhancing the 'sales$\_$df$\text{'}$ DataFrame by adding a new column that categorizes each sale amount into predefined bins. These bins will represent ranges of sale amounts, allowing for easier analysis of the sales data. Specifically, the sale amounts will be categorized into the following bins: $\text{'}$$$0-19\mathrm{.}99\text{'},\text{'}$$$20-39\mathrm{.}99\text{'},\text{'}$$$40-59\mathrm{.}99\text{'},\text{'}$$$60-79\mathrm{.}99\text{'},$ and $\text{'}$$$80-99\mathrm{.}99\text{'}.$ Print 'sales$\_$df$\text{'}$ at the end.

insert your code here

Task $5$: Create Bins

In this task, you will be taking the 'sales$\_$df$\text{'}$ DataFrame, which now includes the 'Sale$\_$Amount$\_$Bin' column, and further aggregating the data to gain insights into the sales distribution across different merchandise categories and sale amount bins. Specifically, you will group the data by both 'Merchandise$\_$Category' and 'Sale$\_$Amount$\_$Bin' and then count the number of items sold within each group.

This this is a little more complex than the examples in the lesson so I will provide the code for this one below. Copy$/$Paste this into your code. Be sure to compare this back to the aggregations in the lessons to see how it differs. Notice how I break break this long statement down across lines. This is just for readability.

# Aggregate the data by Merchandise$\_$Category and Sale$\_$Amount$\_$Bin, and count the number of items in each bin

item$\_$count$\_$bins$\_$df $=($

sales$\_$df

$.$groupby$([\text{'}$Merchandise$\_$Category', 'Sale$\_$Amount$\_$Bin'$])$

$.$agg$(\{\text{'}$Transaction$\_$ID$\text{'}$: 'count'$\})$

$.$rename$($columns$=\{\text{'}$Transaction$\_$ID$\text{'}$: 'Number$\_$of$\_$Items$\_$Sold'$\})$

$.$reset$\_$index$()$

$)$

If this steps works correctly then you should see this output:

Merchandise$\_$Category Sale$\_$Amount$\_$Bin Number$\_$of$\_$Items$\_$Sold

$0$ Apparel $$0-19\mathrm{.}995$

$1$ Apparel $$20-39\mathrm{.}9920$

$2$ Apparel $$40-59\mathrm{.}9919$

$3$ Apparel $$60-79\mathrm{.}990$

$4$ Apparel $$80-99\mathrm{.}990$

$5$ BBQ Essentials $$0-19\mathrm{.}9935$

$6$ BBQ Essentials $$20-39\mathrm{.}9910$

$7$ BBQ Essentials $$40-59\mathrm{.}996$

$8$ BBQ Essentials $$60-79\mathrm{.}990$

$9$ BBQ Essentials $$80-99\mathrm{.}990$

$10$ Home & Kitchen $$0-19\mathrm{.}9921$

$11$ Home & Kitchen $$20-39\mathrm{.}9939$

$12$ Home & Kitchen $$40-59\mathrm{.}990$

$13$ Home & Kitchen $$60-79\mathrm{.}990$

$14$ Home & Kitchen $$80-99\mathrm{.}990$

$15$ Novelties $$0-19\mathrm{.}9945$

$16$ Novelties $$20-39\mathrm{.}990$

$17$ Novelties $$40-59\mathrm{.}990$

$18$ Novelties $$60-79\mathrm{.}990$

$19$ Novelties $$80-99\mathrm{.}990$

insert your code here

Task $6$: Transpose the Bins Into Columns