Problem Setup & Goal

I have a Multindexed Pandas DataFrame that looks like this:

import pandas as pd

df = pd.DataFrame({
    'Values':[1, 3, 4, 8, 5, 2, 9, 0, 2],
    'A':['A1', 'A1', 'A1', 'A1', 'A2', 'A2', 'A3', 'A3', 'A3'],
    'B':['foo', 'bar', 'fab', 'baz', 'foo', 'baz', 'qux', 'baz', 'bar']
})
df.set_index(['A','B'], inplace=True)
print(df.to_string())

        Values
A  B          
A1 foo       1
   bar       3
   fab       4
   baz       8
A2 foo       5
   baz       2
A3 qux       9
   baz       0
   bar       2

My ultimate goal is to replace all the "bar" and "baz" rows in the B column with a summed row called "other" (see below) in the simplest, most canonical Pandas way.

       Values
A  B          
A1 foo       1
   fab       4
   other    11
A2 foo       5
   other     2
A3 qux       9
   other     2

Current Work

I managed to figure out how to create a mask for a MultiIndex DataFrame from a similar problem to highlight the rows we want to eventually aggregate, which are in an agg_list.

agg_list = ['bar', 'baz']
# Create a mask that highlights the rows in B that are in agg_list
filterFunc = lambda x: x.index.get_level_values('B') in agg_list
mask = df.groupby(level=['A','B']).apply(filterFunc)

This produces the expected mask:

print(mask.to_string())

A   B  
A1  bar     True
    baz     True
    fab    False
    foo    False
A2  baz     True
    foo    False
A3  bar     True
    baz     True
    qux    False

And I know how to remove the rows I no longer need:

# Remove rows in B col that are in agg_list using mask
df_masked = df[[~mask.loc[i1, i2] for i1,i2 in df.index]]
print(df_masked.to_string())

    Values
A  B          
A1 foo       1
   fab       4
A2 foo       5
A3 qux       9

But I don't know how to do the actual aggregation/sum on these rows and append it to each Multindexed row.

Similar Problems/Solutions

Similar problems I've seen didn't involve a Multindex DataFrame, so I can't quite use some of the solutions like this one, which has the same general idea of creating a mask and then append a summed row:

threshold = 6
m = df['value'] < threshold
df1 = df[~m].copy()
df1.loc['Z'] = df.loc[m, 'value'].sum()

m = df['value'] < threshold
df1 = df[~m].append(df.loc[m, ['value']].sum().rename('Z'))

CodePudding user response：

Here is one way which resets the index for just B, performs a replace and aggregates the values.

agg_list = ['bar', 'baz']
(df.reset_index(level=1)
.replace({'B':{'|'.join(agg_list):'other'}},regex=True)
.groupby(['A','B']).sum())

Another way is to create a new MultiIndex with bar and baz being replaced with other.

(df.set_axis(pd.MultiIndex.from_arrays([df.index.get_level_values(0),
df.index.get_level_values(1).str.replace('|'.join(agg_list),'other')]))
.groupby(level=[0,1]).sum())

Output:

          Values
A  B            
A1 fab         4
   foo         1
   other      11
A2 foo         5
   other       2
A3 other       2
   qux         9

CodePudding user response：

You can do something as simple as below

b_others = df.B.replace({'bar': 'other', 'baz': 'other'})
df.groupby(['A', b_others]).sum()

Create the variable with replaced 'bar' and 'baz' values. Then, just use it to group.

Output

          Values
A  B            
A1 fab         4
   foo         1
   other      11
A2 foo         5
   other       2
A3 other       2
   qux         9

CodePudding user response：

One option would be to rename axis=0 and level=1 so that bar and baz become other then do a standard groupby sum:

df = df.rename(
    axis=0,
    level=1,
    mapper={'bar': 'other', 'baz': 'other'}
).groupby(level=['A', 'B']).sum()

A bit more generic solution could look like:

from typing import List, Dict


def map_to_value(
        values_to_map: List[str],
        default_value: str
) -> Dict[str, str]:
    return {k: default_value for k in values_to_map}


df = df.rename(
    axis=0,
    level=1,
    mapper=map_to_value(['bar', 'baz'], 'other')
).groupby(level=['A', 'B']).sum()

Either approach results in:

          Values
A  B            
A1 fab         4
   foo         1
   other      11
A2 foo         5
   other       2
A3 other       2
   qux         9

Alternatively, with a bit more work, we can instead keep a set of known values:

from typing import List, Callable


def keep_and_default(
        keep_values: List[str], default_value: str
) -> Callable[[str], str]:
    mapping = {k: k for k in keep_values}

    def mapper(c: str) -> str:
        return mapping.get(c, default_value)

    return mapper


df = df.rename(
    axis=0,
    level=1,
    mapper=keep_and_default(['foo', 'fab', 'qux'], 'other')
).groupby(level=['A', 'B']).sum()

In this case, this also results in:

          Values
A  B            
A1 fab         4
   foo         1
   other      11
A2 foo         5
   other       2
A3 other       2
   qux         9