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

    We respect your privacy. Unsubscribe at anytime.

    CSV GroupBy Processing to Excel with Charts using pandas (Python)

    How to create Excel charts from a CSV file in Python

    You will learn how to read CSV data to Excel using Python. It will be a bit more, you will read the CSV data from GitHub, then group the data by unique values in a column and sum it. Then how to group and sum data on a monthly basis. Finally, how to export this into a multiple-sheet Excel document with the chart.

    In this tutorial, you will learn:

    • Reading CSV data to Excel: Understand how to read CSV data and export it to an Excel file using Python.
    • Grouping and Summing Data: Learn how to group data by unique values in a column and calculate the sum of corresponding values.
    • Monthly Grouping and Summing: Explore how to group and sum data on a monthly basis using date-related information.
    • Exporting to Multiple-Sheet Excel: Discover how to export the processed data into a multi-sheet Excel document, allowing for better organization and presentation.
    • Adding Charts: Learn how to incorporate charts into the Excel document to visualize the data effectively.

    By the end of the tutorial, you will be proficient in reading CSV data, performing grouping and summing operations, and exporting the data to Excel. You will also be able to create charts to enhance data visualization and presentation within the Excel file.

    Step 1: Get and inspect the data

    We can use pandas to read the CSV data (see more about CSV files here).

    import pandas as pd
    url = 'https://raw.githubusercontent.com/LearnPythonWithRune/LearnPython/main/files/SalesData.csv'
    data = pd.read_csv(url, delimiter=';', parse_dates=True, index_col='Date')
    print(data.head())
    

    This will read our data directly from GitHub and show the first few lines.

                Sales rep        Item  Price  Quantity  Sale
    Date                                                    
    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
    

    This data shows different sales representatives and a list of their sales in 2020.

    Step 2: Use GroupBy to get sales of each to represent and monthly sales

    It is easy to group data by columns. The below code will first group all the Sales reps and sum their sales. Second, it will group the data in months and sum it up.

    repr_sales = data.groupby("Sales rep").sum()['Sale']
    print(repr_sales)
    monthly_sale = data.groupby(pd.Grouper(freq='M')).sum()['Sale']
    monthly_sale.index = monthly_sale.index.month_name()
    print(monthly_sale)
    

    This gives.

    Sales rep
    Charlotte     74599
    Emma          65867
    Ethan         40970
    Liam          66989
    Mia           88199
    Noah          78575
    Oliver        89355
    Sophia       103480
    William       80400
    Name: Sale, dtype: int64
    
    Date
    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 3: Create a multiple-sheet Excel document with charts

    Now for the export magic.

    workbook = pd.ExcelWriter("SalesReport.xlsx")
    repr_sales.to_excel(workbook, sheet_name='Sales per rep')
    monthly_sale.to_excel(workbook, sheet_name='Monthly')
    chart1 = workbook.book.add_chart({'type': 'column'})
    # Configure the first series.
    chart1.add_series({
        'name':       'Sales per rep',
        'categories': '=\'Sales per rep\'!$A$2:$A$10',
        'values':     '=\'Sales per rep\'!$B$2:$B$10',
    })
    workbook.sheets['Sales per rep'].insert_chart('D2', chart1)
    chart1 = workbook.book.add_chart({'type': 'column'})
    # Configure the first series.
    chart1.add_series({
        'name':       'Monthly sales',
        'categories': '=Monthly!$A$2:$A$13',
        'values':     '=Monthly!$B$2:$B$13',
    })
    workbook.sheets['Monthly'].insert_chart('D2', chart1)
    workbook.close()
    

    This will create an Excel document called SalesReport.xlsx in your working directory.

    To get a detailed explanation see the video at the top of the post.

    Want to learn more?

    In the next project you will create your own reinforcement model from scratch in Python.

    Want to learn more Python, then this is part of an 8 hours FREE video course with full explanations, projects on each level, and guided solutions.

    The course is structured with the following resources to improve your learning experience.

    • 17 video lessons teaching you everything you need to know to get started with Python.
    • 34 Jupyter Notebooks with lesson code and projects.
    • A FREE eBook to support your Python learning.

    See the full FREE course page here.

    If you instead want to learn more about Machine Learning. Do not worry.

    Then check out my Machine Learning with Python course.

    • 15 video lessons teaching you all aspects of Machine Learning
    • 30 JuPyter Notebooks with lesson code and projects
    • 10 hours of FREE video content to support your learning journey.

    Go to the course page for details.

    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.

    3 thoughts on “CSV GroupBy Processing to Excel with Charts using pandas (Python)”

    1. in windows 10 i get error when trying the example above:
      AttributeError: ‘OpenpyxlWriter’ object has no attribute ‘add_chart’

      using pytho3.10 64bit

      Nevertheless, I like your style of presenting python, and the hard work put into all the tutorials. A big thank you! I have a long way to go, but I am enjoying it so far!

      Reply
    2. further to my earlier message where i noted this doesn’t work in win doze environ. I am glad to advise that it does work fine under macos big sur (arm64), also running python3.10 (but natively under arm64)

      Reply
      • Hi,
        There can be two things.
        Most likely it just needs to install the XlsxWriter.
        This is done as follows:

        pip install xlsxwriter

        This should be run in a terminal in the environment you use.

        If this does not solve the problem, try to update the pandas package

        pip install –upgrade pandas

        This will update it to the newest version.

        Hope it helps
        Rune

        Reply

    Leave a Comment