Làm cách nào để thêm hàng cho khung dữ liệu thời gian?

Dec 31 2020

Tôi đang viết một chương trình sẽ tải tệp excel timeseries vào khung dữ liệu, sau đó tôi tạo một số cột mới bằng cách sử dụng một số phép tính cơ bản. Chương trình của tôi đôi khi sẽ đọc trong các tệp excel bị thiếu tháng đối với một số bản ghi. Vì vậy, trong ví dụ dưới đây, tôi có dữ liệu bán hàng hàng tháng cho hai cửa hàng khác nhau. Các cửa hàng mở cửa vào các tháng khác nhau, vì vậy ngày cuối tháng đầu tiên của họ sẽ khác nhau. Nhưng cả hai sẽ có dữ liệu cuối tháng cho đến 30/9/2020. Trong tệp của tôi, Store BBB không có hồ sơ cho ngày 31 tháng 8 năm 2020 và 30 tháng 9 năm 2020 vì không có doanh số bán hàng trong những tháng đó.

Cửa hàng Tháng khai trương Tiểu bang Tp. Ngày kết thúc tháng Bán hàng
AAA 31/5/2020 NY Newyork 31/5/2020 1000
AAA 31/5/2020 NY Newyork 30/6/2020 5000
AAA 31/5/2020 NY Newyork 30/7/2020 3000
AAA 31/5/2020 NY Newyork 31/8/2020 4000
AAA 31/5/2020 NY Newyork 30/9/2020 2000
BBB 30/6/2020 CT Hartford 30/6/2020 100
BBB 30/6/2020 CT Hartford 30/7/2020 200

Vì vậy, đối với bất kỳ trường hợp nào như thế này, tôi muốn có thể thêm hai hàng cho Cửa hàng BBB cho 8/31 và 30/9. Các hàng mới phải sử dụng cùng một Tháng được mở, Tiểu bang và Thành phố từ ngày kết thúc tháng gần đây nhất. Bán hàng phải được đặt thành 0 cho cả hai hàng mới. Ngay bây giờ, tôi thực hiện các bước sau:

  1. Tạo Dataframe "MaxDateData" với tên cửa hàng và Ngày kết thúc tháng tối đa cho mỗi Cửa hàng và cũng là Ngày kết thúc tháng tối đa cho toàn bộ khung dữ liệu chuỗi thời gian, tôi đặt tên trường này là "Ngày gần đây nhất".
Cửa hàng Ngày kết thúc tháng tối đa Ngày gần đây nhất
AAA 30/9/2020 30/9/2020
BBB 30/7/2020 30/9/2020
  1. Tạo khung dữ liệu "MostRecent" với hàng gần đây nhất từ ​​khung dữ liệu chuỗi thời gian chính. Để làm điều này, tôi thực hiện một phép nối bên trong giữa khung dữ liệu chuỗi thời gian và Dữ liệu ngày tối đa trên Tên cửa hàng và Ngày kết thúc tháng tối đa.
Cửa hàng Tháng khai trương Tiểu bang Tp. Ngày kết thúc tháng Bán hàng Ngày kết thúc tháng tối đa Ngày gần đây nhất
AAA 31/5/2020 NY Newyork 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. Tạo khung dữ liệu "RequestBackfill_MostRecent" bằng cách sử dụng mệnh đề where để lọc các cửa hàng có Ngày Kết thúc Tháng Tối đa <Ngày Gần đây nhất. Xem mã bên dưới. Vì vậy, trong ví dụ này, bảng RequestBackfill_MostRecent sẽ chỉ có một hàng cho BBB cửa hàng.
RequireBackfill_Stores_MostRecent = MaxDateData.where(MaxDateData['Max Month End Date'] <MaxDateData['Most Recent Date'])
RequireBackfill_MostRecent = MostRecent.merge(RequireBackfill_Stores_MostRecent,how='inner')
  1. Sau đó, tôi sử dụng hai vòng lặp for lồng nhau để chuyển qua các ngày mà tôi cần điền vào. Nó tận dụng khung dữ liệu RequiBackfill_MostRecent sẽ chỉ chứa 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) 
  1. Sau đó, tôi thêm NewData vào khung dữ liệu thời gian của mình bằng cách sử dụng concat
FullData_List = [MonthlyData,NewData]
FullData=pd.concat(FullData_List)

Toàn bộ quá trình này hoạt động nhưng có cách nào hiệu quả hơn nhiều để làm điều này không? Điều này có thể trở nên tốn kém khi tôi bắt đầu làm việc với dữ liệu lớn hơn.

Trả lời

Ferris Dec 31 2020 at 14:08
  1. chỉ cần thử upsamplechỉ mục 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. lưu ý rằng: 7/30/2020không phải là ngày cuối cùng của tháng bảy. 7/31/2020Là. vì vậy Sử dụng phương pháp 7/30/2020này sẽ là một vấn đề (chuyển Ngày kết thúc tháng thành ngày kết thúc thực sự).
JoeFerndz Dec 31 2020 at 16:14

Đây là cách tiếp cận từng bước để thực hiện việc này. Nếu bạn có câu hỏi, cho tôi biết.

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)

Đầu ra của điều này là:

   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

Lưu ý rằng tôi đã thêm một mục nhập nữa với CCC để hiển thị cho bạn nhiều biến thể hơn.