import numpy as np
import pandas as pd
import glob
import fileinput
import sys
import os
from matplotlib import pyplot as plt
import openpyxl
# delete 4 csv files
file = 'sales_by_person.csv'
if(os.path.exists(file) and os.path.isfile(file)):
os.remove(file)
print("file sales_by_person.csv deleted")
else:
print("file not found")
file2 = 'sales_by_person_modified.csv'
if(os.path.exists(file2) and os.path.isfile(file2)):
os.remove(file2)
print("file sales_by_person_modified.csv deleted")
else:
print("file not found")
file3 = 'sales_by_person_modified_gr.csv'
if(os.path.exists(file3) and os.path.isfile(file3)):
os.remove(file3)
print("file sales_by_person_modified_gr.csv deleted")
else:
print("file not found")
file4 = 'sales_jan_feb_mar_2021_d.csv'
if(os.path.exists(file4) and os.path.isfile(file4)):
os.remove(file4)
print("file sales_jan_feb_mar_2021_d.csv deleted")
else:
print("file not found")
file sales_by_person.csv deleted file sales_by_person_modified.csv deleted file sales_by_person_modified_gr.csv deleted file sales_jan_feb_mar_2021_d.csv deleted
# read csv file sales_january_2021_d.csv
df = pd.read_csv("_sales_january_2021_d.csv")
print (df)
Month Actual_Sales_Order_Date Budget_Sales Actual_Sales \ 0 Jan 2021 1/1/21 800 1000 1 Jan 2021 3/1/21 1200 1500 2 Jan 2021 4/1/21 8000 7000 3 Jan 2021 7/1/21 3500 3000 4 Jan 2021 9/1/21 4200 4000 5 Jan 2021 12/1/21 1900 2000 6 Jan 2021 17/1/21 1800 2300 7 Jan 2021 19/1/21 4000 4200 Actual_Sales_Order_Id Actual_Sales_Qty Sales_Comments Customer \ 0 1001 100 Above budget Customer 1 1 1002 150 Above budget Customer 2 2 1003 680 Below budget Customer 3 3 1004 290 Below budget Customer 4 4 1005 380 Below budget Customer 5 5 1006 190 Above budget Customer 6 6 1007 210 Above budget Customer 7 7 1008 400 Above budget Customer 8 Salesperson 0 Trifon Papadopoulos 1 George Dimitropoulos 2 Trifon Papadopoulos 3 George Dimitropoulos 4 Andreas Kyriakopoulos 5 Andreas Kyriakopoulos 6 Trifon Papadopoulos 7 Andreas Kyriakopoulos
# print sales head for certain salesperson
salesperson_sales = df[(df['Salesperson'] == 'Trifon Papadopoulos')]
print(salesperson_sales.head())
Month Actual_Sales_Order_Date Budget_Sales Actual_Sales \ 0 Jan 2021 1/1/21 800 1000 2 Jan 2021 4/1/21 8000 7000 6 Jan 2021 17/1/21 1800 2300 Actual_Sales_Order_Id Actual_Sales_Qty Sales_Comments Customer \ 0 1001 100 Above budget Customer 1 2 1003 680 Below budget Customer 3 6 1007 210 Above budget Customer 7 Salesperson 0 Trifon Papadopoulos 2 Trifon Papadopoulos 6 Trifon Papadopoulos
# print number of records
print("Records for Jan 21 for Saleperson Trifon Papadopoulos")
print(len(salesperson_sales))
Records for Jan 21 for Saleperson Trifon Papadopoulos 3
# relative path to search all csv files starting with "_sales"
files = glob.glob("*.csv")
print(files)
['products..csv', 'sales_january_2021_d_products_orders.csv', '_sales_feb_2021_d.csv', '_sales_january_2021_d.csv', '_sales_march_2021_d.csv']
# merge data from csv files into a new excel file and a new csv file
# df2 =[]
# for f in files:
# csv = pd.read_csv(f)
# df2.append(csv)
# df2 = pd.concat(df2)
# setting the path for joining multiple files
files = os.path.join("_sales*.csv")
# list of merged files returned
files = glob.glob(files)
print(files)
# joining files with concat and read_csv
df2 = pd.concat(map(pd.read_csv, files), ignore_index=True)
print("Data from all csv files")
print(" ")
print(df2)
# print number of records
print("-------------------------------------")
print("Records for Jan 21, Feb 21 and Mar 21")
print(len(df2))
df2.to_excel("sales_jan_feb_mar_2021_d.xlsx", index = False)
df2.to_csv("sales_jan_feb_mar_2021_d.csv", index = False)
['_sales_feb_2021_d.csv', '_sales_january_2021_d.csv', '_sales_march_2021_d.csv'] Data from all csv files Month Actual_Sales_Order_Date Budget_Sales Actual_Sales \ 0 Feb 2021 1/2/21 900 1200 1 Feb 2021 4/2/21 1200 1800 2 Feb 2021 10/2/21 9000 7000 3 Feb 2021 11/2/21 3200 3000 4 Feb 2021 14/2/21 4400 3900 5 Feb 2021 15/2/21 1800 2100 6 Feb 2021 17/2/21 1900 2400 7 Feb 2021 18/2/21 4100 4300 8 Jan 2021 1/1/21 800 1000 9 Jan 2021 3/1/21 1200 1500 10 Jan 2021 4/1/21 8000 7000 11 Jan 2021 7/1/21 3500 3000 12 Jan 2021 9/1/21 4200 4000 13 Jan 2021 12/1/21 1900 2000 14 Jan 2021 17/1/21 1800 2300 15 Jan 2021 19/1/21 4000 4200 16 Mar 2021 1/3/21 1300 1100 17 Mar 2021 10/3/21 1100 1700 18 Mar 2021 11/3/21 8900 8000 19 Mar 2021 12/3/21 2900 3100 20 Mar 2021 17/3/21 4400 4150 21 Mar 2021 25/3/21 1800 2150 22 Mar 2021 26/3/21 1750 2250 23 Mar 2021 27/3/21 4000 4450 Actual_Sales_Order_Id Actual_Sales_Qty Sales_Comments Customer \ 0 1009 100 Above budget Customer 1 1 1010 160 Above budget Customer 2 2 1011 670 Below budget Customer 3 3 1012 295 Below budget Customer 4 4 1013 372 Below budget Customer 5 5 1014 195 Above budget Customer 6 6 1015 220 Above budget Customer 7 7 1016 385 Above budget Customer 8 8 1001 100 Above budget Customer 1 9 1002 150 Above budget Customer 2 10 1003 680 Below budget Customer 3 11 1004 290 Below budget Customer 4 12 1005 380 Below budget Customer 5 13 1006 190 Above budget Customer 6 14 1007 210 Above budget Customer 7 15 1008 400 Above budget Customer 8 16 1017 90 Below budget Customer 1 17 1018 155 Above budget Customer 2 18 1019 784 Below budget Customer 3 19 1020 288 Above budget Customer 4 20 1021 376 Below budget Customer 5 21 1022 190 Above budget Customer 6 22 1023 195 Above budget Customer 7 23 1024 430 Above budget Customer 8 Salesperson 0 Trifon Papadopoulos 1 George Dimitropoulos 2 Trifon Papadopoulos 3 George Dimitropoulos 4 Andreas Kyriakopoulos 5 Andreas Kyriakopoulos 6 Trifon Papadopoulos 7 Andreas Kyriakopoulos 8 Trifon Papadopoulos 9 George Dimitropoulos 10 Trifon Papadopoulos 11 George Dimitropoulos 12 Andreas Kyriakopoulos 13 Andreas Kyriakopoulos 14 Trifon Papadopoulos 15 Andreas Kyriakopoulos 16 Trifon Papadopoulos 17 George Dimitropoulos 18 Trifon Papadopoulos 19 George Dimitropoulos 20 Andreas Kyriakopoulos 21 Andreas Kyriakopoulos 22 Trifon Papadopoulos 23 Andreas Kyriakopoulos ------------------------------------- Records for Jan 21, Feb 21 and Mar 21 24
total_actual_sales = pd.pivot_table(df2, index='Month', values='Actual_Sales', aggfunc=np.sum)
total_actual_sales_qty = pd.pivot_table(df2, index='Month', values='Actual_Sales_Qty', aggfunc=np.sum)
total_actual_sales_mean = pd.pivot_table(df2, index='Month', values='Actual_Sales', aggfunc=np.mean)
print('---------------------')
print('total actual sales per month')
print(total_actual_sales)
print('---------------------')
print('total actual sales qty per month')
print(total_actual_sales_qty)
print('---------------------')
print('total actual sales mean per month')
print(total_actual_sales_mean)
--------------------- total actual sales per month Actual_Sales Month Feb 2021 25700 Jan 2021 25000 Mar 2021 26900 --------------------- total actual sales qty per month Actual_Sales_Qty Month Feb 2021 2397 Jan 2021 2400 Mar 2021 2508 --------------------- total actual sales mean per month Actual_Sales Month Feb 2021 3212.5 Jan 2021 3125.0 Mar 2021 3362.5
sales_by_salesperson = pd.pivot_table(df2, index=['Month', 'Salesperson'], values='Actual_Sales', aggfunc=np.sum)
print('---------------------')
print('sales_by_person')
print(sales_by_salesperson)
--------------------- sales_by_person Actual_Sales Month Salesperson Feb 2021 Andreas Kyriakopoulos 10300 George Dimitropoulos 4800 Trifon Papadopoulos 10600 Jan 2021 Andreas Kyriakopoulos 10200 George Dimitropoulos 4500 Trifon Papadopoulos 10300 Mar 2021 Andreas Kyriakopoulos 10750 George Dimitropoulos 4800 Trifon Papadopoulos 11350
sales_by_salesperson.to_csv("sales_by_person.csv")
sales_by_salesperson.to_excel("sales_by_person.xlsx")
print("--------------------------------------------------------")
print("create and save sales_by_person.csv . . .")
print(" ")
print("create and save sales_by_person.xlsx . . .")
print(" ")
inputFileName = "sales_by_person.csv"
# English titles in csv
outputFileName = os.path.splitext(inputFileName)[0] + "_modified.csv"
with open(outputFileName, "w") as outfile:
for line in fileinput.input(
[inputFileName],
inplace=False):
if fileinput.isfirstline():
outfile.write('Month, Salesperson, Actual Sales\n')
else:
outfile.write(line)
inputFileName2 = "sales_by_person.csv"
# Greek titles in csv
outputFileName2 = os.path.splitext(inputFileName2)[0] + "_modified_gr.csv"
with open(outputFileName2, "w") as outfile:
for line in fileinput.input(
[inputFileName2],
inplace=False):
if fileinput.isfirstline():
outfile.write('Μήνας, Πωλητής, Πραγματικές πωλήσεις\n')
else:
outfile.write(line)
print("--------------------------------------------------------")
print("create and save sales_by_person_modified.csv . . .")
print(" ")
print("create and save sales_by_person_modified_gr.csv . . .")
print(" ")
-------------------------------------------------------- create and save sales_by_person.csv . . . create and save sales_by_person.xlsx . . . -------------------------------------------------------- create and save sales_by_person_modified.csv . . . create and save sales_by_person_modified_gr.csv . . .
df3 = pd.read_excel("sales_by_person.xlsx")
print("--------------------------------------------------------")
print("reading sales_by_person.xlsx (pivot table) . . .")
print(" ")
print(df3)
df3.rename(columns={'Month': 'Month', 'Salesperson': 'Salesperson', 'Actual_Sales': 'Actual Sales'}, inplace=True)
df3.to_excel("sales_by_person_modified.xlsx", index = False)
print("--------------------------------------------------------")
print("renaming headers and reading sales_by_person_modified.xlsx (pivot table) . . .")
print(" ")
print(df3)
-------------------------------------------------------- reading sales_by_person.xlsx (pivot table) . . . Month Salesperson Actual_Sales 0 Feb 2021 Andreas Kyriakopoulos 10300 1 NaN George Dimitropoulos 4800 2 NaN Trifon Papadopoulos 10600 3 Jan 2021 Andreas Kyriakopoulos 10200 4 NaN George Dimitropoulos 4500 5 NaN Trifon Papadopoulos 10300 6 Mar 2021 Andreas Kyriakopoulos 10750 7 NaN George Dimitropoulos 4800 8 NaN Trifon Papadopoulos 11350 -------------------------------------------------------- renaming headers and reading sales_by_person_modified.xlsx (pivot table) . . . Month Salesperson Actual Sales 0 Feb 2021 Andreas Kyriakopoulos 10300 1 NaN George Dimitropoulos 4800 2 NaN Trifon Papadopoulos 10600 3 Jan 2021 Andreas Kyriakopoulos 10200 4 NaN George Dimitropoulos 4500 5 NaN Trifon Papadopoulos 10300 6 Mar 2021 Andreas Kyriakopoulos 10750 7 NaN George Dimitropoulos 4800 8 NaN Trifon Papadopoulos 11350
df3 = pd.read_excel("sales_by_person.xlsx")
print("--------------------------------------------------------")
print("reading sales_by_person.xlsx (pivot table) . . .")
print(" ")
print(df3)
df3.rename(columns={'Month': 'Μήνας', 'Salesperson': 'Πωλητής', 'Actual_Sales': 'Πραγματικές πωλήσεις'}, inplace=True)
df3.to_excel("sales_by_person_modified.xlsx", index = False)
print("--------------------------------------------------------")
print("renaming headers and reading sales_by_person_modified_gr.xlsx (pivot table) . . .")
print(" ")
print(df3)
-------------------------------------------------------- reading sales_by_person.xlsx (pivot table) . . . Month Salesperson Actual_Sales 0 Feb 2021 Andreas Kyriakopoulos 10300 1 NaN George Dimitropoulos 4800 2 NaN Trifon Papadopoulos 10600 3 Jan 2021 Andreas Kyriakopoulos 10200 4 NaN George Dimitropoulos 4500 5 NaN Trifon Papadopoulos 10300 6 Mar 2021 Andreas Kyriakopoulos 10750 7 NaN George Dimitropoulos 4800 8 NaN Trifon Papadopoulos 11350 -------------------------------------------------------- renaming headers and reading sales_by_person_modified_gr.xlsx (pivot table) . . . Μήνας Πωλητής Πραγματικές πωλήσεις 0 Feb 2021 Andreas Kyriakopoulos 10300 1 NaN George Dimitropoulos 4800 2 NaN Trifon Papadopoulos 10600 3 Jan 2021 Andreas Kyriakopoulos 10200 4 NaN George Dimitropoulos 4500 5 NaN Trifon Papadopoulos 10300 6 Mar 2021 Andreas Kyriakopoulos 10750 7 NaN George Dimitropoulos 4800 8 NaN Trifon Papadopoulos 11350
# read csv file
df5 = pd.read_csv('sales_by_person.csv')
print (df5)
# assign value 0 to new column Month_ref
df5 = df5.assign(Month_ref=0)
print (df5)
# assign month number to column Month_ref
df5.loc[df5['Month'] == 'Jan 2021', 'Month_ref'] = '01'
df5.loc[df5['Month'] == 'Feb 2021', 'Month_ref'] = '02'
df5.loc[df5['Month'] == 'Mar 2021', 'Month_ref'] = '03'
print ("Actual Sales per month with with new column Month_ref -----------------------------")
print (df5)
# set index to Month_ref and Salesperson
df6 = df5.set_index(['Month_ref', 'Salesperson'])
print ("Actual Sales per month with index Month_ref and Salesperson -----------------------------")
df6.sort_index(inplace=True)
print(df6)
df6.to_excel('sales_by_person_index_month_ref_and_salesperson.xlsx')
# print Actual_Sales sum
print ("-----------------------------")
actual_sales_sum = df5["Actual_Sales"].sum()
print ("Actual Sales sum is:", actual_sales_sum)
# print Actual_Sales mean
print ("-----------------------------")
actual_sales_mean = df5["Actual_Sales"].mean()
print ("Actual Sales mean is:", actual_sales_mean)
# print Actual_Sales for certain salesperson
print ("-----------------------------")
actual_sales_total_for_certain_salesperson = df5.loc[df5['Salesperson'] == 'Trifon Papadopoulos', 'Actual_Sales'].sum()
print ("Actual Sales total for Salesperson Trifon Papadopoulos is:", actual_sales_total_for_certain_salesperson)
#calculate sum of sales grouped by salesperson
print ("-----------------------------")
print ("Actual Sales total per")
actual_sales_total_per_salesperson = df6.groupby('Salesperson')['Actual_Sales'].sum()
print(actual_sales_total_per_salesperson)
# plot the dataframe
import seaborn as sns
sns.set_style("dark")
actual_sales_total_per_salesperson.plot(x="Salesperson", y=["Actual_Sales"], kind="bar")
plt.rcParams["figure.figsize"] = [8.50, 6.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")
plt.legend(["Actual"])
# print bar graph
plt.show()
#calculate sum of sales grouped by month
print ("-----------------------------")
print ("Actual Sales total per")
actual_sales_total_per_month = df6.groupby('Month_ref')['Actual_Sales'].sum()
print(actual_sales_total_per_month)
# plot the dataframe
import seaborn as sns
sns.set_style("dark")
actual_sales_total_per_month.plot(x="Month_ref", y=["Actual_Sales"], kind="bar")
plt.rcParams["figure.figsize"] = [8.50, 6.50]
plt.rcParams["figure.autolayout"] = True
plt.xticks(rotation=30, horizontalalignment="center")
plt.title("Sales / Jan 21 - Mar 21")
plt.xlabel("Month")
plt.ylabel("Actual")
plt.legend(["Actual"])
# print bar graph
plt.show()
Month Salesperson Actual_Sales 0 Feb 2021 Andreas Kyriakopoulos 10300 1 Feb 2021 George Dimitropoulos 4800 2 Feb 2021 Trifon Papadopoulos 10600 3 Jan 2021 Andreas Kyriakopoulos 10200 4 Jan 2021 George Dimitropoulos 4500 5 Jan 2021 Trifon Papadopoulos 10300 6 Mar 2021 Andreas Kyriakopoulos 10750 7 Mar 2021 George Dimitropoulos 4800 8 Mar 2021 Trifon Papadopoulos 11350 Month Salesperson Actual_Sales Month_ref 0 Feb 2021 Andreas Kyriakopoulos 10300 0 1 Feb 2021 George Dimitropoulos 4800 0 2 Feb 2021 Trifon Papadopoulos 10600 0 3 Jan 2021 Andreas Kyriakopoulos 10200 0 4 Jan 2021 George Dimitropoulos 4500 0 5 Jan 2021 Trifon Papadopoulos 10300 0 6 Mar 2021 Andreas Kyriakopoulos 10750 0 7 Mar 2021 George Dimitropoulos 4800 0 8 Mar 2021 Trifon Papadopoulos 11350 0 Actual Sales per month with with new column Month_ref ----------------------------- Month Salesperson Actual_Sales Month_ref 0 Feb 2021 Andreas Kyriakopoulos 10300 02 1 Feb 2021 George Dimitropoulos 4800 02 2 Feb 2021 Trifon Papadopoulos 10600 02 3 Jan 2021 Andreas Kyriakopoulos 10200 01 4 Jan 2021 George Dimitropoulos 4500 01 5 Jan 2021 Trifon Papadopoulos 10300 01 6 Mar 2021 Andreas Kyriakopoulos 10750 03 7 Mar 2021 George Dimitropoulos 4800 03 8 Mar 2021 Trifon Papadopoulos 11350 03 Actual Sales per month with index Month_ref and Salesperson ----------------------------- Month Actual_Sales Month_ref Salesperson 01 Andreas Kyriakopoulos Jan 2021 10200 George Dimitropoulos Jan 2021 4500 Trifon Papadopoulos Jan 2021 10300 02 Andreas Kyriakopoulos Feb 2021 10300 George Dimitropoulos Feb 2021 4800 Trifon Papadopoulos Feb 2021 10600 03 Andreas Kyriakopoulos Mar 2021 10750 George Dimitropoulos Mar 2021 4800 Trifon Papadopoulos Mar 2021 11350 ----------------------------- Actual Sales sum is: 77600 ----------------------------- Actual Sales mean is: 8622.222222222223 ----------------------------- Actual Sales total for Salesperson Trifon Papadopoulos is: 32250 ----------------------------- Actual Sales total per Salesperson Andreas Kyriakopoulos 31250 George Dimitropoulos 14100 Trifon Papadopoulos 32250 Name: Actual_Sales, dtype: int64
----------------------------- Actual Sales total per Month_ref 01 25000 02 25700 03 26900 Name: Actual_Sales, dtype: int64
# read csv file
df7 = pd.read_csv('sales_january_2021_d_products_orders.csv', usecols = ['Actual_Sales_Order_Id', 'Actual_Sales_Product_Id', 'Actual_Sales_Product_Description', 'Actual_Sales_Product_Price', 'Actual_Sales_Qty'])
print ("-----------------------------")
print ("sales_january_2021_d_products_orders.csv")
print (df7)
# reading two csv files
data1 = pd.read_csv("sales_jan_feb_mar_2021_d.csv", usecols = ['Actual_Sales_Order_Id', 'Actual_Sales_Order_Date', 'Customer', 'Salesperson'])
print ("-----------------------------")
print ("data1")
print (data1)
data2 = pd.read_csv('sales_january_2021_d_products_orders.csv', usecols = ['Actual_Sales_Order_Id', 'Actual_Sales_Product_Id', 'Actual_Sales_Product_Description', 'Actual_Sales_Product_Price', 'Actual_Sales_Qty'])
print ("-----------------------------")
print ("data2")
print (data2)
# using merge function by setting how='inner'
output1 = pd.merge(data1, data2,
on='Actual_Sales_Order_Id',
how='left')
# displaying result
print ("-----------------------------")
print ("output1")
# set index to Actual_Sales_Order_Id
output1 = output1.set_index(['Actual_Sales_Order_Id'])
output1.sort_index(inplace=True)
print ("Actual Sales per month with index Actual_Sales_Order_Id -----------------------------")
print(output1)
output1.to_excel('output1.xlsx')
# read csv file
df8 = pd.read_csv('sales_jan_feb_mar_2021_d.csv', usecols = ['Actual_Sales_Order_Id', 'Actual_Sales_Order_Date', 'Customer', 'Salesperson'])
print ("-----------------------------")
print ("sales_jan_feb_mar_2021_d.csv - 4 columns")
print (df8)
----------------------------- sales_january_2021_d_products_orders.csv Actual_Sales_Order_Id Actual_Sales_Product_Id \ 0 1001 150001 1 1001 150002 2 1002 150003 3 1002 150004 4 1003 150005 5 1003 150006 6 1003 150007 7 1004 150008 8 1004 150009 9 1005 150010 10 1005 150011 11 1006 150012 12 1005 150013 13 1007 150014 14 1007 150015 15 1008 150014 Actual_Sales_Product_Description Actual_Sales_Product_Price \ 0 Product Description 150001 10 1 Product Description 150002 10 2 Product Description 150003 10 3 Product Description 150004 10 4 Product Description 150005 50 5 Product Description 150006 10 6 Product Description 150007 10 7 Product Description 150008 10 8 Product Description 150009 11 9 Product Description 150010 10 10 Product Description 150011 11 11 Product Description 150012 11 12 Product Description 150013 10 13 Product Description 150014 10 14 Product Description 150015 12 15 Product Description 150014 10 Actual_Sales_Qty 0 50 1 50 2 75 3 75 4 80 5 100 6 200 7 190 8 100 9 180 10 200 11 100 12 90 13 110 14 100 15 420 ----------------------------- data1 Actual_Sales_Order_Date Actual_Sales_Order_Id Customer \ 0 1/2/21 1009 Customer 1 1 4/2/21 1010 Customer 2 2 10/2/21 1011 Customer 3 3 11/2/21 1012 Customer 4 4 14/2/21 1013 Customer 5 5 15/2/21 1014 Customer 6 6 17/2/21 1015 Customer 7 7 18/2/21 1016 Customer 8 8 1/1/21 1001 Customer 1 9 3/1/21 1002 Customer 2 10 4/1/21 1003 Customer 3 11 7/1/21 1004 Customer 4 12 9/1/21 1005 Customer 5 13 12/1/21 1006 Customer 6 14 17/1/21 1007 Customer 7 15 19/1/21 1008 Customer 8 16 1/3/21 1017 Customer 1 17 10/3/21 1018 Customer 2 18 11/3/21 1019 Customer 3 19 12/3/21 1020 Customer 4 20 17/3/21 1021 Customer 5 21 25/3/21 1022 Customer 6 22 26/3/21 1023 Customer 7 23 27/3/21 1024 Customer 8 Salesperson 0 Trifon Papadopoulos 1 George Dimitropoulos 2 Trifon Papadopoulos 3 George Dimitropoulos 4 Andreas Kyriakopoulos 5 Andreas Kyriakopoulos 6 Trifon Papadopoulos 7 Andreas Kyriakopoulos 8 Trifon Papadopoulos 9 George Dimitropoulos 10 Trifon Papadopoulos 11 George Dimitropoulos 12 Andreas Kyriakopoulos 13 Andreas Kyriakopoulos 14 Trifon Papadopoulos 15 Andreas Kyriakopoulos 16 Trifon Papadopoulos 17 George Dimitropoulos 18 Trifon Papadopoulos 19 George Dimitropoulos 20 Andreas Kyriakopoulos 21 Andreas Kyriakopoulos 22 Trifon Papadopoulos 23 Andreas Kyriakopoulos ----------------------------- data2 Actual_Sales_Order_Id Actual_Sales_Product_Id \ 0 1001 150001 1 1001 150002 2 1002 150003 3 1002 150004 4 1003 150005 5 1003 150006 6 1003 150007 7 1004 150008 8 1004 150009 9 1005 150010 10 1005 150011 11 1006 150012 12 1005 150013 13 1007 150014 14 1007 150015 15 1008 150014 Actual_Sales_Product_Description Actual_Sales_Product_Price \ 0 Product Description 150001 10 1 Product Description 150002 10 2 Product Description 150003 10 3 Product Description 150004 10 4 Product Description 150005 50 5 Product Description 150006 10 6 Product Description 150007 10 7 Product Description 150008 10 8 Product Description 150009 11 9 Product Description 150010 10 10 Product Description 150011 11 11 Product Description 150012 11 12 Product Description 150013 10 13 Product Description 150014 10 14 Product Description 150015 12 15 Product Description 150014 10 Actual_Sales_Qty 0 50 1 50 2 75 3 75 4 80 5 100 6 200 7 190 8 100 9 180 10 200 11 100 12 90 13 110 14 100 15 420 ----------------------------- output1 Actual Sales per month with index Actual_Sales_Order_Id ----------------------------- Actual_Sales_Order_Date Customer \ Actual_Sales_Order_Id 1001 1/1/21 Customer 1 1001 1/1/21 Customer 1 1002 3/1/21 Customer 2 1002 3/1/21 Customer 2 1003 4/1/21 Customer 3 1003 4/1/21 Customer 3 1003 4/1/21 Customer 3 1004 7/1/21 Customer 4 1004 7/1/21 Customer 4 1005 9/1/21 Customer 5 1005 9/1/21 Customer 5 1005 9/1/21 Customer 5 1006 12/1/21 Customer 6 1007 17/1/21 Customer 7 1007 17/1/21 Customer 7 1008 19/1/21 Customer 8 1009 1/2/21 Customer 1 1010 4/2/21 Customer 2 1011 10/2/21 Customer 3 1012 11/2/21 Customer 4 1013 14/2/21 Customer 5 1014 15/2/21 Customer 6 1015 17/2/21 Customer 7 1016 18/2/21 Customer 8 1017 1/3/21 Customer 1 1018 10/3/21 Customer 2 1019 11/3/21 Customer 3 1020 12/3/21 Customer 4 1021 17/3/21 Customer 5 1022 25/3/21 Customer 6 1023 26/3/21 Customer 7 1024 27/3/21 Customer 8 Salesperson Actual_Sales_Product_Id \ Actual_Sales_Order_Id 1001 Trifon Papadopoulos 150001.0 1001 Trifon Papadopoulos 150002.0 1002 George Dimitropoulos 150003.0 1002 George Dimitropoulos 150004.0 1003 Trifon Papadopoulos 150005.0 1003 Trifon Papadopoulos 150006.0 1003 Trifon Papadopoulos 150007.0 1004 George Dimitropoulos 150008.0 1004 George Dimitropoulos 150009.0 1005 Andreas Kyriakopoulos 150013.0 1005 Andreas Kyriakopoulos 150011.0 1005 Andreas Kyriakopoulos 150010.0 1006 Andreas Kyriakopoulos 150012.0 1007 Trifon Papadopoulos 150015.0 1007 Trifon Papadopoulos 150014.0 1008 Andreas Kyriakopoulos 150014.0 1009 Trifon Papadopoulos NaN 1010 George Dimitropoulos NaN 1011 Trifon Papadopoulos NaN 1012 George Dimitropoulos NaN 1013 Andreas Kyriakopoulos NaN 1014 Andreas Kyriakopoulos NaN 1015 Trifon Papadopoulos NaN 1016 Andreas Kyriakopoulos NaN 1017 Trifon Papadopoulos NaN 1018 George Dimitropoulos NaN 1019 Trifon Papadopoulos NaN 1020 George Dimitropoulos NaN 1021 Andreas Kyriakopoulos NaN 1022 Andreas Kyriakopoulos NaN 1023 Trifon Papadopoulos NaN 1024 Andreas Kyriakopoulos NaN Actual_Sales_Product_Description \ Actual_Sales_Order_Id 1001 Product Description 150001 1001 Product Description 150002 1002 Product Description 150003 1002 Product Description 150004 1003 Product Description 150005 1003 Product Description 150006 1003 Product Description 150007 1004 Product Description 150008 1004 Product Description 150009 1005 Product Description 150013 1005 Product Description 150011 1005 Product Description 150010 1006 Product Description 150012 1007 Product Description 150015 1007 Product Description 150014 1008 Product Description 150014 1009 NaN 1010 NaN 1011 NaN 1012 NaN 1013 NaN 1014 NaN 1015 NaN 1016 NaN 1017 NaN 1018 NaN 1019 NaN 1020 NaN 1021 NaN 1022 NaN 1023 NaN 1024 NaN Actual_Sales_Product_Price Actual_Sales_Qty Actual_Sales_Order_Id 1001 10.0 50.0 1001 10.0 50.0 1002 10.0 75.0 1002 10.0 75.0 1003 50.0 80.0 1003 10.0 100.0 1003 10.0 200.0 1004 10.0 190.0 1004 11.0 100.0 1005 10.0 90.0 1005 11.0 200.0 1005 10.0 180.0 1006 11.0 100.0 1007 12.0 100.0 1007 10.0 110.0 1008 10.0 420.0 1009 NaN NaN 1010 NaN NaN 1011 NaN NaN 1012 NaN NaN 1013 NaN NaN 1014 NaN NaN 1015 NaN NaN 1016 NaN NaN 1017 NaN NaN 1018 NaN NaN 1019 NaN NaN 1020 NaN NaN 1021 NaN NaN 1022 NaN NaN 1023 NaN NaN 1024 NaN NaN ----------------------------- sales_jan_feb_mar_2021_d.csv - 4 columns Actual_Sales_Order_Date Actual_Sales_Order_Id Customer \ 0 1/2/21 1009 Customer 1 1 4/2/21 1010 Customer 2 2 10/2/21 1011 Customer 3 3 11/2/21 1012 Customer 4 4 14/2/21 1013 Customer 5 5 15/2/21 1014 Customer 6 6 17/2/21 1015 Customer 7 7 18/2/21 1016 Customer 8 8 1/1/21 1001 Customer 1 9 3/1/21 1002 Customer 2 10 4/1/21 1003 Customer 3 11 7/1/21 1004 Customer 4 12 9/1/21 1005 Customer 5 13 12/1/21 1006 Customer 6 14 17/1/21 1007 Customer 7 15 19/1/21 1008 Customer 8 16 1/3/21 1017 Customer 1 17 10/3/21 1018 Customer 2 18 11/3/21 1019 Customer 3 19 12/3/21 1020 Customer 4 20 17/3/21 1021 Customer 5 21 25/3/21 1022 Customer 6 22 26/3/21 1023 Customer 7 23 27/3/21 1024 Customer 8 Salesperson 0 Trifon Papadopoulos 1 George Dimitropoulos 2 Trifon Papadopoulos 3 George Dimitropoulos 4 Andreas Kyriakopoulos 5 Andreas Kyriakopoulos 6 Trifon Papadopoulos 7 Andreas Kyriakopoulos 8 Trifon Papadopoulos 9 George Dimitropoulos 10 Trifon Papadopoulos 11 George Dimitropoulos 12 Andreas Kyriakopoulos 13 Andreas Kyriakopoulos 14 Trifon Papadopoulos 15 Andreas Kyriakopoulos 16 Trifon Papadopoulos 17 George Dimitropoulos 18 Trifon Papadopoulos 19 George Dimitropoulos 20 Andreas Kyriakopoulos 21 Andreas Kyriakopoulos 22 Trifon Papadopoulos 23 Andreas Kyriakopoulos
#Styling the excel
# read xlsx file
df9 = pd.read_excel('sales_jan_feb_mar_2021_d.xlsx')
print (df9)
Month Actual_Sales_Order_Date Budget_Sales Actual_Sales \ 0 Feb 2021 1/2/21 900 1200 1 Feb 2021 4/2/21 1200 1800 2 Feb 2021 10/2/21 9000 7000 3 Feb 2021 11/2/21 3200 3000 4 Feb 2021 14/2/21 4400 3900 5 Feb 2021 15/2/21 1800 2100 6 Feb 2021 17/2/21 1900 2400 7 Feb 2021 18/2/21 4100 4300 8 Jan 2021 1/1/21 800 1000 9 Jan 2021 3/1/21 1200 1500 10 Jan 2021 4/1/21 8000 7000 11 Jan 2021 7/1/21 3500 3000 12 Jan 2021 9/1/21 4200 4000 13 Jan 2021 12/1/21 1900 2000 14 Jan 2021 17/1/21 1800 2300 15 Jan 2021 19/1/21 4000 4200 16 Mar 2021 1/3/21 1300 1100 17 Mar 2021 10/3/21 1100 1700 18 Mar 2021 11/3/21 8900 8000 19 Mar 2021 12/3/21 2900 3100 20 Mar 2021 17/3/21 4400 4150 21 Mar 2021 25/3/21 1800 2150 22 Mar 2021 26/3/21 1750 2250 23 Mar 2021 27/3/21 4000 4450 Actual_Sales_Order_Id Actual_Sales_Qty Sales_Comments Customer \ 0 1009 100 Above budget Customer 1 1 1010 160 Above budget Customer 2 2 1011 670 Below budget Customer 3 3 1012 295 Below budget Customer 4 4 1013 372 Below budget Customer 5 5 1014 195 Above budget Customer 6 6 1015 220 Above budget Customer 7 7 1016 385 Above budget Customer 8 8 1001 100 Above budget Customer 1 9 1002 150 Above budget Customer 2 10 1003 680 Below budget Customer 3 11 1004 290 Below budget Customer 4 12 1005 380 Below budget Customer 5 13 1006 190 Above budget Customer 6 14 1007 210 Above budget Customer 7 15 1008 400 Above budget Customer 8 16 1017 90 Below budget Customer 1 17 1018 155 Above budget Customer 2 18 1019 784 Below budget Customer 3 19 1020 288 Above budget Customer 4 20 1021 376 Below budget Customer 5 21 1022 190 Above budget Customer 6 22 1023 195 Above budget Customer 7 23 1024 430 Above budget Customer 8 Salesperson 0 Trifon Papadopoulos 1 George Dimitropoulos 2 Trifon Papadopoulos 3 George Dimitropoulos 4 Andreas Kyriakopoulos 5 Andreas Kyriakopoulos 6 Trifon Papadopoulos 7 Andreas Kyriakopoulos 8 Trifon Papadopoulos 9 George Dimitropoulos 10 Trifon Papadopoulos 11 George Dimitropoulos 12 Andreas Kyriakopoulos 13 Andreas Kyriakopoulos 14 Trifon Papadopoulos 15 Andreas Kyriakopoulos 16 Trifon Papadopoulos 17 George Dimitropoulos 18 Trifon Papadopoulos 19 George Dimitropoulos 20 Andreas Kyriakopoulos 21 Andreas Kyriakopoulos 22 Trifon Papadopoulos 23 Andreas Kyriakopoulos
#Styling the excel
from openpyxl.styles import PatternFill, Border, Side, colors, Alignment, Protection, Font, Color
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.worksheet.dimensions import ColumnDimension, DimensionHolder
from openpyxl.utils import get_column_letter
from openpyxl.chart import Reference, LineChart, BarChart3D
font2 = Font(name='Calibri', size=17, bold=True, color='07101c')
font3 = Font(name='Calibri', size=13, bold=False, color='07101c')
align2 = Alignment(horizontal="center", wrap_text= True, vertical="center")
alignment=Alignment(horizontal='general',
vertical='bottom',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
font = Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
center_align = Alignment(horizontal='center', vertical='center')
Border3=Border(left=Side(border_style='double', color='FF000000'),
right=Side(border_style='double', color='FF000000'),
top=Side(border_style='double', color='FF000000'),
bottom=Side(border_style='double', color='FF000000'))
import warnings
warnings.filterwarnings('ignore', category=UserWarning, module='openpyxl')
# styling xlsx file sales_jan_feb_mar_2021_d.xlsx
wb = openpyxl.load_workbook('sales_jan_feb_mar_2021_d.xlsx')
# Styling sheet
ws = wb['Sheet1']
dim_holder = DimensionHolder(worksheet=ws)
for col in range(ws.min_column, ws.max_column + 1):
dim_holder[get_column_letter(col)] = ColumnDimension(ws, min=col, max=col, width=37)
ws.column_dimensions = dim_holder
for cell in ws['A']:
cell.alignment = Alignment(horizontal="center")
cell.font = font3
cell.border = Border3
for cell in ws['B']:
cell.alignment = Alignment(horizontal="center")
cell.font = font3
cell.border = Border3
for cell in ws['C']:
cell.alignment = Alignment(horizontal="center")
cell.font = font3
cell.border = Border3
cell.number_format = '#,##0.00€'
for cell in ws['D']:
cell.alignment = Alignment(horizontal="center")
cell.font = font3
cell.border = Border3
cell.number_format = '#,##0.00€'
for cell in ws['E']:
cell.alignment = Alignment(horizontal="center")
cell.font = font3
cell.border = Border3
for cell in ws['F']:
#cell.alignment = Alignment(horizontal="center")
cell.alignment = align2
cell.font = font3
cell.border = Border3
for cell in ws['G']:
#cell.alignment = Alignment(horizontal="center")
cell.alignment = align2
cell.font = font3
cell.border = Border3
for cell in ws['H']:
#cell.alignment = Alignment(horizontal="center")
cell.alignment = align2
cell.font = font3
cell.border = Border3
for cell in ws['I']:
#cell.alignment = Alignment(horizontal="center")
cell.alignment = align2
cell.font = font3
cell.border = Border3
ws['A1'].font = font2
ws['B1'].font = font2
ws['C1'].font = font2
ws['D1'].font = font2
ws['D1'].alignment = align2
ws['E1'].font = font2
ws['F1'].font = font2
ws['G1'].font = font2
ws['H1'].font = font2
ws['I1'].font = font2
ws['K16'] = 'Credit :'
ws['K16'].font = font2
ws['K17'] = 'Trifonas Papadopoulos'
ws['K17'].font = font2
ws['K18'] = 'www.mindstorm.gr'
ws['K18'].font = font2
wb.save('sales_jan_feb_mar_2021_d.xlsx')
# styling xls file output1.xlsx
wb = openpyxl.load_workbook('output1.xlsx')
# Styling sheet
ws = wb['Sheet1']
dim_holder = DimensionHolder(worksheet=ws)
for col in range(ws.min_column, ws.max_column+1):
dim_holder[get_column_letter(col)] = ColumnDimension(ws, min=col, max=col, width=37)
ws.column_dimensions = dim_holder
for cell in ws['A']:
cell.alignment = Alignment(horizontal="center")
cell.font = font3
cell.border = Border3
for cell in ws['B']:
cell.alignment = Alignment(horizontal="center")
cell.font = font3
cell.border = Border3
for cell in ws['C']:
cell.alignment = Alignment(horizontal="center")
cell.font = font3
cell.border = Border3
for cell in ws['D']:
cell.alignment = Alignment(horizontal="center")
cell.font = font3
cell.border = Border3
for cell in ws['E']:
cell.alignment = Alignment(horizontal="center")
cell.font = font3
cell.border = Border3
for cell in ws['F']:
#cell.alignment = Alignment(horizontal="center")
cell.alignment = align2
cell.font = font3
cell.border = Border3
for cell in ws['G']:
#cell.alignment = Alignment(horizontal="center")
cell.alignment = align2
cell.font = font3
cell.border = Border3
for cell in ws['H']:
#cell.alignment = Alignment(horizontal="center")
cell.alignment = align2
cell.font = font3
cell.border = Border3
ws['A1'].font = font2
ws['B1'].font = font2
ws['C1'].font = font2
ws['D1'].font = font2
ws['D1'].alignment = align2
ws['E1'].font = font2
ws['F1'].font = font2
ws['G1'].font = font2
ws['H1'].font = font2
ws['K16'] = 'Credit :'
ws['K16'].font = font2
ws['K17'] = 'Trifonas Papadopoulos'
ws['K17'].font = font2
ws['K18'] = 'www.mindstorm.gr'
ws['K18'].font = font2
wb.save('output1.xlsx')
print("You are done! Check excel files : sales_jan_feb_mar_2021_d.xlsx and output1.xlsx")
You are done! Check excel files : sales_jan_feb_mar_2021_d.xlsx and output1.xlsx