CODEWITHSUNDEEP
python-3.xpandas
kiyas
kiyas

Reputation: 155

Pandas locate certain value and get the value from next column

I have a large dataframe that looks like this, 

A         B         C        
1.2       11.1      42.1     
2.4       12.3      32.4            
3.1       32.4      21.7
5.8       42.1
7.3       6.8
8.9       9.21
9.1       10.2
10.1      21.7

I would like to locate the C values in the B column and get it's corresponding A values, like below

A         B         C        D
1.2       11.1      42.1     5.8
2.4       12.3      32.4     3.1    
3.1       32.4      21.7     10.1
5.8       42.1
7.3       6.8
8.9       9.21
9.1       10.2
10.1      21.7

Thank you

Upvotes: 0

Views: 27

Answers (1)

jezrael
jezrael

Reputation: 863256

Use Series.map, but is necessary unique values in B column:

df['D'] = df['C'].map(df.set_index('B')['A'])
print (df)
      A      B     C     D
0   1.2  11.10  42.1   5.8
1   2.4  12.30  32.4   3.1
2   3.1  32.40  21.7  10.1
3   5.8  42.10   NaN   NaN
4   7.3   6.80   NaN   NaN
5   8.9   9.21   NaN   NaN
6   9.1  10.20   NaN   NaN
7  10.1  21.70   NaN   NaN

If not unique, get:

"InvalidIndexError: Reindexing only valid with uniquely valued Index objects"

Then use:

df['D'] = df['C'].map(df.drop_duplicates('B').set_index('B')['A'])

Upvotes: 2

Related Questions