How to change values of rows within a certain time range?

user_14114654 0
Viewed 33

I have a dataframe of purchases. I want to create a new returned column. For each person and store, if the time between returns is less or equal tp 5 minutes, I drop the yes.

10:00      amy    apple      store  returned
10:01      amy    pear       London   yes
10:03      amy    headphones
10:04      amy    missing
10:05      amy    blueberry  London   yes (within 5 minutes of 10.01 so set as NaN) 
10:06      amy    unknown
10:07      amy    table
10:08      amy    banana     London   yes (within 5 minutes of 10.05, set as NaN) 
10:09      amy    unknown
10:10      amy    banana

Expected Output

10:00      amy    apple      store  returnedv2
10:01      amy    pear      London   yes
10:03      amy    headphones
10:04      amy    missing
10:05      amy    blueberry  London   
10:06      amy    unknown
10:07      amy    table
10:08      amy    banana     London  
10:09      amy    unknown
10:10      amy    banana
pandas group-by python
1 Answers

With the dataframe you provided:

import pandas as pd


df = (
    pd.DataFrame(
        {
            "time": [
                "10:00",
                "10:01",
                "10:03",
                "10:04",
                "10:05",
                "10:06",
                "10:07",
                "10:08",
                "10:09",
                "10:10",
            ],
            "person": [
                "amy",
                "amy",
                "amy",
                "amy",
                "amy",
                "amy",
                "amy",
                "amy",
                "amy",
                "amy",
            ],
            "purchase": [
                "apple",
                "pear",
                "headphones",
                "missing",
                "blueberry",
                "unknown",
                "table",
                "banana",
                "unknown",
                "banana",
            ],
            "store": [
                None,
                "London",
                None,
                None,
                "London",
                None,
                None,
                "London",
                None,
                None,
            ],
            "returned": [None, "yes", None, None, "yes", None, None, "yes", None, None],
        }
    )
    .fillna("")
)

Here is one way to do it:

# Setup
df["time"] = pd.to_datetime(df["time"])

# Split dataframe
temp_df = df[df["returned"] == "yes"]
df = df[~df.index.isin(temp_df.index)]

# Change values in "returned" column according to specified condition
temp_df.loc[
    (temp_df["time"] - temp_df["time"].shift(+1)).apply(lambda x: x.total_seconds())
    <= 300,
    "returned",
] = ""

# Build final dataframe
df = (
    pd.concat([df, temp_df])
    .sort_index()
    .rename(columns={"returned": "returnedv2"})
    .assign(time=lambda df_: df_["time"].dt.strftime("%H:%M"))
)

So that:

print(df)
# Output
    time person    purchase   store returnedv2
0  10:00    amy       apple
1  10:01    amy        pear  London        yes
2  10:03    amy  headphones
3  10:04    amy     missing
4  10:05    amy   blueberry  London
5  10:06    amy     unknown
6  10:07    amy       table
7  10:08    amy      banana  London
8  10:09    amy     unknown
9  10:10    amy      banana
user_112460560
Related