Python: how to plot data coming from different excel sheets in the same chart

Question

I need to create an interactive chart on python taking data from different sheets of an Excel file. I tried to create a for loop to take data from all the sheets automatically, but I manage to graph only data coming from the last sheet of the file. I also would like to create a legend with the names of the sheets where data come from. This is my code, can you help me improving it?

import openpyxl as xl
import os, os.path
import pandas as pd
import plotly.express as px

output_data2=r'C:\\Users\\Desktop\\Vibration data analysis'

wb=xl.load_workbook(os.path.join(output_data2, "RMS Comparison.xlsx"))
for sheet in wb.worksheets:
    if sheet.title != 'Graph':
        df = pd.read_excel(os.path.join(output_data2, "RMS Comparison.xlsx"),sheet_name=sheet.title)
        fig = px.line(df, x='Unnamed: 0', y='Unnamed: 2')
fig.show()

Try to indent `fig.show()`. I believe that your code might work, but it only shows the last plot since it is outside of the `for-loop`. — Carst3n, Feb 11 '22 at 12:58
Thanks, I indented it but it creates different charts, one for each sheet of the Excel file, while I need to have all data shown in the same graph in order to compare them.. — Annachiara, Feb 11 '22 at 13:18
if you want to combine all dataframe to one, which you then want to print try this: After your have created the indvidual `df` write a line in which you append this `df` to `df_list` (which you need to create first). Then as soon as your loop is done concat all the `df` within this list by this line: `df_final = pd.concat(df_list)`. This `df_final` could then be used to create a graph based on the columns of your final dataframe as described [here](https://stackoverflow.com/questions/55822042/how-to-draw-a-multiple-line-chart-using-plotly-express). — Carst3n, Feb 11 '22 at 13:47

score 0 · Accepted Answer · answered Feb 11 '22 at 14:11

have simulated a workbook to demonstrate plotly code
create a figure, then add a line per worksheet

import openpyxl as xl
from pathlib import Path
import pandas as pd
import numpy as np
import plotly.express as px
import plotly.graph_objects as go

f = Path.cwd().joinpath("RMS Comparison.xlsx")

# create an Excel with multiple sheets
with pd.ExcelWriter(f) as writer: 
    for s in "ABCDEF":
        pd.DataFrame({c:np.random.randint(1,10,30) if c[-1]!="0" else np.linspace(1,20, 30) for c in ['Unnamed: 0', 'Unnamed: 2']}).to_excel(writer, sheet_name=s)

# create a figure with a line per worksheet
wb=xl.load_workbook(f)
fig = go.Figure()
for sheet in wb.worksheets:
    if sheet.title != 'Graph':
        df = pd.read_excel(f,sheet_name=sheet.title)
        fig = fig.add_traces(px.line(df, x='Unnamed: 0', y='Unnamed: 2').update_traces(name=sheet.title, showlegend=True).data)

fig.show()

Great thanks a lot, that works perfectly! One last question: how can I assign different colors to each line? — Annachiara, Feb 12 '22 at 16:18
reset the line color `update_traces(name=sheet.title, showlegend=True, line_color=None)` — Rob Raymond, Feb 12 '22 at 16:39

Python: how to plot data coming from different excel sheets in the same chart

1 Answers1

Linked