Suppose we have an excel file 'try.xlsx'. It has 4 columns: 'product', 'date', 'town', and 'sales'. Following python code will read the excel file in dataframe. Then take required steps to print following outputs:
1) product, month-wise sales total of each product
2) write code to print town-wise total sales. close the file. add required libraries
import pandas as pd
# Read the Excel file into a DataFrame
df = pd.read_excel('try.xlsx')
# Convert 'date' column to datetime format
df['date'] = pd.to_datetime(df['date'])
# Extract month from the 'date' column
df['month'] = df['date'].dt.month
# Calculate month-wise sales total of each product
product_monthly_sales = df.groupby(['product', 'month'])['sales'].sum()
# Print product, month-wise sales total
print("Product, Month-wise Sales Total:")
print(product_monthly_sales)
# Calculate town-wise total sales
town_total_sales = df.groupby('town')['sales'].sum()
# Print town-wise total sales
print("\nTown-wise Total Sales:")
print(town_total_sales)
