Como adicionar linhas para um dataframe de série temporal?

Question

Estou escrevendo um programa que carregará em um arquivo de excel de série de tempo em um dataframe, então crio várias novas colunas usando alguns cálculos básicos. Meu programa às vezes lê em arquivos do Excel que estão faltando meses para alguns registros. Portanto, no exemplo abaixo, tenho dados de vendas mensais para duas lojas diferentes. As lojas abrem durante meses diferentes, portanto, a data de término do primeiro mês será diferente. Mas ambos devem ter dados de final de mês até 30/09/2020. No meu arquivo, Loja BBB não tem registros para 31/08/2020 e 30/09/2020 porque não houve vendas durante esses meses.

Loja	Mês de Abertura	Estado	Cidade	Data Final do Mês	Vendas
AAA	31/05/2020	Nova Iorque	Nova york	31/05/2020	1000
AAA	31/05/2020	Nova Iorque	Nova york	30/06/2020	5000
AAA	31/05/2020	Nova Iorque	Nova york	30/07/2020	3000
AAA	31/05/2020	Nova Iorque	Nova york	31/08/2020	4000
AAA	31/05/2020	Nova Iorque	Nova york	30/09/2020	2000
BBB	30/06/2020	CT	Hartford	30/06/2020	100
BBB	30/06/2020	CT	Hartford	30/07/2020	200

Portanto, para qualquer instância como essa, quero poder adicionar duas linhas para a Loja BBB para 31/8 e 30/9. As novas linhas devem usar o mesmo mês de abertura, estado e cidade da data de encerramento do mês mais recente. As vendas devem ser definidas como 0 para ambas as novas linhas. A partir de agora, eu realizo as seguintes etapas:

1. Crie Dataframe "MaxDateData" com o nome da loja e a data de término máxima do mês para cada loja e também a data de término máxima do mês para todo o quadro de dados da série temporal. Chamo esse campo de "Data mais recente".

Loja	Data Máxima de Fim do Mês	Data Mais Recente
AAA	30/09/2020	30/09/2020
BBB	30/07/2020	30/09/2020

1. Crie o Dataframe "MostRecent" com a linha mais recente do dataframe da série temporal principal. Para fazer isso, faço uma junção interna entre o dataframe da série temporal e MaxDateData no nome da loja e na data máxima de término do mês.

Loja	Mês de Abertura	Estado	Cidade	Data Final do Mês	Vendas	Data Máxima de Fim do Mês	Data Mais Recente
AAA	31/05/2020	Nova Iorque	Nova york	30/09/2020	2000	30/09/2020	30/09/2020
BBB	30/06/2020	CT	Hartford	30/07/2020	200	30/07/2020	30/09/2020

1. Crie um dataframe "RequireBackfill_MostRecent" usando uma cláusula where para filtrar as lojas em que Max Month End Date <Most Recent Date. Veja o código abaixo. Portanto, neste exemplo, a tabela RequireBackfill_MostRecent terá apenas uma linha para a loja 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. Em seguida, uso dois loops for aninhados para percorrer as datas que preciso preencher. Ele aproveita o dataframe RequireBackfill_MostRecent, que conteria apenas o 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. Eu, então, adiciono NewData ao meu dataframe da série temporal usando concat

FullData_List = [MonthlyData,NewData]
FullData=pd.concat(FullData_List)

Todo esse processo funciona, mas existe uma maneira muito mais eficiente de fazer isso? Isso pode se tornar caro quando eu começar a trabalhar com dados maiores.

Answer 1

1. apenas tente upsampleo í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. observe que: 7/30/2020não é o dia do fim de julho. 7/31/2020é. portanto, usar esse método 7/30/2020será um problema (converta a Data de término do mês como a data de término real).

Answer 2

Aqui está a abordagem passo a passo para fazer isso. Se você tiver dúvidas, deixe-me 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)

O resultado disso é:

   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

Observe que adicionei mais uma entrada com CCC para mostrar mais variação.