Learn how you can become a Python programmer in just 12 weeks.

    We respect your privacy. Unsubscribe at anytime.

    How to Create Excel Report Automation with Python

    What will we cover in this tutorial?

    How to create excel report automation with Python. This will be done on the data similar to this.

    The data we work with – 1000 lines like the above – get Excel sheet form link below.

    And we will create Excel reports from Python like these ones.

    The first sheet we will create in Excel from Python.
    The second Excel sheet we create from Python.

    Get direct Download link to JuPyter Notebook and Excel sheet used in YouTube video and follow along.

    Step 1: Load the data in Pandas DataFrame

    Pandas is a fast, powerful, flexible and easy to use open source data analysis and manipulation tool, built on top of the Python programming language.

    Pandas has great integration with Excel – both reading and writing Excel files. We will use that in this tutorial.

    To read the data simply use the read_excel(…).

    import pandas as pd
    data = pd.read_excel("SalesData.xlsx", index_col="Date")

    Which will result in output similar to this.

    	Sales rep	Item	Price	Quantity	Sale
    2020-05-31	Mia	Markers	4	1	4
    2020-02-01	Mia	Desk chair	199	2	398
    2020-09-21	Oliver	Table	1099	2	2198
    2020-07-15	Charlotte	Desk pad	9	2	18
    2020-05-27	Emma	Book	12	1	12

    Step 2: Group the data by Sales rep

    To identify how much each Sales rep has sold, we will group it and calculate the sum.

    repr = data.groupby("Sales rep")
    repr_sales = repr['Sale'].sum()

    Now that is too simple, right?

    The data in rep_sales looks like this.

    Sales rep
    Charlotte     74599
    Emma          65867
    Ethan         40970
    Liam          66989
    Mia           88199
    Noah          78575
    Oliver        89355
    Sophia       103480
    William       80400
    Name: Sale, dtype: int64

    Step 3: Group the data by month, change to month names and sum it

    This is a bit more challenging, as we need to group the dates in months and rename the index to month names.

    Except if you know how to do it, as you will in a moment.

    months = data.groupby(pd.Grouper(freq="M"))
    months_sales = months['Sale'].sum()
    months_sales.index = months_sales.index.month_name()

    You are awesome. See, you use groupby on a Grouper with frequency month (“M”). To use the month name on index and not the last day of month, you reassign the index to use index.month_name().

    This creates the following output where you can see the month names are used.

    January      69990
    February     51847
    March        67500
    April        58401
    May          40319
    June         59397
    July         64251
    August       51571
    September    55666
    October      50093
    November     57458
    December     61941
    Name: Sale, dtype: int64

    Step 4: Create Excel sheet with the data from Step 2 and 3

    This is done by creating an Excel writer.

    writer = pd.ExcelWriter("SalesReport.xlsx")
    repr_sales.to_excel(writer, sheet_name="Sale per rep")
    months_sales.to_excel(writer, sheet_name="Sale per month")

    This will create an Excel sheet of name SalesReport.xlsx, with two sheets Sale per rep and Sale per month, respectively.

    But let’s try to add the two charts.

    Step 5: Adding the charts

    This is a bit more involved. As you see, the above steps have been quite straight forward and not required any high level Python programming skills (or Excel, except you need to know how to open an Excel sheet).

    writer = pd.ExcelWriter("SalesReport.xlsx")
    repr_sales.to_excel(writer, sheet_name="Sale per rep")
    months_sales.to_excel(writer, sheet_name="Sale per month")
    chart = writer.book.add_chart({'type': 'column'})
        'values': '=\'Sale per rep\'!$B$2:$B$10',
        'categories': '=\'Sale per rep\'!$A$2:$A$10',
        'name': "Sale"
    writer.sheets['Sale per rep'].insert_chart("D2", chart)
    chart = writer.book.add_chart({'type': 'column'})
        'values': '=\'Sale per month\'!$B$2:$B$13',
        'categories': '=\'Sale per month\'!$A$2:$A$13',
        'name': "Sale"
    writer.sheets['Sale per month'].insert_chart("D2", chart)

    This will create the sheets we have been waiting for. The charts are dynamic, meaning, that if you change the values the charts will change accordingly.

    First sheet from our results
    Second sheet from out results

    Final remarks

    Remember to see the YouTube video, which explains the material in more depth.

    If you like the above content, please share it on a social media or point a friend in this direction.

    Python Circle

    Do you know what the 5 key success factors every programmer must have?

    How is it possible that some people become programmer so fast?

    While others struggle for years and still fail.

    Not only do they learn python 10 times faster they solve complex problems with ease.

    What separates them from the rest?

    I identified these 5 success factors that every programmer must have to succeed:

    1. Collaboration: sharing your work with others and receiving help with any questions or challenges you may have.
    2. Networking: the ability to connect with the right people and leverage their knowledge, experience, and resources.
    3. Support: receive feedback on your work and ask questions without feeling intimidated or judged.
    4. Accountability: stay motivated and accountable to your learning goals by surrounding yourself with others who are also committed to learning Python.
    5. Feedback from the instructor: receiving feedback and support from an instructor with years of experience in the field.

    I know how important these success factors are for growth and progress in mastering Python.

    That is why I want to make them available to anyone struggling to learn or who just wants to improve faster.

    With the Python Circle community, you can take advantage of 5 key success factors every programmer must have.

    Python Circle
    Python Circle

    Be part of something bigger and join the Python Circle community.

    Leave a Comment