散景- pandas 无法从JS读取Excel文件的bytesIO对象 [英] Bokeh - Pandas not able to read bytesIO object of excel file from JS

问题描述

对于几天以来我面临的一些挑战，我需要您的投入.

I need your inputs on some challenges I'm facing since few days.

我的目标是要有一个上传按钮，我可以从中共享2张纸的.xlsx文件.加载此数据并将其读入pandas DataFrame后，我将执行一些pythonic计算/优化代码等，并且得到的结果很少(表格汇总).现在，基于唯一的级别"/组"的数量，我将创建多个选项卡，然后在每个选项卡中显示此汇总结果.在主页上也有一个通用图.

My target is to have a upload button, from which I share .xlsx file with 2 sheets. Once I load this data and read it into pandas DataFrame, I perform some pythonic calculations/optimisation code etc and get few results(tabular summarised). Now based on the number of unique 'levels'/'groups' I will create that many tabs and then display this summarise results in each tab. Also have a general(common) plot in the main page.

下面是我的努力(不是我的，而是社区的:)):

Below is my effort(not mine but community's :) ) :

## Load library
###########################################################################
import pandas as pd
import numpy as np
from xlrd import XLRDError
import io
import base64
import os

from bokeh.layouts import row, column, widgetbox, layout
from bokeh.models import ColumnDataSource, CustomJS, LabelSet
from bokeh.models.widgets import Button, Div, TextInput, DataTable, TableColumn, Panel, Tabs
from bokeh.io import curdoc
from bokeh.plotting import figure
###########################################################################
## Upload Button Widget
file_source = ColumnDataSource({'file_contents':[], 'file_name':[]})
cds_test = ColumnDataSource({'v1':[], 'v2':[]})


def file_callback(attr,old,new):

    global tabs, t
    print('filename:', file_source.data['file_name'])
    raw_contents = file_source.data['file_contents'][0]
    prefix, b64_contents = raw_contents.split(",", 1)
    file_contents = base64.b64decode(b64_contents)
    file_io = io.BytesIO(file_contents)

# Here it errors out when trying '.xlsx' file but work for .csv Any Idea ???? 
    #df1 = pd.read_excel(file_io, sheet = 'Sheet1')
    #df2 = pd.read_excel(file_io, sheet = 'Sheet2')

    # call some python functions for analysis
    # returns few results
    # for now lets assume main_dt has all the results of analysis


    df1 = pd.read_excel(file_path, sheet_name = 'Sheet1')
    df2 = pd.read_excel(file_path, sheet_name = 'Sheet2')

    main_dt = pd.DataFrame({'v1':df1['v1'], 'v2': df2['v2']})
    level_names = main_dt['v2'].unique().tolist()

    sum_v1_level = []
    for i in level_names:
        csd_temp = ColumnDataSource(main_dt[main_dt['v2'] == i])
        columns = [TableColumn(field=j, title="First") for j in main_dt.columns] 
        dt = DataTable(source = csd_temp, columns = columns, width=400, height=280)
        temp = Panel(child = dt, title = i)
        t.append(temp)
        sum_v1_level.append(sum(csd_temp.data['v1']))

    tabs = Tabs(tabs = t)
    cds_plot = ColumnDataSource({'x':level_names, 'y':sum_v1_level})

    p_o = figure(x_range = level_names, plot_height=250, title="Plot")
    p_o.vbar(x='x', top = 'y', width=0.9, source = cds_plot)
    p_o.xgrid.grid_line_color = None
    p_o.y_range.start = 0
    p_o.y_range.end = max(sum_v1_level)*1.2
    labels_o = LabelSet(x='x', y = 'y', text='y', level='glyph',
        x_offset=-13.5, y_offset=0, render_mode='canvas', source = cds_plot)
    p_o.add_layout(labels_o)

    curdoc().add_root(p_o)
    curdoc().add_root(tabs)
    print('successful upload')

file_source.on_change('data', file_callback)

button = Button(label="Upload Data", button_type="success")
# when butotn is clicked, below code in CustomJS will be called
button.callback = CustomJS(args=dict(file_source=file_source), code = """
function read_file(filename) {
    var reader = new FileReader();
    reader.onload = load_handler;
    reader.onerror = error_handler;
    // readAsDataURL represents the file's data as a base64 encoded string
    reader.readAsDataURL(filename);
}

function load_handler(event) {
    var b64string = event.target.result;
    file_source.data = {'file_contents' : [b64string], 'file_name':[input.files[0].name]};
    file_source.trigger("change");
}

function error_handler(evt) {
    if(evt.target.error.name == "NotReadableError") {
        alert("Can't read file!");
    }
}

var input = document.createElement('input');
input.setAttribute('type', 'file'); 
input.onchange = function(){
    if (window.FileReader) {
        read_file(input.files[0]);
    } else {
        alert('FileReader is not supported in this browser');
    }
}
input.click();
""")

Bdw:有什么方法可以抑制此警告，或者我做错了吗?(在将读取列插入CDS时)

Bdw : Any way to suppress this warning or am I doing it the wrong way ?(while inserting read column into CDS)

BokehUserWarning:ColumnDataSource的列必须具有相同的长度.当前长度:('v1'，19)，('v2'，0)

BokehUserWarning: ColumnDataSource's columns must be of the same length. Current lengths: ('v1', 19), ('v2', 0)

2.添加到布局

curdoc().title = 'Test Joel'
curdoc().add_root(button)

下面是输出:

这是原始数据: 注意:此处共享的所有数据都是伪数据，实际情况下具有更多的工作表和更多的尺寸.

This was the original data : Note : All data shared here are dummy and real case has more sheets and more dimensions .

所以总结一下:

1. 无法通过上传按钮读取.xlsx文件

在按钮回调函数本身中执行所有步骤是否正确?

Is it correct to do all steps in the button callback function itself ?

推荐答案

对于那些您需要进一步参考的人，请参考以下线程:这是一种具有上载按钮来处理.xlsx文件的解决方案.这是针对python3

For those you would infuture reference this thread : Here is a solution to have a upload button to handle .xlsx file . This was for python3

我只共享主要数据处理代码.像上面一样休息一切.

I am sharing only the main data handling code. Rest everything is as it is above.

import pandas as pd
import io
import base64


def file_callback_dt1(attr,old,new):
    print('filename:', file_source_dt1.data['file_name'])
    raw_contents = file_source_dt1.data['file_contents'][0]
    prefix, b64_contents = raw_contents.split(",", 1)
    file_contents = base64.b64decode(b64_contents)
    file_io = io.BytesIO(file_contents)
    excel_object = pd.ExcelFile(file_io, engine='xlrd')
    dt_1 = excel_object.parse(sheet_name = 'Sheet1', index_col = 0)
    # rest is upto you :)

这篇关于散景- pandas 无法从JS读取Excel文件的bytesIO对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！