CODEWITHSUNDEEP
pandas
Hristo Stoychev
Hristo Stoychev

Reputation: 535

Pandas search in ascending index and match certain column value

I have a DF with thousands of rows. Column 'col1' is repeatedly from 1 to 6. Column 'value' is with unique numbers:

diction = {'col1': [1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6], 'target': [34, 65, 23, 65, 12, 87, 36, 51, 26, 74, 34, 87]}
df1 = pd.DataFrame(diction, index = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11])

   col1 target
0   1   34
1   2   65
2   3   23
3   4   65
4   5   12
5   6   87
6   1   36
7   2   51
8   3   26
9   4   74
10  5   34
11  6   87

I'm trying to create a new column (let's call it previous_col) that match col1 value (let's say COL1 value 2 with TARGET column value -> 65) so next time COL1 with value 2 to refer to previous TARGET value from the same row as col1 value 1:

    col1  previous_col  target
0   1       0           34
1   2       0           65
2   3       0           23
3   4       0           65
4   5       0           12
5   6       0           87
6   1       34          36
7   2       65          51
8   3       23          26
9   4       65          74
10  5       12          34
11  6       87          79

Note that first 6 rows are 0 values for previous column cuz no previous target values exist :D The tricky part here is that I need to extract previous target's by DF index ascending order or the first met COL1 value ascending. So if we have a DF with 10k rows not just to match from the top or from the middle same COL1 value and to take the TARGET value. Each value in PREVIOUS_COL should be taken ascending to index and COL1 matching values. I know I can do it with shift but sometimes COL1 is with a missing order not from 1 to 6 strictly so I need to match exactly the COL1 value.

Upvotes: 1

Views: 48

Answers (1)

BENY
BENY

Reputation: 323326

df1['Per_col']=df1.groupby('col1').target.shift(1).fillna(0)
df1
Out[1117]: 
    col1  target  Per_col
0      1      34      0.0
1      2      65      0.0
2      3      23      0.0
3      4      65      0.0
4      5      12      0.0
5      6      87      0.0
6      1      36     34.0
7      2      51     65.0
8      3      26     23.0
9      4      74     65.0
10     5      34     12.0
11     6      87     87.0

Upvotes: 1

Related Questions