Efficient solutions insert or update row pandas

Question

I have a dataframe with the following structure:

              0               1   2
 0   0.00086076    500.00000000  []
 1   0.00086075    819.00000000  []
 2   0.00086072   1162.00000000  []
 3   0.00086071     20.00000000  []
 4   0.00086069  10170.00000000  []
 5   0.00086067     18.00000000  []

Then I have another dataframe with update values:

              0               1   2
 0   0.00086071     50.00000000  []
 1   0.00086068     81.00000000  []

It is sorted on column 0 and I need to merge the two as follows:

              0               1   2
 0   0.00086076    500.00000000  []
 1   0.00086075    819.00000000  []
 2   0.00086072   1162.00000000  []
 3   0.00086071     50.00000000  []
 4   0.00086069  10170.00000000  []
 5   0.00086068     81.00000000  []
 6   0.00086067     18.00000000  []

So if a value matches one in column 0 it should update column 1 if not it should insert a new row and sort again.

Is there an efficient way or predefined function for doing this?

score 10 · Accepted Answer · edited Jan 13 '18 at 16:16

10

I think you need:

concat both DataFrames together
drop_duplicates for remove original rows with same values in column 0
sort_values by column 0
reset_index for unique index values

df = (pd.concat([df1, df2])
        .drop_duplicates([0] , keep='last')
        .sort_values(0 , ascending=False)
        .reset_index(drop=True))
print (df)
          0        1   2
0  0.000861    500.0  []
1  0.000861    819.0  []
2  0.000861   1162.0  []
3  0.000861     50.0  []
4  0.000861  10170.0  []
5  0.000861     81.0  []
6  0.000861     18.0  []

edited Jan 13 '18 at 16:16

user3605780

6,542
13
42
67

answered Jan 12 '18 at 12:41

jezrael

822,522
95
1,334
1,252

Is your `df[0]` all the same, as you posted? If so, then it isn't like the desired output. – jo9k Jan 12 '18 at 12:46
2

@jo9k He is just printing them with less precision. The information is there nevertheless – Ma0 Jan 12 '18 at 12:47

Espoir Murhabazi · Answer 2 · 2018-01-12T20:23:34.467

0

what you need is append and drop duplicates

df = df1.append(df2)
df = df.drop_duplicates('0', keep='last').sort_values('0', ascending= False)

But according to this Jezrael answer is the fast and most efficient mine is just an alternative !

edited Jan 12 '18 at 20:23

answered Jan 12 '18 at 12:40

Espoir Murhabazi

5,973
5
42
73

1

I think it is not so nice copy my solution :( – jezrael Jan 12 '18 at 13:07

Efficient solutions insert or update row pandas

2 Answers2