Find The Mode Of Multiple Columns For Each Unique Value

I have a dataset with an ID column and 10 different regular services column and 1 premium service column. A person could come in multiple times, so IDs can repeat. During a person

Solution 1:

I'm not sure what's wrong with your original code but here's one solution:

import pandas as pd
from itertools import chain

>>>df
   Service1 Service2 Service3 Service10
ID
1         A        B        C         Z
1         B        C        D         Y
1         A        B        C         O
2         R        S        T         B

df_regsvc = df.groupby(df.index)['Service1','Service2','Service3','Service10'] \
    .apply(lambda x : list(chain.from_iterable([*x.values]))) \
    .apply(lambda x: max(x, key=x.count)).to_frame()

>>>df_regsvc
ID
1    B
2    R
dtype: object

# Join it with the aggregate for the Premium column
df_premium = df.groupby(df.index)['Premium'].agg(lambda x: pd.Series.mode(x)[0]).to_frame()
df_agg = df_regsvc.join(df_premium)

>>>df_agg
    0 Premium
ID
1   B         XX
2   R         XX