-1

I have the following code which takes a csv string and saves it as an excel file. I would like to plot a line graph out of this csv, excluding some columns and using the only string column as x axis (all other columns are numbers). Creating this graph in excel is a simple three steps procedure:

  1. Marking the relevant rows
  2. Click on instert
  3. Click on line chart

However, I couldnt find a way to do it in python.

Here is my code:

def save_to_excel(csv, type):
    csv_data_frame = pd.read_csv(StringIO(csv))
    excel_file = pd.ExcelWriter(f"{type}_results.xlsx", engine='openpyxl')
    csv_data_frame.to_excel(excel_file, index=False, sheet_name="results")
    with excel_file:
        pass

Can anyone assist in finding an elegant way to go about this?

PloniStacker
  • 574
  • 4
  • 23
  • 1
    To better understand your question: You want the graph to be plottet with e.g. matplotlib, or to be exported and written to the excel-file? – DataJanitor Jul 26 '23 at 13:38
  • @DataJanitor I'd like to have the graph to be part of the excel file and reside next to the columns. – PloniStacker Jul 26 '23 at 14:17
  • Does this answer your question? [Python Xlsxwriter charting](https://stackoverflow.com/questions/58520584/python-xlsxwriter-charting) – DataJanitor Jul 26 '23 at 14:35

2 Answers2

2

You can use add_chart and insert_chart to accomplish this.

import pandas as pd

df = pd.DataFrame(
    {
        "One": [1, 2, 3, 4],
        "two": [5,6,7,8],
    }
)

excel_file = "Excel file.xlsx"
sheet_name = "Sheet1"
writer = pd.ExcelWriter(excel_file, engine="xlsxwriter")

df.to_excel(writer, sheet_name=sheet_name)

workbook = writer.book
worksheet = writer.sheets[sheet_name]

chart = workbook.add_chart({"type": "line"})

(max_row, max_col) = df.shape

chart.add_series({"values": [sheet_name, 1, 1, max_row, 1]})
chart.add_series({"values": [sheet_name, 1, 2, max_row, 2]})

worksheet.insert_chart(1, 4, chart)

writer.close()

This gives: Screenshot of result: data and chart in Excel

Joshua Shew
  • 618
  • 2
  • 19
jjk
  • 111
  • 9
0

For plotting graphs from a DataFrame, I always use the matplotlib library import. It's a library for plotting data with built-in functions for Python, and it works great for Pandas Dataframes. For plotting a line chart, I recommend this article for its general syntax.

Spencer
  • 1
  • 1