Pandas: Iterate and insert column with conditions within groups complex question

Question

I have a quite complex question about how to add a new column with conditions for each group. Here is the example dataframe,

df = pd.DataFrame({
    'id': ['AA', 'AA', 'AA', 'AA', 'BB', 'BB', 'BB', 'BB', 'BB',
           'CC', 'CC', 'CC', 'CC', 'CC', 'CC', 'CC'],
    'From_num': [80, 68, 751, 'Issued', 32, 68, 126, 'Issued', 'Missed', 105, 68, 114, 76, 68, 99, 'Missed'],
    'To_num':[99, 80, 68, 751, 105, 32, 68, 126, 49, 324, 105, 68, 114, 76, 68, 99],
})
    id From_num  To_num
0   AA       80      99
1   AA       68      80
2   AA      751      68
3   AA   Issued     751
4   BB       32     105
5   BB       68      32
6   BB      126      68
7   BB   Issued     126
8   BB   Missed      49
9   CC      105     324
10  CC       68     105
11  CC      114      68
12  CC       76     114
13  CC       68      76
14  CC       99      68
15  CC   Missed      99

I have a 'flag' number 68. In each group, for any row equals or above this flag number in 'From_num' column will be tagged "Forward" in the new column , any row equals or below the flag number in 'To_num' column will be labelled 'Back' in the same column. However, the hardest situation is: if this flag number appears more than once in each column, the rows between the 'From_num' and 'To_num' will be labelled "Forward&Back" in the new column, see the df and the expected result below.

Expected result
    id From_num  To_num     Direction
0   AA       80      99       Forward
1   AA       68      80       Forward
2   AA      751      68          Back
3   AA   Issued     751          Back
4   BB       32     105       Forward
5   BB       68      32       Forward
6   BB      126      68          Back
7   BB   Issued     126          Back
8   BB   Missed      49          Back
9   CC      105     324       Forward
10  CC       68     105       Forward 
11  CC      114      68  Forward&Back # From line 11 to 13, flag # 68 appears more than once
12  CC       76     114  Forward&Back # so the line 11, 12 and 13 labelled "Forward&Back"
13  CC       68      76  Forward&Back 
14  CC       99      68          Back 
15  CC   Missed      99          Back

I tried to write many loops, and they all failed and could not have an expected result. So if anyone has ideas, please help. Hopefully the question is clear. Many thanks!

user_9441404 · Answer

I've done without "real looping".

preserve the row numbers (reset_index())
construct a new data frame that is records that contain flags (68)
simple logic for "Forward" and "Back" is based on row being before or after first sighting of 68
"Forward&Back" occurs when there are multiple sightings and between 2nd and (n-1)th sighting

def direction(r):
    flagrow = df2[(df2["id"]==r["id"]) ]["index"].values
    if r["index"] <= flagrow[0]: val = "Forward"
    elif r["index"] > flagrow[0]: val = "Back"
    if len(flagrow)>2 and r["index"] >= flagrow[1] and r["index"]

Pandas: Iterate and insert column with conditions within groups complex question

1 Answers