Wie füge ich Zeilen für einen Zeitreihen-Datenrahmen hinzu?
Ich schreibe ein Programm, das in eine Excel-Datei einer Zeitreihe in einen Datenrahmen geladen wird, und erstelle dann mithilfe einiger grundlegender Berechnungen mehrere neue Spalten. Mein Programm liest manchmal Excel-Dateien ein, denen für einige Datensätze Monate fehlen. Im folgenden Beispiel habe ich monatliche Verkaufsdaten für zwei verschiedene Geschäfte. Die Geschäfte haben in verschiedenen Monaten geöffnet, daher unterscheidet sich das erste Monatsende. Beide sollten jedoch Daten zum Monatsende bis zum 30.09.2020 haben. In meiner Datei hat Store BBB keine Aufzeichnungen für den 31.08.2020 und den 30.09.2020, da in diesen Monaten keine Verkäufe getätigt wurden.
Geschäft | Monat geöffnet | Zustand | Stadt | Monatsende | Der Umsatz |
---|---|---|---|---|---|
AAA | 31.05.2020 | NY | New York | 31.05.2020 | 1000 |
AAA | 31.05.2020 | NY | New York | 30.06.2020 | 5000 |
AAA | 31.05.2020 | NY | New York | 30.07.2020 | 3000 |
AAA | 31.05.2020 | NY | New York | 31.08.2020 | 4000 |
AAA | 31.05.2020 | NY | New 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 |
In solchen Fällen möchte ich zwei Zeilen für Store BBB für 8/31 und 9/30 hinzufügen können. Die neuen Zeilen sollten den gleichen geöffneten Monat, den gleichen Bundesstaat und die gleiche Stadt ab dem letzten Monatsende verwenden. Der Umsatz sollte für beide neuen Zeilen auf 0 gesetzt werden. Ab sofort mache ich folgende Schritte:
- Erstellen Sie den Datenrahmen "MaxDateData" mit dem Geschäftsnamen und dem maximalen Monatsenddatum für jeden Laden sowie dem maximalen Monatsenddatum für den gesamten Zeitreihendatenrahmen. Ich nenne dieses Feld "Letztes Datum".
Geschäft | Maximales Monatsende | Letztes Datum |
---|---|---|
AAA | 30.09.2020 | 30.09.2020 |
BBB | 30.07.2020 | 30.09.2020 |
- Erstellen Sie den Datenrahmen "MostRecent" mit der neuesten Zeile aus dem Hauptzeitreihendatenrahmen. Dazu führe ich eine innere Verknüpfung zwischen dem Zeitreihendatenrahmen und den MaxDateData für den Geschäftsnamen und das maximale Monatsenddatum durch.
Geschäft | Monat geöffnet | Zustand | Stadt | Monatsende | Der Umsatz | Maximales Monatsende | Letztes Datum |
---|---|---|---|---|---|---|---|
AAA | 31.05.2020 | NY | New 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 |
- Erstellen Sie einen Datenrahmen "RequireBackfill_MostRecent" mit einer where-Klausel, um nach Geschäften zu filtern, in denen das maximale Monatsenddatum <das letzte Datum ist. Siehe Code unten. In diesem Beispiel enthält die Tabelle RequireBackfill_MostRecent nur eine Zeile für den Speicher BBB.
RequireBackfill_Stores_MostRecent = MaxDateData.where(MaxDateData['Max Month End Date'] <MaxDateData['Most Recent Date'])
RequireBackfill_MostRecent = MostRecent.merge(RequireBackfill_Stores_MostRecent,how='inner')
- Ich verwende dann zwei verschachtelte for-Schleifen, um die Daten zu durchlaufen, die ich ausfüllen muss. Es nutzt den RequireBackfill_MostRecent-Datenrahmen, der nur Store BBB enthalten würde.
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)
- Anschließend füge ich die NewData mit concat zu meinem Zeitreihen-Datenrahmen hinzu
FullData_List = [MonthlyData,NewData]
FullData=pd.concat(FullData_List)
Dieser ganze Prozess funktioniert, aber gibt es einen viel effizienteren Weg, dies zu tun? Dies kann teuer werden, wenn ich anfange, mit größeren Daten zu arbeiten.
Antworten
upsample
Probieren Sie einfach den DateTime-Index aus. 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()
- Beachten Sie, dass:
7/30/2020
nicht der Endtag im Juli ist.7/31/2020
ist. Die Verwendung dieser Methode7/30/2020
ist daher ein Problem (konvertieren Sie das Monatsenddatum als das wahre Enddatum).
Hier ist der schrittweise Ansatz, um dies zu tun. Wenn Sie Fragen haben, lassen Sie es mich wissen.
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)
Die Ausgabe davon ist:
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
Hinweis Ich habe einen weiteren Eintrag mit CCC hinzugefügt, um Ihnen mehr Variationen zu zeigen.