In [18]:
import numpy as np
import pandas as pd
import glob
import fileinput
import sys
import os
from matplotlib import pyplot as plt
import openpyxl
In [19]:
# 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
In [20]:
# 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
In [21]:
# 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
In [22]:
# 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
In [23]:
# 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']
In [24]:
# 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
In [25]:
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
In [26]:
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
In [27]:
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 . . .
In [28]:
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
In [29]:
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
In [30]:
# 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
In [31]:
# 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
In [32]:
#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
In [33]:
#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
In [ ]:
In [ ]: