# -*- coding: utf-8 -*- """ Created on Wed Sep 27 17:45:59 2023 env: dardos2 @author: forti -Grafica training y validation sets para comparar eficiencia de pronostico - Grafica con plotly y matplotlib. -Genera df_reindexed con periodo semanal -Calcula sigma y safety stock - Calcula MOVING AVERAGE ( descomentar) """ import numpy as np import pandas as pd # import matplotlib.pyplot as plt import plotly.io as pio import plotly.express as px from darts import TimeSeries from darts.models import RandomForest # from darts.models import NaiveDrift # from darts.models import MovingAverageFilter import streamlit as st # import locale from darts.metrics import mae, rmse from PIL import Image ########################################################################### @st.cache_data def convert_df(df_new): # IMPORTANT: Cache the conversion to prevent computation on every rerun return df_new.to_csv().encode('utf-8') ########################################################################### pio.renderers.default = "browser" st.title(" 🏪 Super Playitas") st.header("🤖 Pronósticos con Inteligencia Artificial 🤖") df = None uploaded_file = st.file_uploader("Seleccione su producto", type='xlsx') if uploaded_file is not None: df = pd.read_excel(uploaded_file) # st.write(data) if df is not None: ################ Limpieza de datos ########################### # df = pd.read_excel('marlboro-20.xlsx') # df = pd.read_excel('cocacola-600.xlsx') df.drop([0, 1, 2, 3, 4, 5], inplace=True) df.columns = df.iloc[0] df.drop(6, inplace=True) ########################################################### # Set to Spanish or English locale #locale.setlocale(locale.LC_TIME, "en_US.UTF-8") # locale.setlocale(locale.LC_TIME, "es_US.UTF-8") # Create a dictionary mapping Spanish month abbreviations to English month_dict = { 'Ene': 'Jan', 'Feb': 'Feb', 'Mar': 'Mar', 'Abr': 'Apr', 'May': 'May', 'Jun': 'Jun', 'Jul': 'Jul', 'Ago': 'Aug', 'Sep': 'Sep', 'Oct': 'Oct', 'Nov': 'Nov', 'Dic': 'Dec' } # Assume df is your DataFrame and 'date' is the column with dates df['Fecha'] = df['Fecha'].replace(month_dict, regex=True) # convert the date column into datetime df['Fecha'] = pd.to_datetime(df['Fecha'], format='%d/%b/%Y %I:%M %p', errors='coerce') # rename columns df.columns.values[2] = 'Cantidad-1' df.columns.values[5] = 'Cantidad-2' df.columns.values[8] = 'Cantidad-3' df['Cantidad-2'] = pd.to_numeric(df['Cantidad-2'], errors='coerce') # lee el ultimo valor de la columna de cantidad-3 df['Cantidad-3'] = pd.to_numeric(df['Cantidad-3'], errors='coerce') inventario_neto = df['Cantidad-3'].iloc[-1] # drop NaN rows from column "Cantidad-2" df.dropna(subset=['Cantidad-2'], inplace=True) # drop negative values from "Cantidad-2" df = df[df['Cantidad-2'] >= 0] # Create a new column 'Fecha_sin_hora' with just the date df['Fecha_sin_hora'] = df['Fecha'].dt.date # Group by 'Fecha_sin_hora' and sum 'Cantidad-2' df_sum = df.groupby('Fecha_sin_hora')['Cantidad-2'].sum().reset_index() ############################################################### # # Create a new column 'Mes' with just the month and year # df['Mes'] = df['Fecha_sin_hora'].dt.to_period('M') # # Group by 'Mes' and sum 'Cantidad-2' # df_sum = df.groupby('Mes')['Cantidad-2'].sum().reset_index() ############################################################### # df_sum.drop(737, inplace=True) # df_sum = df_sum.iloc[699:800] ################################################################ ######## Create a DataFrame with a new date range ############################ # Create the date range # new_date_range = pd.date_range(start='2023-03-12', end='2023-08-11') new_date_range = pd.date_range(start=df_sum.iloc[0,0], end=df_sum.iloc[-1,0]) # Convert the date range to a DataFrame #df_3 = pd.DataFrame({'Fecha': new_date_range}) ################################################################## df_sum.set_index('Fecha_sin_hora', inplace=True) # Re-index with a new date-range for each group #df_reindexed = df_sum.apply(lambda x: x.set_index('Fecha_sin_hora').reindex(new_date_range)) df_reindexed = df_sum.apply(lambda x: x.reindex(new_date_range)) # Reset the index #df_reindexed = df_reindexed.reset_index(level=0, drop=True).reset_index() # Fill missing sales values with 0 df_reindexed['Cantidad-2'] = df_reindexed['Cantidad-2'].fillna(0) ################################################################# # Create a new column 'Semana' df_reindexed.reset_index(inplace=True) df_reindexed = df_reindexed.rename(columns={'index': 'Fecha'}) df_reindexed['Semana'] = df_reindexed['Fecha'].dt.to_period('W') # Group by 'Semana' and sum 'Cantidad-2' df_reindexed = df_reindexed.groupby('Semana')['Cantidad-2'].sum().reset_index() # rename columns df_reindexed.columns.values[0] = 'Fecha' # ax = df_reindexed.plot(label='Historial') # plt.show() #hacer fecha el indice # df_reindexed.set_index('Fecha', inplace=True) df_reindexed['Fecha'] = df_reindexed['Fecha'].astype(str) df_reindexed['Fecha'] = df_reindexed['Fecha'].str.split('/').str[0] # usar para pronostico de las ultimas dos semanas. fecha automatizada # dt = pd.to_datetime(df_sum.index[1]) # dt = dt + pd.Timedelta(days=1) ################################################################# # Convert datetime to string to be able to use DARTS #df_sum['Fecha_sin_hora'] = df_sum['Fecha_sin_hora'].astype(str) ########## Reset the index. Use only for daily df_reindexed ################## # df_reindexed['Fecha'] = df_reindexed.index # df_reindexed.reset_index(drop=True, inplace=True) # df_reindexed = df_reindexed[['Fecha', 'Cantidad-2']] # Create a TimeSeries object, specifying the time and value columns series = TimeSeries.from_dataframe(df_reindexed, "Fecha", 'Cantidad-2') # series = TimeSeries.from_dataframe(df_reindexed, freq="W") # Assuming df_reindexed is your DataFrame and it's indexed by date # series = TimeSeries.from_times_and_values(times=df_reindexed, values=df_reindexed['Cantidad-2']) # Set aside the last 16 days as a validation series train, val = series[:-15], series[-15:] #################### Train Forecasting Models ###################### # Train baseline model # model_1 = NaiveDrift() # model_1.fit(series) # Train ML model model_2 = RandomForest(lags=20) #25 #55 model_2a = RandomForest(lags=20) # model_2.fit(train) model_2.fit(series) model_2a.fit(train) # MOVING AVERAGE # model_3 = MovingAverageFilter(window=15) # filtered_series = model_3.filter(series) #################################################################### ################## Predict with Forecasting Models ################# # Predict using baseline model # prediction_1 = model_1.predict(15) # Predict using RandomForest # prediction_2 = model_2.predict(len(val)) prediction_2 = model_2.predict(15) prediction_2a = model_2a.predict(15) # df_4 = pd.DataFrame(prediction_2.pd_dataframe()) # df_4 = df_4.reset_index(drop=True) ################################################################### ######## plot forecast from validation set ######################## # converts datetime objects to pandas dataframes series_df = series.pd_dataframe() prediction_2_df = prediction_2.pd_dataframe() prediction_2a_df = prediction_2a.pd_dataframe() val_df = val.pd_dataframe() # series.plot(label="Historial de Ventas") # prediction_2a.plot(label="Pronóstico", low_quantile=0.05, high_quantile=0.95) # filtered_series.plot(label="Historial de Ventas") # series.plot(label="Historial de Ventas") # plt.xlabel('Fecha') # plt.ylabel('Ventas') # plt.legend() # plt.subplots_adjust(bottom=0.2) # plt.savefig("producto_forecast_2.png", dpi=200) # n=115 # series_df = series_df.tail(n) # ax = series_df.plot(label='Historial') # prediction_2_df.plot(ax=ax, label='Pronostico') # plt.legend() # plt.show() ############################################################################### #########################Cacula std del forecast ############################ error = prediction_2a_df["Cantidad-2"] - val_df["Cantidad-2"] mean = error.mean() diff = error - mean diff_squared = diff.apply(lambda x:x**2).sum() std = np.sqrt(diff_squared/(len(diff)-1)) safety_stock = 1.96*std ############################################################################### ########################## metricas ################################ mae_1 = mae(val, prediction_2a) # mape_1 = mape(val, prediction_2a) rmse_1 = rmse(val, prediction_2a) ##################### Pronostico final ################################## promedio = prediction_2_df.mean() pronostico_promedio = promedio + safety_stock # semana = prediction_2_df.index[0] #redondea y los numeros del df. si la borro siguie funcionando todo prediction_2_df = prediction_2_df.round().astype(int) safety_stock = safety_stock.round() ##################Grafica con plotly########################################### # df_borrar = df_sum +1 # df_borrar = df_borrar.iloc[800:844] figure_01=px.line(series_df,y="Cantidad-2",) figure_01.layout.update(title_text=None,xaxis_rangeslider_visible=True) figure_02=px.line(prediction_2_df,y="Cantidad-2") figure_02.update_traces(line=dict(color='red')) figure_03=px.scatter(series_df,y="Cantidad-2",) figure_03.update_traces(marker=dict(color='DodgerBlue')) figure_04=px.scatter(prediction_2_df,y="Cantidad-2",) figure_04.update_traces(marker=dict(color='red')) # st.plotly_chart(figure_01) # Add figure_02 data to figure_01 for trace in figure_02.data: figure_01.add_trace(trace) # Add figure_03 data to figure_01 for trace in figure_03.data: figure_01.add_trace(trace) for trace in figure_04.data: figure_01.add_trace(trace) st.subheader(":blue[Historial] y :red[Pronóstico] de Ventas Semanal") st.plotly_chart(figure_01) ############################################################################### if mae_1 < 7: st.subheader("Pronóstico de las proximas 15 semanas:") prediction_2_total = prediction_2_df + safety_stock prediction_2_total.index = pd.to_datetime(prediction_2_total.index).date prediction_2_total.rename(columns={'Cantidad-2': 'Nivel de Inventario'}, inplace=True) prediction_2_total['Pronostico'] = prediction_2_df['Cantidad-2'] # Create an array with the number repeated the desired number of times stock = np.full((len(prediction_2_total), 1), safety_stock) # Convert the array to a DataFrame stock_df = pd.DataFrame(stock, columns=['safety']).astype(int) prediction_2_total['Stock de Seguridad'] = stock_df['safety'].values prediction_2_total = prediction_2_total[['Pronostico', 'Stock de Seguridad', 'Nivel de Inventario']] prediction_2_total['Nivel de Inventario'] = prediction_2_total['Nivel de Inventario'].round().astype(int) st.table(prediction_2_total) else: st.subheader("Pronóstico de las proximas 15 semanas:") pronostico_promedio = pronostico_promedio.iloc[0] # Create an array with the number repeated the desired number of times pronostico_promedio = np.full((len(prediction_2_df), 1), pronostico_promedio) # Convert the array to a DataFrame pronostico_promedio_df = pd.DataFrame(pronostico_promedio, columns=['Nivel de Inventario']) prediction_2_df["Nivel de Inventario"] = pronostico_promedio_df['Nivel de Inventario'].values # Create an array with the number repeated the desired number of times stock = np.full((len(prediction_2_df), 1), safety_stock) # Convert the array to a DataFrame stock_df = pd.DataFrame(stock, columns=['safety']).astype(int) prediction_2_df['Stock de Seguridad'] = stock_df['safety'].values prediction_2_total = prediction_2_df prediction_2_total.index = pd.to_datetime(prediction_2_total.index).date prediction_2_total.rename(columns={'Cantidad-2': 'Pronostico'}, inplace=True) prediction_2_total = prediction_2_total[['Pronostico', 'Stock de Seguridad', 'Nivel de Inventario']] prediction_2_total['Nivel de Inventario'] = prediction_2_total['Nivel de Inventario'].round().astype(int) st.table(prediction_2_total) ################################################################################# csv = convert_df(prediction_2_total) st.download_button( label="Descargar datos ⤵", data=csv, file_name='pronostico-producto.csv', mime='text/csv', ) cantidad = round(prediction_2_total.iloc[0,0] - inventario_neto) if cantidad<0: cantidad =0 # st.text("Cantidad =" + str(cantidad)) st.subheader("Cantidad de producto a ordenar (por semana): ") # st.text("Cantidad = Pronostico - Inventario Neto") st.metric(label=":blue[Cantidad = Nivel de Inventario - Inventario Neto]", value=cantidad) st.write('\n') st.write('\n') st.write('\n') st.write('\n') st.write('\n') st.write('\n') st.write('\n') st.write('\n') st.write('\n') image = Image.open("magna.png") st.image(image, caption='Inteligencia Artificial para tu negocio') st.markdown("Contáctanos: www.magna-machina.com") else: st.subheader("No ha seleccionado su producto.") st.write('\n') st.write('\n') st.write('\n') st.write('\n') st.write('\n') st.write('\n') st.write('\n') st.write('\n') st.write('\n') st.write('\n') st.write('\n') st.write('\n') st.write('\n') st.write('\n') st.write('\n') st.write('\n') image = Image.open("magna.png") st.image(image, caption='Inteligencia Artificial para tu negocio') st.markdown("Contáctanos: www.magna-machina.com")