¿Cómo agregar filas para un marco de datos de series temporales?
Estoy escribiendo un programa que se cargará en un archivo de Excel de serie temporal en un marco de datos, luego creo varias columnas nuevas usando algunos cálculos básicos. Mi programa a veces leerá archivos de Excel que faltan meses para algunos registros. Entonces, en el ejemplo siguiente, tengo datos de ventas mensuales para dos tiendas diferentes. Las tiendas abren durante diferentes meses, por lo que la fecha de finalización del primer mes será diferente. Pero ambos deberían tener datos de fin de mes hasta el 30/9/2020. En mi archivo, la Tienda BBB no tiene registros para el 31/8/2020 y el 30/9/2020 porque no hubo Ventas durante esos meses.
Tienda | Mes de apertura | Expresar | Ciudad | Fecha de finalización del mes | Ventas |
---|---|---|---|---|---|
AAA | 31/5/2020 | Nueva York | Nueva York | 31/5/2020 | 1000 |
AAA | 31/5/2020 | Nueva York | Nueva York | 30/6/2020 | 5000 |
AAA | 31/5/2020 | Nueva York | Nueva York | 30/7/2020 | 3000 |
AAA | 31/5/2020 | Nueva York | Nueva York | 31/8/2020 | 4000 |
AAA | 31/5/2020 | Nueva York | Nueva York | 30/09/2020 | 2000 |
BBB | 30/6/2020 | Connecticut | Hartford | 30/6/2020 | 100 |
BBB | 30/6/2020 | Connecticut | Hartford | 30/7/2020 | 200 |
Entonces, para casos como este, quiero poder agregar dos filas para Store BBB para 8/31 y 9/30. Las nuevas filas deben usar el mismo mes de apertura, estado y ciudad de la fecha de finalización del mes más reciente. Las ventas deben establecerse en 0 para ambas filas nuevas. A partir de ahora, hago los siguientes pasos:
- Cree el marco de datos "MaxDateData" con el nombre de la tienda y la fecha máxima de finalización del mes para cada tienda y también la fecha máxima de finalización del mes para todo el marco de datos de la serie de tiempo, llamo a este campo "Fecha más reciente".
Tienda | Fecha máxima de finalización del mes | Fecha más reciente |
---|---|---|
AAA | 30/09/2020 | 30/09/2020 |
BBB | 30/7/2020 | 30/09/2020 |
- Cree el marco de datos "MostRecent" con la fila más reciente del marco de datos de la serie temporal principal. Para hacer esto, hago una unión interna entre el marco de datos de la serie temporal y MaxDateData en el nombre de la tienda y la fecha máxima de finalización del mes.
Tienda | Mes de apertura | Expresar | Ciudad | Fecha de finalización del mes | Ventas | Fecha máxima de finalización del mes | Fecha más reciente |
---|---|---|---|---|---|---|---|
AAA | 31/5/2020 | Nueva York | Nueva York | 30/09/2020 | 2000 | 30/09/2020 | 30/09/2020 |
BBB | 30/6/2020 | Connecticut | Hartford | 30/7/2020 | 200 | 30/7/2020 | 30/09/2020 |
- Cree un marco de datos "RequireBackfill_MostRecent" utilizando una cláusula where para filtrar las tiendas donde Max Month End Date <Most Recent Date. Consulte el código a continuación. Entonces, en este ejemplo, la tabla RequireBackfill_MostRecent solo tendrá una fila para la tienda BBB.
RequireBackfill_Stores_MostRecent = MaxDateData.where(MaxDateData['Max Month End Date'] <MaxDateData['Most Recent Date'])
RequireBackfill_MostRecent = MostRecent.merge(RequireBackfill_Stores_MostRecent,how='inner')
- Luego utilizo dos bucles for anidados para recorrer las fechas que necesito completar. Aprovecha el marco de datos RequireBackfill_MostRecent que solo contendría Store BBB.
X=[]
end = MaxDateData['Most Recent Date'][0]
for i in MonthlyData['Month End Date'].unique():
per1 = pd.date_range(start = i, end = end, freq ='M')
for val in per1:
Data=[]
Data = RequireBackfill_MostRecent[["Store"
,"Month Opened"
,"City"
,"State"
]].where(RequireBackfill_MostRecent['Max Month End date']==i).dropna()
Data["Month End Date"]= val
Data["Sales"]= 0
X.append(Data)
NewData = pd.concat(X)
- Luego agrego NewData a mi dataframe de la serie temporal usando concat
FullData_List = [MonthlyData,NewData]
FullData=pd.concat(FullData_List)
Todo este proceso funciona, pero ¿hay una forma mucho más eficiente de hacerlo? Esto podría resultar costoso cuando empiece a trabajar con datos más grandes.
Respuestas
- prueba con
upsample
el índice DateTime. ref: pandas-resample-upsample-last-date-edge-of-data
# group by `Store`
# with `Month End Date` column show be converted to DateTime
group.set_index(['Month End Date']).resample('M').asfreq()
- tenga en cuenta que:
7/30/2020
no es el último día de julio.7/31/2020
es. por lo tanto, usar este método7/30/2020
será un problema (convierta la fecha de finalización del mes como la fecha de finalización real).
Aquí está el enfoque paso a paso para hacer esto. Si tiene alguna pregunta, hágamelo saber.
import pandas as pd
pd.set_option('display.max_columns', None)
c = ['Store','Month Opened','State','City','Month End Date','Sales']
d = [['AAA','5/31/2020','NY','New York','5/31/2020',1000],
['AAA','5/31/2020','NY','New York','6/30/2020',5000],
['AAA','5/31/2020','NY','New York','7/30/2020',3000],
['AAA','5/31/2020','NY','New York','8/31/2020',4000],
['AAA','5/31/2020','NY','New York','9/30/2020',2000],
['BBB','6/30/2020','CT','Hartford','6/30/2020',100],
['BBB','6/30/2020','CT','Hartford','7/30/2020',200],
['CCC','3/31/2020','NJ','Cranbury','3/31/2020',1500]]
df = pd.DataFrame(d,columns = c)
df['Month Opened'] = pd.to_datetime(df['Month Opened'])
df['Month End Date'] = pd.to_datetime(df['Month End Date'])
#select last entry for each Store
df1 = df.sort_values('Month End Date').drop_duplicates('Store', keep='last').copy()
#delete all rows that have 2020-09-30. We want only ones that are less than 2020-09-30
df1 = df1[df1['Month End Date'] != '2020-09-30']
#set target end date to 2020-09-30
df1['Target_End_Date'] = pd.to_datetime ('2020-09-30')
#calculate how many rows to repeat
df1['repeats'] = df1['Target_End_Date'].dt.to_period('M').astype(int) - df1['Month End Date'].dt.to_period('M').astype(int)
#add 1 month to month end so we can start repeating from here
df1['Month End Date'] = df1['Month End Date'] + pd.DateOffset(months =1)
#set sales value as 0 per requirement
df1['Sales'] = 0
#repeat each row by the value in column repeats
df1 = df1.loc[df1.index.repeat(df1.repeats)].reset_index(drop=True)
#reset repeats to start from 0 thru n using groupby cumcouunt
#this will be used to calculate months to increment from month end date
df1['repeats'] = df1.groupby('Store').cumcount()
#update month end date based on value in repeats
df1['Month End Date'] = df1.apply(lambda x: x['Month End Date'] + pd.DateOffset(months = x['repeats']), axis=1)
#set end date to last day of the month
df1['Month End Date'] = pd.to_datetime(df1['Month End Date']) + pd.offsets.MonthEnd(0)
#drop columns that we don't need anymore. required before we concat dfs
df1.drop(columns=['Target_End_Date','repeats'],inplace=True)
#concat df and df1 to get the final dataframe
df = pd.concat([df, df1], ignore_index=True)
#sort values by Store and Month End Date
df = df.sort_values(by=['Store','Month End Date'],ignore_index=True)
print (df)
El resultado de esto es:
Store Month Opened State City Month End Date Sales
0 AAA 2020-05-31 NY New York 2020-05-31 1000
1 AAA 2020-05-31 NY New York 2020-06-30 5000
2 AAA 2020-05-31 NY New York 2020-07-30 3000
3 AAA 2020-05-31 NY New York 2020-08-31 4000
4 AAA 2020-05-31 NY New York 2020-09-30 2000
5 BBB 2020-06-30 CT Hartford 2020-06-30 100
6 BBB 2020-06-30 CT Hartford 2020-07-30 200
7 BBB 2020-06-30 CT Hartford 2020-08-30 0
8 BBB 2020-06-30 CT Hartford 2020-09-30 0
9 CCC 2020-03-31 NJ Cranbury 2020-03-31 1500
10 CCC 2020-03-31 NJ Cranbury 2020-04-30 0
11 CCC 2020-03-31 NJ Cranbury 2020-05-31 0
12 CCC 2020-03-31 NJ Cranbury 2020-06-30 0
13 CCC 2020-03-31 NJ Cranbury 2020-07-31 0
14 CCC 2020-03-31 NJ Cranbury 2020-08-31 0
15 CCC 2020-03-31 NJ Cranbury 2020-09-30 0
Tenga en cuenta que agregué una entrada más con CCC para mostrarle más variaciones.