CODEWITHSUNDEEP
pythonexcelpandasdataframe
Hitesh kumar
Hitesh kumar

Reputation: 73

Merge duplicate cells of a column

My Current excel looks like:

  ----------------
  |  Type |  Val |
  |--------------|
  |  A    |  1   |
  |--------------|     
  |  A    |  2   |     
  |--------------|
  |  B    |  3   |
  |--------------|     
  |  B    |  4   |     
  |--------------|     
  |  B    |  5   |
  |--------------|
  |  C    |  6   |

----------------

This is the required excel:

  ----------------------
  |  Type |  Val | Sum |
  |--------------------|
  |  A    |  1   | 3   |
  |       |------|     |
  |       |  2   |     |
  |--------------------|
  |  B    |  3   | 12  |
  |       |------|     |
  |       |  4   |     |
  |       |------|     |
  |       |  5   |     |
  |--------------------|
  |  C    |  6   |  6  |
  ----------------------

Is it possible in Python using Pandas or any other module?

Upvotes: 2

Views: 1792

Answers (3)

jezrael
jezrael

Reputation: 862801

For merged first 2 levels is possible set all 3 columns to MultiIndex - only order of columns is different:

#specify column name after groupby
df['Sum'] = df.groupby('Type')['Val'].transform('sum')

df = df.set_index(['Type','Sum', 'Val'])
df.to_excel('file.xlsx')

But in my opinion the best is working with duplicated values:

df['Sum'] = df.groupby('Type')['Val'].transform('sum')
print (df)

  Type  Val  Sum
0    A    1    3
1    A    2    3
2    B    3   12
3    B    4   12
4    B    5   12
5    C    6    6

df.to_excel('file.xlsx', index=False)

Upvotes: 2

anky
anky

Reputation: 75080

IIUC use:

df['Sum']=df.groupby('Type').transform('sum')
df.loc[df[['Type','Sum']].duplicated(),['Type','Sum']]=''
print(df)

   Type     Val Sum
0    A        1   3
1             2    
2    B        3  12
3             4    
4             5    
5    C        6   6

P.s: you can also add this as index:

df=df.set_index(['Type','Sum']) #export to excel without index=False

enter image description here

Upvotes: 5

WebDev
WebDev

Reputation: 1371

You can use 

import pandas as pd

df = pd.DataFrame({'Type': ['A', 'A','B','B','B','C'], 'Val': [1,2 ,3,4,5,6]})

df_result = df.merge(df.groupby(by='Type', as_index=False).agg({'Val':'sum'}).rename(columns={'Val':'Sum'}), on = 'Type')

which gives the output as

print(df_result)
  Type  Val  Sum
0    A    1    3
1    A    2    3
2    B    3   12
3    B    4   12
4    B    5   12
5    C    6    6

Is this what you are looking for?

Upvotes: 1

Related Questions