在這個問題中，您有兩個資料框，一個帶有最后一次價格發布，通常是今天。在另一個資料框中，我們有所有的發射。
我們的想法是，我們可以使用這兩個資料框，結果是該價格的當前日期和最近的第二天之間的差異的結果。重復今天并忽略倒數第二個日期。而最難的部分是這種差異需要遵循周期性模式。因此，如果日期型別是星期五，則只能與之前的星期五有所不同。
以一種重復行的方式，除了不可用的價格。
第一個資料框：
import pandas as pd
data = {
'Type': ['Product1', 'Product2', 'Product3'],
'State': ['New York', 'Washington', 'Illinois'],
'Date':['25/03/2022','25/03/2022','25/03/2022'],
'Price':['5.00','4.00','4.00'],
'Type-Date':['Friday (only)','Friday (only)','Monday, Wednesday, Friday (only)']}
df_1 = pd.DataFrame(data)
df_1
Type State Date Price Name-Date
0 Product1 New York 25/03/2022 5.00 Friday (only)
1 Product2 Washington 25/03/2022 4.00 Friday (only)
2 Product3 Illinois 25/03/2022 4.00 Monday, Wednesday, Friday (only)
第二個資料框：
data = {'Type': ['Product1', 'Product1', 'Product1','Product2','Product2','Product2','Product3','Product3','Product3'],
'State': ['New York', 'New York','New York', 'Washington', 'Washington', 'Washington', 'Illinois', 'Illinois', 'Illinois'],
'Date':['25/03/2022','04/03/2022','25/02/2022', '25/03/2022', '11/03/2022', '04/03/2022', '25/03/2022', '16/03/2022', '14/03/2022'],
'Price':['5.00','4.00','4.00','4.00','3.00','2.00','4.00','3.00','4.00'],
'Type-Date':['Friday (only)','Friday (only)','Friday (only)','Friday (only)','Friday (only)','Friday (only)',
'Monday, Wednesday, Friday (only)','Monday, Wednesday, Friday (only)','Monday, Wednesday, Friday (only)']}
df_2 = pd.DataFrame(data)
df_2
Type State Date Price Type-Date
0 Product1 New York 25/03/2022 5.00 Friday (only)
1 Product1 New York 04/03/2022 4.00 Friday (only)
2 Product1 New York 25/02/2022 4.00 Friday (only)
3 Product2 Washington 25/03/2022 4.00 Friday (only)
4 Product2 Washington 11/03/2022 3.00 Friday (only)
5 Product2 Washington 04/03/2022 2.00 Friday (only)
6 Product3 Illinois 25/03/2022 4.00 Monday, Wednesday, Friday (only)
7 Product3 Illinois 16/03/2022 3.00 Monday, Wednesday, Friday (only)
8 Product3 Illinois 14/03/2022 4.00 Monday, Wednesday, Friday (only)
期望的結果
Type State Date Price Type-Date
0 Product1 New York 25/03/2022 5.00 Friday (only)
1 Product1 New York 18/03/2022 NaN Friday (only)
2 Product1 New York 11/03/2022 NaN Friday (only)
3 Product2 Washington 25/03/2022 4.00 Friday (only)
4 Product2 Washington 18/03/2022 NaN Friday (only)
5 Product3 Illinois 25/03/2022 4.00 Monday, Wednesday, Friday (only)
6 Product3 Illinois 23/03/2022 NaN Monday, Wednesday, Friday (only)
7 Product3 Illinois 21/03/2022 NaN Monday, Wednesday, Friday (only)
8 Product3 Illinois 18/03/2022 NaN Monday, Wednesday, Friday (only)
uj5u.com熱心網友回復：
這里有很多，這也意味著可能會出現幾種可能的情況，這些情況可能會或可能不會出現在這個答案中。例如，如果在 df_2 中找不到給定型別的 df_1 中的日期，或者在給定型別的 df_2 中沒有條目等，該怎么辦？
有了這個警告，這里有一些代碼可以產生問題中指定的預期結果：
import pandas as pd
import numpy as np
data = {
'Type': ['Product1', 'Product2', 'Product3'],
'State': ['New York', 'Washington', 'Illinois'],
'Date':['25/03/2022','25/03/2022','25/03/2022'],
'Price':['5.00','4.00','4.00'],
'Type-Date':['Friday (only)','Friday (only)','Monday, Wednesday, Friday (only)']}
df_1 = pd.DataFrame(data)
data = {'Type': ['Product1', 'Product1', 'Product1','Product2','Product2','Product2','Product3','Product3','Product3'],
'State': ['New York', 'New York','New York', 'Washington', 'Washington', 'Washington', 'Illinois', 'Illinois', 'Illinois'],
'Date':['25/03/2022','04/03/2022','25/02/2022', '25/03/2022', '11/03/2022', '04/03/2022', '25/03/2022', '16/03/2022', '14/03/2022'],
'Price':['5.00','4.00','4.00','4.00','3.00','2.00','4.00','3.00','4.00'],
'Type-Date':['Friday (only)','Friday (only)','Friday (only)','Friday (only)','Friday (only)','Friday (only)',
'Monday, Wednesday, Friday (only)','Monday, Wednesday, Friday (only)','Monday, Wednesday, Friday (only)']}
df_2 = pd.DataFrame(data)
'''
Objective:
Create a dataframe which for each Type contains:
- today's Date and Price from df_1
- prior Date values with Price of NaN going back in time according to the Type's corresponding Type-Date value, back to but not including the penultimate date for which a price is available in df_2
'''
dayStrToInt = {'Monday':0,'Tuesday':1,'Wednesday':2,'Thursday':3,'Friday':4,'Saturday':5,'Sunday':6}
freqByType = {}
def setFreqByType(row):
weekdays = [s.strip() for s in row['Type-Date'].replace('(only)', '').split(',')]
if not weekdays:
raise ValueError(f"No weekdays found in Type-Date {repr(row['Type-Date'])}")
days = []
for w in weekdays:
if w not in dayStrToInt:
raise ValueError(f'Bad day-of-week string {w}')
days.append(dayStrToInt[w])
freqByType[row['Type']] = days
import datetime
datePriceListByType = []
def compileDatePriceByType(row):
curType = row['Type']
curDate = datetime.datetime.strptime(row['Date'], '%d/%m/%Y').date()
allDates = [datetime.datetime.strptime(dateStr, '%d/%m/%Y').date() for dateStr in df_2[df_2['Type']==row['Type']]['Date']]
allDateStrs = [dt.strftime('%d/%m/%Y') for dt in allDates]
minDate = min(allDates)
newDates = [curDate]
dt = curDate
days = freqByType[curType]
while dt > minDate:
curWD = dt.weekday()
nextWD = curWD
while nextWD not in days:
nextWD = (nextWD - 1) % 7
iWD = (days.index(nextWD) - (1 if nextWD == curWD else 0)) % len(days)
dt -= datetime.timedelta(days=(curWD - days[iWD]) % 7 if curWD != days[iWD] else 7)
if dt in allDates:
break
if dt > minDate:
newDates.append(dt)
datePrice = [[dt.strftime('%d/%m/%Y') for dt in newDates], [row['Price']] [np.nan]*(len(newDates) - 1)]
datePriceListByType.append(datePrice)
df_1.apply(setFreqByType, axis=1)
df_1.apply(compileDatePriceByType, axis=1)
df_result = df_1
df_result[['Date', 'Price']] = pd.DataFrame(datePriceListByType, columns=['Date', 'Price'])
df_result = df_result.explode(['Date', 'Price'], ignore_index=True)
print(df_result)
輸出：
Type State Date Price Type-Date
0 Product1 New York 25/03/2022 5.00 Friday (only)
1 Product1 New York 18/03/2022 NaN Friday (only)
2 Product1 New York 11/03/2022 NaN Friday (only)
3 Product2 Washington 25/03/2022 4.00 Friday (only)
4 Product2 Washington 18/03/2022 NaN Friday (only)
5 Product3 Illinois 25/03/2022 4.00 Monday, Wednesday, Friday (only)
6 Product3 Illinois 23/03/2022 NaN Monday, Wednesday, Friday (only)
7 Product3 Illinois 21/03/2022 NaN Monday, Wednesday, Friday (only)
8 Product3 Illinois 18/03/2022 NaN Monday, Wednesday, Friday (only)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/450714.html