¿Cómo agregar filas para un marco de datos de series temporales?

Dec 31 2020

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:

  1. 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
  1. 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
  1. 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')
  1. 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) 
  1. 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

Ferris Dec 31 2020 at 14:08
  1. prueba con upsampleel í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()
  1. tenga en cuenta que: 7/30/2020no es el último día de julio. 7/31/2020es. por lo tanto, usar este método 7/30/2020será un problema (convierta la fecha de finalización del mes como la fecha de finalización real).
JoeFerndz Dec 31 2020 at 16:14

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.