Jak dodać wiersze do ramki danych Timeeries?
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:
- 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 |
- 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 |
- 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')
- 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)
- 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
- po prostu spróbuj
upsample
indeksu 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()
- uwaga:
7/30/2020
to nie koniec lipca.7/31/2020
jest. więc użycie tej metody7/30/2020
będzie problemem (przekonwertuj datę końcową miesiąca na prawdziwą datę końcową).
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.