Create column summary in df

Question

ACTION_NAME      ACTION_REASON         CH      PT    SK    CZ   UK
    Add Name         Name Change       No            Yes    ?   Yes
    Add Job          Job Change        Yes           No    No   ?
    Add Position     Postion Change    Yes           No    No   ?

I would need to to create 4 extra columns named:

• Countries Using this Action Reason
• Countries not Using this Action Reason
• Countries with Question Mark
• Countries that didn't answer (blank values)

In front of those columns, I would need to consolidate the countries that answered Yes, No, NO ANSWER, ? for each of those action/action reason looking like this (example of first row):

ACTION_NAME      ACTION_REASON      CH     PT      SK      CZ   UK          Countries using this action   Countries not Using this Action   Countries with Question Mark   Countries Blank
Add Name         Name Change        No             Yes     ?    Yes                          SK, UK                            CH                             CZ                     PT

What would be the best solution to create those consolidated columns?

Thank you so much!

Answer 1

First create a mapping dict to rename values (Yes, No, ?, '') then use melt to flatten your dataframe and finally pivot_table to reshape it:

MAPPING = {'Yes': 'Countries using this action',
           'No': 'Countries not Using this Action',
           '?': 'Countries with Question Mark',
           np.nan: 'Countries Blank'}

df1 = (df.melt(['ACTION_NAME', 'ACTION_REASON']).replace({'value': MAPPING})
         .pivot_table('variable', ['ACTION_NAME', 'ACTION_REASON'], 'value',
                      aggfunc=', '.join, sort=False)[MAPPING.values()]
         .rename_axis(columns=None).set_index(df.index))
out = pd.concat([df, df1], axis=1)

Output:

>>> out
    ACTION_NAME   ACTION_REASON   CH   PT  SK   CZ  UK Countries using this action Countries not Using this Action Countries with Question Mark Countries Blank
0      Add Name     Name Change   No  Yes   ?  Yes NaN                      PT, CZ                              CH                           SK              UK
1       Add Job      Job Change  Yes   No  No    ? NaN                          CH                          PT, SK                           CZ              UK
2  Add Position  Postion Change  Yes   No  No    ? NaN                          CH                          PT, SK                           CZ              UK