CODEWITHSUNDEEP
python-3.xpandaspandas-groupby
Ali
Ali

Reputation: 466

Summing specific columns based on a mapping

I have a series which contains a mapping:

serm = pd.Series(
data={'ARD1': 53, 'BUL1': 37, 
'BUL2': 37, 'BSR1': 49, 'BTR1': 53, 'CR1': 53,
'CRR1': 53, 'CRE3': 53,'TAB1': 52, 'NEP1': 42, 'HAL1': 42})

which maps the asset id (the index) to an area (the value). I have the the following dataframe where serm index is the columns names. 

data=pd.DataFrame(data={'ARD1': {0: 4.0, 1: 2.0, 2: 2.0, 3: 3.0, 4: 2.0},
'BUL1': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.0, 4: 0.0},
'BUL2': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.0, 4: 0.0},
'BSR1': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.0, 4: 0.0},
'BTR1': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.0, 4: 0.0},
'CR1': {0: 15.0, 1: 13.0, 2: 13.0, 3: 11.0, 4: 13.0},
'CRR1': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.0, 4: 0.0},
'CRE3': {0: 8.0, 1: 10.0, 2: 9.0, 3: 10.0, 4: 11.0},
'TAB1': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.0, 4: 0.0},
'NEP1': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.0, 4: 0.0},
'HAL1': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.0, 4: 0.0}})

I would like to sum the columns of data that fall in the same area, according to the mapping of serm. How can I achieve this (the more pandanoic the better)

Upvotes: 0

Views: 47

Answers (1)

jezrael
jezrael

Reputation: 863301

Use Index.map with groupby per columns and aggregate sum:

df = data.groupby(data.columns.map(serm.get), axis=1).sum()
print (df)
    37   42   49   52    53
0  0.0  0.0  0.0  0.0  27.0
1  0.0  0.0  0.0  0.0  25.0
2  0.0  0.0  0.0  0.0  24.0
3  0.0  0.0  0.0  0.0  24.0
4  0.0  0.0  0.0  0.0  26.0

Or assign columns back and use sum:

data.columns = data.columns.map(serm.get)
df = data.sum(level=0, axis=1)

Upvotes: 1

Related Questions