In [2]:
import openpyxl
from openpyxl import load_workbook
# Specify the Excel file name
excel_file = "sales_jan_feb_mar_2021_d.xlsx"
# Load the Excel file and create a workbook object
workbook = load_workbook(excel_file)
# Specify the name of the sheet you want to delete
sheet_name_to_delete = "ActSalesMean by Salesperson" # Replace with the actual sheet name
# Check if the sheet exists, and if so, delete it
if sheet_name_to_delete in workbook.sheetnames:
sheet_to_delete = workbook[sheet_name_to_delete]
workbook.remove(sheet_to_delete)
# Save the modified workbook
workbook.save(excel_file)
# Create now an ExcelWriter in 'append' mode
writer = pd.ExcelWriter(excel_file, engine='openpyxl', mode='a')
data = pd.read_excel(excel_file)
mean_sales_by_salesperson = data.groupby('Salesperson')['Actual_Sales'].mean().reset_index()
# Rename the 'old_label' column to 'new_label'
mean_sales_by_salesperson = mean_sales_by_salesperson.rename(columns={'Actual_Sales': 'Actual_Sales_Mean'})
# Write the DataFrame to a new sheet
mean_sales_by_salesperson.to_excel(writer, sheet_name='ActSalesMean by Salesperson', index=False)
# Save and close the ExcelWriter
writer.save()
print(f"Data has been saved to {excel_file} in the 'ActSalesMean by Salesperson' tab.")
print(mean_sales_by_salesperson.to_string(index=False))
# plot the dataframe
from matplotlib import pyplot as plt
import seaborn as sns
sns.set_style("dark")
mean_sales_by_salesperson.plot(x="Salesperson", y=["Actual_Sales_Mean"], kind="bar")
plt.rcParams["figure.figsize"] = [11.50, 7.50]
plt.rcParams["figure.autolayout"] = True
plt.xticks(rotation=30, horizontalalignment="center")
plt.title("Sales / Jan 21 - Mar 21")
plt.xlabel("Salesperson")
plt.ylabel("Actual Sales Mean")
plt.legend(["Actual Sales Mean"])
# print bar graph
plt.show()
Data has been saved to sales_jan_feb_mar_2021_d.xlsx in the 'ActSalesMean by Salesperson' tab.
Salesperson Actual_Sales_Mean
Andreas Kyriakopoulos 3472.222222
George Dimitropoulos 2350.000000
Trifon Papadopoulos 3583.333333
