Join, merge multiple CSV files to one CSV or EXCEL file, renaming headers and styling multiple EXCEL files with PYTHON

• Let’s assume that you got three csv files from the sales department :

“_sales_january_2021_d.csv”

“_sales_feb_2021_d.csv”“_sales_mar_2021_d.csv”

and you want :

1. to join them in one new csv file : “sales_jan_feb_mar_2021_d.csv”
2. to find actual sales sum per month, actual sales sum (quantity) per month and actual sales mean per month
3. to save actual sales by person per month in csv file : “sales_by_person.csv”and in excel file : “sales_by_person.xlsx”
4. to save “sales_by person.csv” as “sales_by_person_modified.csv” with different english headers
5. to save “sales_by person.csv” as “sales_by_person_modified_gr.csv” with different greek headers
6. to find actual sales sum, actual sales mean and actual sales total for salesperson : “Trifon Papadopoulos”
7. to create a graph(bar) for Actual Sales total per Salesperson
8. to create a graph(bar) for Actual Sales total per Month_ref
9. to join “sales_jan_feb_mar_2021_d.csv” with “sales_january_2021_d_products_orders.csv” and save the output to excel file : “output1.xlsx”
10. to improve styling of excel files : “sales_jan_feb_mar_2021_d.xlsx” and “output1.xlsx”

Results of code

• delete (if they exist) the following csv files :  “sales_by_person.csv” deleted, “sales_by_person_modified.csv” deleted, “sales_by_person_modified_gr.csv” deleted, “sales_jan_feb_mar_2021_d.csv” deleted

• read csv file : “_sales_january_2021_d.csv”

• print sales head for certain salesperson from csv file : “_sales_january_2021_d.csv”

• print number of records for certain salesperson “Trifon Papadopoulos“ from csv file : “_sales_january_2021_d.csv”
• print names of all csv files in a folder
• print names of csv files in a folder starting with “_sales”
• join all csv files in a folder starting with “_sales” and show the results (print)
• show the number of records (print)
• save records from all csv files in a folder starting with “_sales” in excel file : “sales_jan_feb_mar_2021_d.xlsx”
• save records from all csv files in a folder starting with “_sales” in csv file : “sales_jan_feb_mar_2021_d.csv”
• find actual sales sum per month for Jan 2021 – Mar 2021
• find actual sales sum (quantity) per month for Jan 2021 – Mar 2021
• find actual sales mean per month for Jan 2021 – Mar 2021
• find actual sales by person by month for Jan 2021 – Mar 2021
• save actual sales by person by month for Jan 2021 – Mar 2021 in excel file : “sales_by_person.xlsx”
• save actual sales by person by month for Jan 2021 – Mar 2021 in csv file : “sales_by_person.csv”
• save “sales_by_person.csv” as “sales_by_person_modified.csv” with different english headers
• save “sales_by_person.csv” as “sales_by_person_modified_gr.csv” with different greek headers
• add new field “Month_ref” and corresponding value i.e. “01” for “Jan 21”, “02” for “Feb 21”, “03” for “Mar 2021” according to existing field “Month” to “sales_by_person.csv”
• print actual sales sum
• print actual sales mean
• print actual sales sum for salesperson “Trifon Papadopoulos” (csv file : sales_by_person.csv)
• show graph(bar) for Actual Sales total per Salesperson
• show graph(bar) for Actual Sales total per Month_ref
• join “sales_jan_feb_mar_2021_d.csv” with “sales_january_2021_d_products_orders.csv” and save the output to excel file : “output1.xlsx”
• change styling of excel files : “sales_jan_feb_mar_2021_d.xlsx” and “output1.xlsx”

Download zip file : main_2.rar with the following 11 files :

1) “main_2.ipynb”,

2) “_sales_january_2021_d.csv”,

3) “_sales_feb_2021_d.csv”,

4) “_sales_mar_2021_d.csv”,

5) “sales_jan_feb_mar_2021_d.csv”,

6) “sales_jan_feb_mar_2021_d.xlsx”,

7) “output1.xlsx”,

8) “sales_by_person.csv”,

9) “sales_by_person.xlsx”,

10) “sales_january_2021_d_products_orders.csv”,

11) “sales_january_2021_d_products_orders.xlsx”.

See code file “main_2.ipynb” saved as html file : “main_2.html“

See part of the styling of excel file : output1.xlsxDownload excel file : output1.xlsx