Bagaimana cara menambahkan baris untuk rentang waktu dataframe?
Saya menulis program yang akan memuat file excel rangkaian waktu ke dalam kerangka data, lalu saya membuat beberapa kolom baru menggunakan beberapa perhitungan dasar. Program saya kadang-kadang akan membaca di file excel yang hilang berbulan-bulan untuk beberapa catatan. Jadi dalam contoh di bawah ini saya memiliki data penjualan bulanan untuk dua toko yang berbeda. Toko buka selama bulan yang berbeda, jadi tanggal akhir bulan pertama mereka akan berbeda. Tetapi keduanya harus memiliki data akhir bulan hingga 30/9/2020. Di file saya, Store BBB tidak memiliki catatan untuk 8/31/2020 dan 9/30/2020 karena tidak ada Penjualan selama bulan-bulan tersebut.
Toko | Bulan Dibuka | Negara | Kota | Tanggal Akhir Bulan | Penjualan |
---|---|---|---|---|---|
AAA | 31/5/2020 | NY | New York | 31/5/2020 | 1000 |
AAA | 31/5/2020 | NY | New York | 30/6/2020 | 5.000 |
AAA | 31/5/2020 | NY | New York | 30/7/2020 | 3000 |
AAA | 31/5/2020 | NY | New York | 31/8/2020 | 4000 |
AAA | 31/5/2020 | NY | New York | 30/9/2020 | 2000 |
BBB | 30/6/2020 | CT | Hartford | 30/6/2020 | 100 |
BBB | 30/6/2020 | CT | Hartford | 30/7/2020 | 200 |
Jadi untuk setiap contoh seperti ini, saya ingin dapat menambahkan dua baris untuk Store BBB untuk 8/31 dan 9/30. Baris baru harus menggunakan Bulan Dibuka, Negara Bagian dan Kota yang sama dari tanggal akhir bulan terbaru. Penjualan harus disetel ke 0 untuk kedua baris baru. Mulai sekarang, saya melakukan langkah-langkah berikut:
- Buat Dataframe "MaxDateData" dengan nama toko dan Tanggal Akhir Bulan maksimum untuk setiap Toko dan juga Tanggal Akhir Bulan maksimum untuk seluruh frame data deret waktu, saya beri nama bidang ini "Tanggal Terbaru".
Toko | Tanggal Akhir Bulan Maks | Tanggal Terbaru |
---|---|---|
AAA | 30/9/2020 | 30/9/2020 |
BBB | 30/7/2020 | 30/9/2020 |
- Buat Dataframe "MostRecent" dengan baris terbaru dari dataframe deret waktu utama. Untuk melakukan ini, saya melakukan gabungan dalam antara kerangka data deret waktu dan MaxDateData pada Nama Toko dan Tanggal Akhir Bulan Maks.
Toko | Bulan Dibuka | Negara | Kota | Tanggal Akhir Bulan | Penjualan | Tanggal Akhir Bulan Maks | Tanggal Terbaru |
---|---|---|---|---|---|---|---|
AAA | 31/5/2020 | NY | New York | 30/9/2020 | 2000 | 30/9/2020 | 30/9/2020 |
BBB | 30/6/2020 | CT | Hartford | 30/7/2020 | 200 | 30/7/2020 | 30/9/2020 |
- Buat kerangka data "RequireBackfill_MostRecent" menggunakan klausa where untuk memfilter penyimpanan di mana Tanggal Akhir Bulan Maks <Tanggal Terbaru. Lihat kode di bawah ini. Jadi dalam contoh ini, tabel RequireBackfill_MostRecent hanya akan memiliki baris untuk menyimpan BBB.
RequireBackfill_Stores_MostRecent = MaxDateData.where(MaxDateData['Max Month End Date'] <MaxDateData['Most Recent Date'])
RequireBackfill_MostRecent = MostRecent.merge(RequireBackfill_Stores_MostRecent,how='inner')
- Saya kemudian menggunakan dua bersarang untuk loop untuk siklus melalui tanggal yang perlu saya isi. Ini memanfaatkan dataframe RequireBackfill_MostRecent yang hanya akan berisi Simpan 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)
- Saya kemudian menambahkan NewData ke dataframe rangkaian waktu saya menggunakan concat
FullData_List = [MonthlyData,NewData]
FullData=pd.concat(FullData_List)
Keseluruhan proses ini berhasil, tetapi adakah cara yang jauh lebih efisien untuk melakukan ini? Ini bisa menjadi mahal ketika saya mulai bekerja dengan data yang lebih besar.
Jawaban
- coba
upsample
saja indeks 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()
- perhatikan bahwa:
7/30/2020
bukan hari akhir Juli.7/31/2020
aku s. jadi Menggunakan metode ini7/30/2020
akan menjadi masalah (ubah Tanggal Akhir Bulan sebagai tanggal akhir yang sebenarnya).
Inilah pendekatan langkah demi langkah untuk melakukan ini. Jika ada pertanyaan, beri tahu saya.
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)
Output dari ini adalah:
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
Catatan Saya menambahkan satu entri lagi dengan CCC untuk menunjukkan kepada Anda lebih banyak variasi.