python pandas loop selection from other column

Mathurin Elsen 0
Viewed 32

I have this Dataframe :

df = pd.DataFrame({
    "b":["1","2","3","4","5","6"],
    "c":["7", "8", "9","6","4","10"],
    "d":[""],
    "e":[""]
})

I want the column 'd' and 'e' to fill with conditions : if a value from column 'b' exist in column 'c', take the value from column 'b' on the row where we have the existing value. For exemple, here, we'll have :

df = pd.DataFrame({
    "b":["1","2","3","4","5","6"],
    "c":["7", "8", "9","6","4","10"],
    "d":["","","","10","6",""]

Because the value '4' on column 'b' exist in column 'c', and the value on the same row on column 'b' is '10'.

So far I found this :

    df_[commune]['d']=''
    
    for rows in df:
        df.loc[df["b"].isin(df["c"]), 'd'] = df.shift().loc[df["b"].isin(df["c"]), 'b']

And it's working for column 'd'.

But if I want to go further, I want column 'e' to verify the same operation but for value in column 'd'. It would be : if value from 'd' exist in 'c', write the value from column 'b' on 'e'. However, when I try :

    df_[commune]['e']=''
    
    for rows in df:
        df.loc[df["d"].isin(df["c"]), 'e'] = df.shift().loc[df["d"].isin(df["c"]), 'b']

It goes wrong, and take the same value as the previous operation. I get this :

df = pd.DataFrame({
    "b":["1","2","3","4","5","6"],
    "c":["7", "8", "9","6","4","10"],
    "d":["","","","10","6",""],
    "e":["","","","10","6",""]
})

expected :

df = pd.DataFrame({
    "b":["1","2","3","4","5","6"],
    "c":["7", "8", "9","6","4","10"],
    "d":["","","","10","6",""],
    "e":["","","","","4",""]
})

Can anyone help?

dataframe pandas loops python
1 Answers

According to your description, you want:

# if b in c, take 
df['d'] = df['c'].map(df.set_index('b')['c'])
df['e'] = df['d'].map(df.set_index('c')['b'])

output:

   b   c    d    e
0  1   7  NaN  NaN
1  2   8  NaN  NaN
2  3   9  NaN  NaN
3  4   6   10    6
4  5   4    6    4
5  6  10  NaN  NaN

Or maybe:

df['d'] = df['c'].map(df.set_index('b')['c'])
df['e'] = df['d'].map(df.set_index('b')['c'])

output:

   b   c    d    e
0  1   7  NaN  NaN
1  2   8  NaN  NaN
2  3   9  NaN  NaN
3  4   6   10  NaN
4  5   4    6   10
5  6  10  NaN  NaN
mozway0
Related