จะเพิ่มแถวสำหรับ dataframe ของไทม์ซีรีส์ได้อย่างไร?
Dec 31 2020
ฉันกำลังเขียนโปรแกรมที่จะโหลดไฟล์ excel ของ timeseries ลงใน dataframe จากนั้นฉันสร้างคอลัมน์ใหม่หลายคอลัมน์โดยใช้การคำนวณพื้นฐาน บางครั้งโปรแกรมของฉันจะอ่านไฟล์ excel ที่ขาดหายไปเป็นเดือนสำหรับบางบันทึก ตัวอย่างด้านล่างฉันมีข้อมูลการขายรายเดือนสำหรับร้านค้าสองแห่งที่แตกต่างกัน ร้านค้าจะเปิดในช่วงเดือนที่ต่างกันดังนั้นวันที่สิ้นเดือนแรกจะแตกต่างกัน แต่ทั้งสองควรมีข้อมูลสิ้นเดือนจนถึง 30/09/2020 ในไฟล์ของฉัน Store BBB ไม่มีบันทึกสำหรับ 31/8/2020 และ 9/30/2020 เนื่องจากไม่มีการขายในช่วงเดือนดังกล่าว
| เก็บ | เดือนที่เปิด | สถานะ | เมือง | วันที่สิ้นสุดเดือน | ฝ่ายขาย |
|---|---|---|---|---|---|
| AAA | 31/5/2020 | นิวยอร์ก | นิวยอร์ก | 31/5/2020 | 1,000 |
| AAA | 31/5/2020 | นิวยอร์ก | นิวยอร์ก | 30 มิ.ย. 63 | 5,000 |
| AAA | 31/5/2020 | นิวยอร์ก | นิวยอร์ก | 30/7/2020 | 3000 |
| AAA | 31/5/2020 | นิวยอร์ก | นิวยอร์ก | 31/8/2020 | 4000 |
| AAA | 31/5/2020 | นิวยอร์ก | นิวยอร์ก | 30/9/2020 | พ.ศ. 2543 |
| BBB | 30 มิ.ย. 63 | CT | ฮาร์ตฟอร์ด | 30 มิ.ย. 63 | 100 |
| BBB | 30 มิ.ย. 63 | CT | ฮาร์ตฟอร์ด | 30/7/2020 | 200 |
ดังนั้นสำหรับอินสแตนซ์เช่นนี้ฉันต้องการเพิ่มสองแถวสำหรับ Store BBB สำหรับ 8/31 และ 9/30 แถวใหม่ควรใช้เดือนที่เปิดรัฐและเมืองเดียวกันจากวันที่สิ้นเดือนล่าสุด ควรตั้งค่าการขายเป็น 0 สำหรับทั้งสองแถวใหม่ ณ ตอนนี้ฉันทำตามขั้นตอนต่อไปนี้:
- สร้าง Dataframe "MaxDateData" ด้วยชื่อร้านค้าและวันที่สิ้นสุดเดือนสูงสุดสำหรับแต่ละร้านค้าและวันที่สิ้นสุดเดือนสูงสุดสำหรับกรอบข้อมูลอนุกรมเวลาทั้งหมดฉันตั้งชื่อช่องนี้ว่า "วันที่ล่าสุด"
| เก็บ | วันที่สิ้นสุดเดือนสูงสุด | วันที่ล่าสุด |
|---|---|---|
| AAA | 30/9/2020 | 30/9/2020 |
| BBB | 30/7/2020 | 30/9/2020 |
- สร้าง Dataframe "MostRecent" ด้วยแถวล่าสุดจาก dataframe อนุกรมเวลาหลัก ในการดำเนินการนี้ฉันทำการรวมภายในระหว่าง dataframe อนุกรมเวลาและ MaxDateData ในชื่อร้านค้าและวันที่สิ้นสุดเดือนสูงสุด
| เก็บ | เดือนที่เปิด | สถานะ | เมือง | วันที่สิ้นสุดเดือน | ฝ่ายขาย | วันที่สิ้นสุดเดือนสูงสุด | วันที่ล่าสุด |
|---|---|---|---|---|---|---|---|
| AAA | 31/5/2020 | นิวยอร์ก | นิวยอร์ก | 30/9/2020 | พ.ศ. 2543 | 30/9/2020 | 30/9/2020 |
| BBB | 30 มิ.ย. 63 | CT | ฮาร์ตฟอร์ด | 30/7/2020 | 200 | 30/7/2020 | 30/9/2020 |
- สร้าง dataframe "RequireBackfill_MostRecent" โดยใช้ where clause เพื่อกรองร้านค้าที่ Max Month End Date <Most Recent Date. ดูรหัสด้านล่าง ดังนั้นในตัวอย่างนี้ตาราง RequireBackfill_MostRecent จะมีเฉพาะแถวสำหรับ store BBB
RequireBackfill_Stores_MostRecent = MaxDateData.where(MaxDateData['Max Month End Date'] <MaxDateData['Most Recent Date'])
RequireBackfill_MostRecent = MostRecent.merge(RequireBackfill_Stores_MostRecent,how='inner')
- จากนั้นฉันใช้สองอันซ้อนกันสำหรับลูปเพื่อวนรอบวันที่ที่ฉันต้องการกรอกโดยใช้ประโยชน์จากเฟรมข้อมูล RequireBackfill_MostRecent ซึ่งจะมีเฉพาะ 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)
- จากนั้นฉันเพิ่ม NewData ลงใน dataframe ชุดเวลาของฉันโดยใช้ concat
FullData_List = [MonthlyData,NewData]
FullData=pd.concat(FullData_List)
กระบวนการทั้งหมดนี้ใช้ได้ผล แต่มีวิธีที่มีประสิทธิภาพมากกว่านี้หรือไม่? สิ่งนี้อาจมีค่าใช้จ่ายสูงเมื่อฉันเริ่มทำงานกับข้อมูลขนาดใหญ่
คำตอบ
Ferris Dec 31 2020 at 14:08
- เพียงแค่ลอง
upsampleใช้ดัชนี 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()
- โปรดสังเกตว่า:
7/30/2020ไม่ใช่วันสิ้นเดือนกรกฎาคม7/31/2020คือ. ดังนั้นการใช้วิธีนี้7/30/2020จะเป็นปัญหา (แปลงวันที่สิ้นสุดเดือนเป็นวันที่สิ้นสุดตามจริง)
JoeFerndz Dec 31 2020 at 16:14
นี่คือวิธีการทีละขั้นตอนในการดำเนินการนี้ หากคุณมีคำถามโปรดแจ้งให้เราทราบ
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)
ผลลัพธ์คือ:
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
หมายเหตุฉันเพิ่มอีกหนึ่งรายการด้วย CCC เพื่อแสดงรูปแบบเพิ่มเติม