Title: Selecting specific rows from a dataframe and restructuring a variable (Python)
Tags: python;pandas;dataframe
Question: Let's say I have the following dataframe:
df = pd.DataFrame({'order_id' : [45136, 1265, 94713, 45534, 2122, 34875, 6577, 1722, 522743, 998, 9265, 15463],
'user_id' : [1, 1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3],
'order_number' : [1, 2, 3, 4, 5, 1, 2, 3, 4, 1, 2, 3],
'days_since_prior_order' : [None, 15, 21, 29, 14, None, 3, 9, 19, None, 28, 30]
})
print(df)
order_id  user_id  order_number  days_since_prior_order
0      45136        1             1                     NaN
1       1265        1             2                    15.0
2      94713        1             3                    21.0
3      45534        1             4                    29.0
4       2122        1             5                    14.0
5      34875        2             1                     NaN
6       6577        2             2                     3.0
7       1722        2             3                     9.0
8     522743        2             4                    19.0
9        998        3             1                     NaN
10      9265        3             2                    28.0
11     15463        3             3                    30.0
What I want to do, is change the numeric variable &quot;days since prior order&quot; into a boolean &quot;has there been a reorder within the next 14 days&quot;.
Furthermore I want to remove the latest order of each user and make a new dataframe out of it, because those are the orders for which I will want to predict, whether a reorder within the next 14 days will take place.
The results I'm looking for would look like this:
print(df2)
order_id  user_id  order_number  reorder_within_14_days
0     45136        1             1                       0
1      1265        1             2                       0
2     94713        1             3                       0
3     45534        1             4                       1
4     34875        2             1                       1
5      6577        2             2                       1
6      1722        2             3                       0
7       998        3             1                       0
8      9265        3             2                       0
print(df3)
order_id  user_id  order_number
0      2122        1             5
1    522743        2             4
2     15463        3             3
I would imagine there is a way to use for loops and groupby to achieve this, but I don't know how. For any help I would be very grateful!
Comment: `df["reorder_within_14_days"] = (df["days_since_prior_order"]<=14).astype(int)`

Comment: if no one helps u soon ill do it but currently not on my pc

Comment: @ombk Thank you for your comment, that is a good starting point. Unfortunately I still have to somehow move those results up by one row, because the "days until next order" are the "days since prior order" of the following order, if that makes sense.

Comment: @ombk Ok I found a way to achieve that by using .shift(-1). Thanks a lot! Now I just need to somehow extract those latest orders...

Comment: I would very much appreciate it!

Here is the accepted answer: From the comments, you can find you desired reorder_within_14_days column by simply doing:
df[&quot;reorder_within_14_days&quot;] = (df[&quot;days_since_prior_order&quot;] <= 14).astype(int)
And to get the last order by user, you can use groupby with the tail method, such as :
last_orders = df.sort_values('order_number').groupby('user_id').tail(1)
df.drop(last_orders.index, inplace=True)
Let us know if you have any further issues!


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 5, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2020:00:12/05/20', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<python><pandas><dataframe>', 'Title': 'Selecting specific rows from a dataframe and restructuring a variable (Python)', 'ViewCount': 109, 'id': 65160360, 'user_AboutMe': None, 'user_AccountId': 17590479.0, 'user_DisplayName': 'Zwiebak', 'user_Reputation': 304.0, 'user_Views': 19.0, 'user_id': 12762467.0}}