Pandas DataFrame - for duplicate rows move column value

Question

I need to do the following thing:

If two rows have the same invoice value, I need to show the values in the same column.

Here I am trying to do so moving the values from column E to column G.

A           B        C            D              E           F      G   
INV12-75821 SLS      12/18/2016   $459.26                           $459.26 

INV12-75821 92004    01/11/2017                 **($459.20)**           

INV12-97267 SLS      01/06/2019   $750.45                           $750.45 

INV12-97267 11654    01/23/2019                 **($750.00)**

Here a table screen

Expected OutPut:

A           B        C            D              E           F      G   
INV12-75821 SLS      12/18/2016   $459.26                           $459.26 

INV12-75821 92004    01/11/2017                                     ($459.20)

INV12-97267 SLS      01/06/2019   $750.45                           $750.45 

INV12-97267 11654    01/23/2019                                     ($750.00)

Will there ever be a case where there is a value in both G and E? If not, why not just check for empty values in G and fill with the value of E and then drop E? Wouldn't this work regardless of the invoice number? Then you could group or sort the invoice numbers if you wanted to see everything together. — whege
– whege, Commented Apr 1, 2020 at 21:23
There will not be value in E and G at the same time, in any case there might be value in either E, F or G. I just want to align both values to be in either E or F or G. Hope you understand what I am looking to achieve. — Prasad Kadrikar
– Prasad Kadrikar, Commented Apr 1, 2020 at 23:26

Raphaele Adjerad · Accepted Answer · 2020-04-04 06:53:28Z

1

I suggest:

import pandas as pd
df = pd.DataFrame({"A": ["Inv1", "Inv2", "Inv1", "Inv3"],
                   "E": ["", "", "**($459.2)**", ""],
                   "G": ["$459.26", "23", "", "6283"]})
df["dup"] = df.duplicated(subset="A")
df.loc[df["dup"], "G"] = df.loc[df["dup"], "E"].str.replace("*", "")
df = df.drop(columns="dup")

You first create a duplicated row dummy and then fill the column G with values from E, while stripping the * characters. And then drop the column dup you created.

answered Apr 4, 2020 at 6:53

Raphaele Adjerad

1,1257 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

Pandas DataFrame - for duplicate rows move column value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related