Python(Pandas) - Create a column by matching column's values into dataframe

Question

I have the below assumed dataframe

a       b       c       d       e       F
0.02    0.62    0.31    0.67    0.27    a
0.30    0.07    0.23    0.42    0.00    a
0.82    0.59    0.34    0.73    0.29    a
0.90    0.80    0.13    0.14    0.07    d
0.50    0.62    0.94    0.34    0.53    d
0.59    0.84    0.95    0.42    0.54    d
0.13    0.33    0.87    0.20    0.25    d
0.47    0.37    0.84    0.69    0.28    e

Column F represents the columns of the dataframe. For each row of column F I want to find relevant row and column from the rest of the dataframe and return the values into one column

The outcome will look like this:

a       b       c       d       e       f   To_Be_Filled
0.02    0.62    0.31    0.67    0.27    a   0.02 
0.30    0.07    0.23    0.42    0.00    a   0.30 
0.82    0.59    0.34    0.73    0.29    a   0.82 
0.90    0.80    0.13    0.14    0.07    d   0.14 
0.50    0.62    0.94    0.34    0.53    d   0.34 
0.59    0.84    0.95    0.42    0.54    d   0.42 
0.13    0.33    0.87    0.20    0.25    d   0.20 
0.47    0.37    0.84    0.69    0.28    e   0.28

I am able to identify each case with the below, but not sure how to do it across the whole dataframe.

test.loc[test.iloc[:,5]==a,test.columns==a]

Many thanks in advance.

user2285236 · Accepted Answer · 2017-04-03 14:54:29Z

6

You can use lookup:

df['To_Be_Filled'] = df.lookup(np.arange(len(df)), df['F'])
df
Out: 
      a     b     c     d     e  F  To_Be_Filled
0  0.02  0.62  0.31  0.67  0.27  a          0.02
1  0.30  0.07  0.23  0.42  0.00  a          0.30
2  0.82  0.59  0.34  0.73  0.29  a          0.82
3  0.90  0.80  0.13  0.14  0.07  d          0.14
4  0.50  0.62  0.94  0.34  0.53  d          0.34
5  0.59  0.84  0.95  0.42  0.54  d          0.42
6  0.13  0.33  0.87  0.20  0.25  d          0.20
7  0.47  0.37  0.84  0.69  0.28  e          0.28

np.arange(len(df)) can be replaced with df.index.

answered Apr 3, 2017 at 14:54

user2285236

Sign up to request clarification or add additional context in comments.

1 Comment

pbou Over a year ago

Thank you so much!! This is exactly what I was looking for! Much appreciated

Collectives™ on Stack Overflow

Python(Pandas) - Create a column by matching column's values into dataframe

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related