Adding a column based on the values from another dataframe pandas

Question

I have a dataframe created using the following data.

data = {'B1':[65, 41, 17, 7], 
        'B2':[28, 40, 47, 30],
        'B3':[16, 29, 39, 44],
        'L':[6, 6.5, 7, 7.5]
        'U':[6.5, 7, 7.5, 8]}
df=pd.DataFrame(data)

I have another dataframe df1 as follows:

data1 = {'Value':[6, 6.5, 7, 7.5]}
df1=pd.DataFrame(data1)

For each row in df1, I want to get the row from dfwhere the value is between the 'L' and 'U' values and get the column name of the maximum value of the other columns and add it as a column to df1. For example, the output can be seen in the image below: Image

Here is the code I used:

maxval=[]
for index,row in df1.iterrows():
   tmp_val=df[df.columns[0:3]][(row.Value>= df.L) &(row.Value<df.U)]
   maxval.append(tmp_val.idxmax(axis=1))

I then add this column to df1. While this works, I have a dataframe that has 300,000 rows and it is taking a long time to do this. Are there any other ways to do this quicker?

Length of df is at maximum 10 rows. However, df1 is 300,000 rows. — labalala
– labalala, Commented Oct 25, 2019 at 17:04

yatu · Accepted Answer · 2019-10-25 15:57:42Z

1

Using idxmax:

df1.assign(max_col=df.idxmax(1))

    Value max_col
0    6.0      B1
1    6.5      B1
2    7.0      B2
3    7.5      B3

We can speed it up a little more working with the underlying numpy arrays and using argmax:

df1.assign(max_col=df.columns[df.values.argmax(1)])

answered Oct 25, 2019 at 15:57

yatu

88.6k12 gold badges93 silver badges148 bronze badges

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

3 Comments

labalala Over a year ago

Sorry, I just updated the question and added the 'U' column.

yatu Over a year ago

Pleas share the expected output @labalala

labalala Over a year ago

Its in the image attached.

Quang Hoang · Accepted Answer · 2019-10-25 16:20:52Z

0

IIUC, your df['U'] is df['L'] shifted by 1. If so, you can still use idxmax but with a pd.cut:

row_map = pd.cut(df1['Value'], bins = [df['L'][0]] + df['U'].to_list(),
                 right=False,
                 labels=df.index)

df1['max_col'] = df.loc[row_map,df.columns[:3]].idxmax(1)

Output:

   Value max_col
0    6.0      B1
1    6.5      B1
2    7.0      B2
3    7.5      B3

answered Oct 25, 2019 at 16:20

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Collectives™ on Stack Overflow

Adding a column based on the values from another dataframe pandas

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related