DashPlotly and Pandas choosing excel file via dropdown

I have a folder with xlsx files. I want to use names of these files to populate the dropdown menu from dash plotly. I am stacked with what to begin from.

I read files from folder, list and append. If I put df_list[0] or df_list[1] I can manually choose which excel file to use for dataframe but how do I choose which file to use using dropdown menu? So even if new files will be added to the folder you can just select them via dropdown menu in dash app instead of changing the code?

```
path = os.getcwd()
csv_files = glob.glob(os.path.join(path, "*.xlsx"))



df_list = []

for f in csv_files:
    
    # read the csv file
    df_1 = pd.read_excel(f)
    df_list.append(df_1)
```

Solution

Here is a method that works:

import dash
import dash_core_components as dcc
import dash_html_components as html
import plotly.graph_objects as go
import pandas as pd
import os

# 1. first create sample Excel files
data1 = [{'x': 1, 'y': 2}, {'x': 3, 'y': 4}]
data2 = [{'x': 10, 'y': -6}, {'x': -10, 'y': 8}]
pd.DataFrame(data1).to_excel('file1.xlsx', index=False)
pd.DataFrame(data2).to_excel('file2.xlsx', index=False)

# 2. read the Excel files and add a 'file_name' column
data_frames = []
for file_name in os.listdir():
    if file_name.endswith('.xlsx'):
        dfx = pd.read_excel(file_name)
        dfx['file_name'] = file_name
        data_frames.append(dfx)

# 3. concat the data frames
df = pd.concat(data_frames, axis=0)

excel_files = sorted(df['file_name'].unique().tolist())

# 4. dash code
app = dash.Dash()

fig_dropdown = html.Div([
    dcc.Dropdown(
        id='fig_dropdown',
        options=[{'label': x, 'value': x} for x in excel_files],
        value=excel_files[0]
    )])
fig_plot = html.Div(id='fig_plot')
app.layout = html.Div([fig_dropdown, fig_plot])

@app.callback(
  dash.dependencies.Output('fig_plot', 'children'),
  [dash.dependencies.Input('fig_dropdown', 'value')])
def name_to_figure(fig_name):
    dff = df[df.file_name == fig_name]
    figure = go.Figure()
    figure.add_trace(go.Scatter(y=dff.y, x=dff.x))
    return dcc.Graph(figure=figure)

app.run_server(debug=True, use_reloader=False)

We create 2 sample Excel files (you will not do this).
We read files ending in .xlsx.
Then we turn the files into Pandas dataframes.
Next, we add a column called 'file_name' to filter our data later.
We simply concatenate all the Excel files/ Padas dataframes.
We implement our Dash code.
I used this example from Stack Overflow to help with this part.
Here, we are using a callback function to update our figure.
This is where we use the 'file_name' filter from above.
Alternatively, you can read the Excel files directly instead of creating this filter.