ALL BUSINESS DATA ANALYSIS MICROSOFT EXCEL TIPS PYTHON

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“_sales_january_2021_d.csv”

sales_feb_2021_d“_sales_feb_2021_d.csv”sales_mar_2021_d“_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

main_2_1

  • read csv file : “_sales_january_2021_d.csv”

main_2_2

  • 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.xlsxpart_of_output1_xlsDownload excel file : output1.xlsx

Comments are closed.

Pin It
AI Chatbot Avatar

By continuing to use the site, you agree to the use of cookies. / Συνεχίζοντας να χρησιμοποιείτε την ιστοσελίδα, συμφωνείτε με τη χρήση των cookies. more information / περισσότερες πληροφορίες

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close