Creating new dataframe based on maximum element

Question

I have two dataframes-

cols = ['A','B']
data = [[-1,2],[0,2],[5,1]]
data = np.asarray(data)
indices = np.arange(0,len(data))

df = pd.DataFrame(data, index=indices, columns=cols)

cols = ['A','B']
data2 = [[-13,2],[-1,2],[0,4],[2,1],[5,0]]
data2 = np.asarray(data2)
indices = np.arange(0,len(data2))

df2 = pd.DataFrame(data2, index=indices, columns=cols)

Now I want to create a new dataframe which has for the same A the maximum of B from either dataframe.

Therefore, the output would be-

df1 has 3 rows, df2 has 5. When you say "for the same A the maximum of B from either dataframe" you mean "maximum of B for that specific row in either df". — smci
– smci, Commented Oct 22, 2018 at 23:56
Btw, you can just instantiate each dataframe in one line: df = pd.DataFrame([[-1,2],[0,2],[5,1]], columns=['A','B']). You don't need to cast data = np.asarray(data), and you don't need to generate and pass in the indices, it defaults to range-indices like you want. Makes your example much clearer. — smci
– smci, Commented Oct 22, 2018 at 23:59

BENY · Accepted Answer · 2018-10-23 00:00:24Z

4

Using drop_duplicates

pd.concat([df2,df]).sort_values('B').drop_duplicates('A',keep='last')
Out[80]: 
    A  B
3   2  1
2   5  1
0 -13  2
0  -1  2
2   0  4

answered Oct 23, 2018 at 0:00

BENY

324k22 gold badges176 silver badges250 bronze badges

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

1 Comment

jpp Over a year ago

For completeness / efficiency, we probably need reset_index to get rid of the duplicate indices.

jpp · Accepted Answer · 2018-10-22 23:53:22Z

2

You can align indices, concatenate and then take the maximum:

res = pd.concat([df.set_index('A'), df2.set_index('A')], axis=1)\
        .max(1).astype(int).rename('B').reset_index()

print(res)

    A  B
0 -13  2
1  -1  2
2   0  4
3   2  1
4   5  1

answered Oct 22, 2018 at 23:53

jpp

166k37 gold badges301 silver badges363 bronze badges

2 Comments

ubuntu_noob Over a year ago

Could you explain the part after concat?

jpp Over a year ago

Sure, max(1) takes row-wise maximums, astype(int) converts to integers (otherwise you get float output). Since the resultant series has name 0 we need to rename as 'B'. Finally, to elevate index to column we use reset_index.

Collectives™ on Stack Overflow

Creating new dataframe based on maximum element

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