Assign weights to observations based on date difference and sequence condition

user_9137206 0
Viewed 41

I already asked the question on the same problem and @mozway helped a lot.

However my logic on weights assignment was wrong.

I need to form the following dataframe w/ weight column:

    id      date        status    weight   diff_in_days  comment
-----------------------------------------------------------------
0    2     2019-02-03   reserve   0.003         0       1 / diff_days
1    2     2019-12-31   reserve   0.001       331       since diff to next is 1 day 
2    2     2020-01-01   reserve    0.9          1       since the next date status is sold   
3    2     2020-01-02   sold        1           1       sold
4    3     2020-01-03   reserve   0.001         0       since diff to next is 1 day
5    4     2020-01-03   booked     0.9          0       since the next date status is sold
6    3     2020-02-04   reserve    0.9          1       since the next date status is sold
7    4     2020-02-06   sold        1           3       sold
7    3     2020-02-07   sold        1           3       sold

To make diff_in_days column I use:

df['diff_in_days'] = df.groupby('flat_id')['date'].diff().dt.days.fillna(0)

Is there a way to implement this preudo-code without for-loop:

for i in df.iterrows():
    df['weight'][i] = 1 / df['diff_in_days'][i+1]
if df['status'][i+1] == 'sold' (for each flat_id):
    df['weight'][i] = 0.9
if df['status'][i] == 'sold':
    df['weight'][i] = 1
pandas dataframe for-loop python
1 Answers

Managed to do it like this:

df.sort_values(['flat_id', 'date'], inplace=True)

find diff in days between dates for flat_ids and shift it one row back

s = df.groupby(['flat_id']['date'].diff().dt.days.shift(-1)

assign weights for flat_ids with status == 'sold'

df['weight'] = np.where(df['status'].eq('sold'),s.max()+10, s.fillna(0))

now find rows with status == sold and shift back one row to find it's predecessors

s1 = df["status"].eq("sold").shift(-1)
s1 = s1.fillna(False)

assign them second maximum weights

df.loc[s1, "weight"] = s.max()+5
df["weight"].ffill(inplace=True)

final dataframe

    flat_id date    status  weight
4   2   2019-02-04  reserve 331.0
0   2   2020-01-01  reserve 336.0
1   2   2020-01-02  sold    341.0
2   3   2020-01-03  reserve 1.0
5   3   2020-01-04  reserve 336.0
7   3   2020-02-07  sold    341.0
3   4   2020-01-03  booked  336.0
6   4   2020-02-06  sold    341.0
user_91372060
Related