如何将来自不同EXCEL工作表的数据绘制在同一图表中 [英] Python: how to plot data coming from different excel sheets in the same chart

问题描述

我需要在Python上创建一个交互式图表，从Excel文件的不同工作表获取数据。我试图创建一个for循环来自动获取所有工作表中的数据，但我设法只绘制来自文件最后一个工作表的数据。 我还想用数据来源的工作表的名称创建一个图例。这是我的代码，你能帮我改进一下吗？

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

推荐答案

• 我模拟了一个工作簿来详细演示代码
• 创建图形，然后为每个工作表添加一行

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

这篇关于如何将来自不同EXCEL工作表的数据绘制在同一图表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！