Jak dodać wiersze do ramki danych Timeeries?

Dec 31 2020

Piszę program, który załaduje plik timeseries excel do ramki danych, a następnie utworzę kilka nowych kolumn za pomocą podstawowych obliczeń. Mój program będzie czasami czytać w plikach Excela, których brakuje miesięcy dla niektórych rekordów. W poniższym przykładzie mam miesięczne dane o sprzedaży dla dwóch różnych sklepów. Sklepy są otwarte w różnych miesiącach, więc ich data zakończenia pierwszego miesiąca będzie inna. Ale oba powinny mieć dane na koniec miesiąca do 30.09.2020. W moim pliku Store BBB nie ma zapisów z 31.08.2020 i 30.09.2020, ponieważ w tych miesiącach nie było żadnej sprzedaży.

Sklep Miesiąc otwarty Stan Miasto Data zakończenia miesiąca Sprzedaż
AAA 31.05.2020 NY Nowy Jork 31.05.2020 1000
AAA 31.05.2020 NY Nowy Jork 30.06.2020 5000
AAA 31.05.2020 NY Nowy Jork 30.07.2020 3000
AAA 31.05.2020 NY Nowy Jork 31.08.2020 4000
AAA 31.05.2020 NY Nowy Jork 30.09.2020 2000
BBB 30.06.2020 CT Hartford 30.06.2020 100
BBB 30.06.2020 CT Hartford 30.07.2020 200

W takich przypadkach chcę mieć możliwość dodania dwóch wierszy dla Store BBB na 31.08 i 30.09. W nowych wierszach należy użyć tego samego miesiąca otwarcia, stanu i miasta z ostatniej daty zakończenia miesiąca. Wartość Sales należy ustawić na 0 dla obu nowych wierszy. W tej chwili wykonuję następujące czynności:

  1. Utwórz ramkę danych „MaxDateData” z nazwą sklepu i maksymalną datą zakończenia miesiąca dla każdego sklepu, a także maksymalną datą zakończenia miesiąca dla całej ramki danych szeregu czasowego, nazywam to pole „najnowszą datą”.
Sklep Maksymalna data zakończenia miesiąca Najnowsza data
AAA 30.09.2020 30.09.2020
BBB 30.07.2020 30.09.2020
  1. Utwórz ramkę danych „MostOstatnie” z najnowszym wierszem z głównej ramki danych szeregów czasowych. Aby to zrobić, wykonuję sprzężenie wewnętrzne między ramką danych szeregów czasowych i MaxDateData w Store Name i Max Month End Date.
Sklep Miesiąc otwarty Stan Miasto Data zakończenia miesiąca Sprzedaż Maksymalna data zakończenia miesiąca Najnowsza data
AAA 31.05.2020 NY Nowy Jork 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. Utwórz ramkę danych „RequireBackfill_MostRecent”, używając klauzuli Where do filtrowania w poszukiwaniu sklepów, w których maksymalna data zakończenia miesiąca <najnowsza data. Zobacz kod poniżej. W tym przykładzie tabela RequireBackfill_MostRecent będzie zawierała tylko wiersz przechowujący 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. Następnie używam dwóch zagnieżdżonych pętli for, aby przechodzić między datami, które muszę wypełnić. Wykorzystuje on ramkę danych RequireBackfill_MostRecent, która zawierałaby tylko 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. Następnie dodaję NewData do mojej ramki danych timeseries przy użyciu concat
FullData_List = [MonthlyData,NewData]
FullData=pd.concat(FullData_List)

Cały ten proces działa, ale czy jest na to znacznie wydajniejszy sposób? Może to być kosztowne, gdy zacznę pracować z większymi danymi.

Odpowiedzi

Ferris Dec 31 2020 at 14:08
  1. po prostu spróbuj upsampleindeksu 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. uwaga: 7/30/2020to nie koniec lipca. 7/31/2020jest. więc użycie tej metody 7/30/2020będzie problemem (przekonwertuj datę końcową miesiąca na prawdziwą datę końcową).
JoeFerndz Dec 31 2020 at 16:14

Oto krok po kroku, jak to zrobić. Jeśli masz pytania, daj mi znać.

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)

Wynik tego jest:

   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

Uwaga Dodałem jeszcze jeden wpis z CCC, aby pokazać więcej wariacji.