CODEWITHSUNDEEP
pythonexcelpandasexcel-formula
Esha
Esha

Reputation: 57

Consolidating duplicate data in pandas

I have an excel file somewhat like this:

c1       c2      c3  
A         2     sdg  
A         1     sjdn  
A         3      hh  
B         4      nk

Desired output should look like-

c1       c2      c3  
A         6      sdg   
B         4      nk

Can this be handled in excel itself? Or otherwise a python code would also help!

Upvotes: 0

Views: 26

Answers (2)

EDS
EDS

Reputation: 2195

You can do this directly in Excel in one go with something like:

=LET(x, UNIQUE($A$1:$A$6),
y, SUMIF($A$1:$A$6, x, $B$1:$B$6),
z, INDEX($A$1:$C$6, MATCH(x, $A$1:$A$6,0), 3),
mycols, SEQUENCE(1,3),
IF(mycols=1, x, IF(mycols=2, y,z)))

if you have the newest version. If not, then you can go column-by-column and put similar formulas, then drag down.

enter image description here

Upvotes: 0

BENY
BENY

Reputation: 323316

In pandas we can do groupby with agg

out = df.groupby('c1',as_index=False).agg({'c2':'sum','c3':'first'})
Out[128]: 
  c1  c2   c3
0  A   6  sdg
1  B   4   nk

Upvotes: 1

Related Questions