def get_df(sellers, items, products, orders, translation):
    my_sellers = sellers[['seller_id', 'seller_state']].drop_duplicates()
    merged_df = my_sellers.merge(items, on = 'seller_id')
    df = products.merge(merged_df, on = 'product_id')
    df = df[['seller_state', 'product_category_name', 'order_id']].merge(orders[['order_status', 'order_id', 'order_delivered_customer_date']], on = 'order_id')
    df = df.merge(translation, on = "product_category_name")
    df.drop(columns = ['product_category_name'], inplace = True)
    df.rename(columns = {'product_category_name_english': 'category', 'seller_state': 'state', 'order_status': 'status', 'order_delivered_customer_date' : 'year'}, inplace = True)
    df['year'] = df['year'].apply(lambda x: pd.to_datetime(x).year)
    return df



from dash import Dash, dcc, html, Input, Output
app = Dash(__name__)

df = get_df(sellers, items, products, orders, translation)

app.layout = html.Div([
    dcc.Graph(id='sellers-graph'),
    dcc.RangeSlider(
        df['year'].min(),
        df['year'].max(),
        step=None,
        value = [df['year'].min(), df['year'].max()],
        marks={str(year): str(year) for year in df['year'].unique()},
        id='year-slider'
    ),
    dcc.Checklist(
        df['status'],
        df['status'],
        id = 'status-filter'
    ),
    dcc.Dropdown(
        df['state'],
        df['state'][0],
        id = 'state-dropdown'
    )

])



@app.callback(
    Output('sellers-graph', 'figure'),
    [Input('year-slider', 'value'),
     Input('state-dropdown', 'value'),
     Input('status-filter', 'value')])

def update_figure(years_to_display, state_to_display, statuses_filter):
    cur_df = get_df(sellers, items, products, orders, translation)
    cur_df = cur_df.groupby(['state', 'category', 'status']).agg({'order_id' : 'count'}).reset_index()
    df.rename(columns = {'order_id' : 'count'}, inplace = True)
    cur_df = cur_df[cur_df['status'].isin(statuses_filter)]
    if state_to_display != 'All':
        cur_df = cur_df[cur_df.state == state_to_display]
    cur_df = cur_df.loc[cur_df.year <= years_to_display[1]]
    cur_df = cur_df.loc[cur_df.year >= years_to_display[0]]
    fig = px.bar(cur_df, y="state", x="count", color="category",
                 title="Distribution by sales categories", orientation='h', height=300)
    fig.update_layout(transition_duration=500)
    return fig