You have already learnt how to perform pivot/groupby operations in both Python (Pandas) and SQL. In this segment, you will explore Pivot Tables in Excel.
Pivot Tables allow you to summarise and analyse data easily. In this session, you will focus on:
- Calculating the number of days it takes to deliver orders
- Analysing the costs associated with each order
It is recommended to work alongside the SME for a better grasp of the concepts. In the following video, Vivek will elaborate more on this topic and demonstrate how Pivot Tables can simplify your data analysis tasks.
In the video, a column named ‘days_to_delivery’ is created. It shows the difference between the dispatch date and the order delivery date.
Excel provides many date functions that help manage date/time columns of the data set. In the demonstration, the DAYS() function is used to calculate the difference between the two dates.
To learn more about date functions in Excel, you can refer to this link.
The next video will show how to create Pivot Tables to understand how the average cost and number of days to deliver orders vary across shipping routes and logistic providers.
From the conditional formatting of the Pivot Table, shown in the following graph, you might have noticed that for both logistic providers, the local orders have lower logistic costs compared to the outstation orders.
Similarly, for the Pivot Chart showing average days to delivery for both logistic providers, as seen in the graph below, the delivery time for local orders is lower compared to outstation orders.
The above clustered bar charts show the comparison between the two logistic providers in terms of delivery charges and delivery time.
Before proceeding to the next question, attempt the following exercises using the Superstores_data dataset.
In the next segment, you will learn about the What-If Analysis functionality in Excel, which helps you evaluate different scenarios (outcome values) by changing the values in the cells.