Select columns in a DataFrame conditional on row

Question

I am attempting to generate a dataframe (or series) based on another dataframe, selecting a different column from the first frame dependent on the row using another series. In the below simplified example, I want the frame1 values from 'a' for the first three rows, and 'b for the final two (the picked_values series).

frame1=pd.DataFrame(np.random.randn(10).reshape(5,2),index=range(5),columns=['a','b'])
picked_values=pd.Series(['a','a','a','b','b'])

Frame1

    a           b
0   0.283519    1.462209
1   -0.352342   1.254098
2   0.731701    0.236017
3   0.022217    -1.469342
4   0.386000    -0.706614

Trying to get to the series:

0   0.283519
1   -0.352342
2   0.731701
3   -1.469342
4   -0.706614

I was hoping values[picked_values] would work, but this ends up with five columns.

In the real-life example, picked_values is a lot larger and calculated.

Thank you for your time.

anky · Accepted Answer · 2020-01-24 14:21:12Z

6

Use df.lookup

pd.Series(frame1.lookup(picked_values.index,picked_values))

0    0.283519
1   -0.352342
2    0.731701
3   -1.469342
4   -0.706614
dtype: float64

answered Jan 24, 2020 at 14:21

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

1 Comment

TheSuperbard Over a year ago

And this is why stack overflow is the best. Thanks!

yatu · Accepted Answer · 2020-01-24 14:25:01Z

3

Here's a NumPy based approach using integer indexing and Series.searchsorted:

frame1.values[frame1.index, frame1.columns.searchsorted(picked_values.values)]
# array([0.22095278, 0.86200616, 1.88047197, 0.49816937, 0.10962954])

answered Jan 24, 2020 at 14:25

yatu

88.7k12 gold badges94 silver badges149 bronze badges

2 Comments

anky Over a year ago

I was wondering why this has a different output, then realized that the sample is a random.

yatu Over a year ago

Yup :) could've stuck to the generated one to make it less confusing @anky

Collectives™ on Stack Overflow

Select columns in a DataFrame conditional on row

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related