Bagaimana cara menambahkan baris untuk rentang waktu dataframe?

Dec 31 2020

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:

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

Ferris Dec 31 2020 at 14:08
  1. coba upsamplesaja 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()
  1. perhatikan bahwa: 7/30/2020bukan hari akhir Juli. 7/31/2020aku s. jadi Menggunakan metode ini 7/30/2020akan menjadi masalah (ubah Tanggal Akhir Bulan sebagai tanggal akhir yang sebenarnya).
JoeFerndz Dec 31 2020 at 16:14

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.