Wie füge ich Zeilen für einen Zeitreihen-Datenrahmen hinzu?

Dec 31 2020

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:

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

Ferris Dec 31 2020 at 14:08
  1. upsampleProbieren 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()
  1. Beachten Sie, dass: 7/30/2020nicht der Endtag im Juli ist. 7/31/2020ist. Die Verwendung dieser Methode 7/30/2020ist daher ein Problem (konvertieren Sie das Monatsenddatum als das wahre Enddatum).
JoeFerndz Dec 31 2020 at 16:14

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.