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

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()

CodePudding user response：

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()