CODEWITHSUNDEEP
pythonpandasdataframe
KS17
KS17

Reputation: 95

In Pandas how to update column value in one row based on another column value in another row

I want to update value in a cell based on value in another cell in different row.

My dataframe is given below:

OrderId   OrderType   Exch
    P_001       P         NYSE
    P_001_1     C          | | 
    P_002       P          |CHIX|
    P_002_1     C          | |
    P_002_2     C          | |

And i want the result to be 

OrderId   OrderType    Exch

P_001       P          |NYSE|
P_001_1     C          |NYSE|
P_002       P          |CHIX|
P_002_1     C          |CHIX|
P_002_2     C          |CHIX|

Using .loc i can update same rows but i am not able to find any solution in Pandas data-frame for such an update.

While i ask this question, i am try to split the Order id and search in the data frame to update the Exch values.

Upvotes: 3

Views: 318

Answers (2)

jezrael
jezrael

Reputation: 862431

If not exist values are missing, use forward filling missing values:

df['Exch'] = df['Exch'].ffill()

Or use Series.str.split for new DataFrame, groupby by first and second column with GroupBy.transform and GroupBy.first:

df1 = df['OrderId'].str.split('_', expand=True)
df['Exch'] = df.groupby([df1[0], df1[1]])['Exch'].transform('first')

print (df)
   OrderId OrderType  Exch
0    P_001         P  NYSE
1  P_001_1         C  NYSE
2    P_002         P  CHIX
3  P_002_1         C  CHIX
4  P_002_2         C  CHIX

Another idea is get rows with P, create Series and map:

s = df[df['OrderType'].eq('P')].set_index('OrderId')['Exch']
df['Exch'] = df['OrderId'].str.rsplit('_', n=1).str[0].map(s).fillna(df['Exch'])
print (df)
   OrderId OrderType  Exch
0    P_001         P  NYSE
1  P_001_1         C  NYSE
2    P_002         P  CHIX
3  P_002_1         C  CHIX
4  P_002_2         C  CHIX

Upvotes: 1

prosti
prosti

Reputation: 46301

df= df.ffill(axis = 0) 
print(df)

         a  b     c
0    P_001  P  NYSE
1  P_001_1  C  None
2    P_002  P  CHIX
3  P_002_1  C  None
4  P_002_2  C  None
         a  b     c
0    P_001  P  NYSE
1  P_001_1  C  NYSE
2    P_002  P  CHIX
3  P_002_1  C  CHIX
4  P_002_2  C  CHIX

If you would like to do that for the while dateset.

Upvotes: 0

Related Questions