Multi-index tuple join

January 5, 2023

I have the following column of a multiple index tuple in this form:

df_incumbent_Q.columns

Which looks like this:

MultiIndex([('Cust Linehaul', '2022Q2'),
            ('Cust Linehaul', '2022Q3'),
            ('Cust Linehaul', '2022Q4'),
            (  'Load Number', '2022Q2'),
            (  'Load Number', '2022Q3'),
            (  'Load Number', '2022Q4')],
           names=[None, 'Quarter'])

I am trying to combine the columns name so that I have something like this:

           ['Cust Linehaul_2022Q2',
            'Cust Linehaul_2022Q3',
            'Cust Linehaul_2022Q4',
              'Load Number_2022Q2',
              'Load Number_2022Q3',
              'Load Number_2022Q4']

I tried to join them this way

df_incumbent_Q.columns =[''.join(str(c)) for c in df_incumbent_Q.columns]

but it comes back with Period aggregation.

Index(['('Cust Linehaul', Period('2022Q2', 'Q-DEC'))',
   '('Cust Linehaul', Period('2022Q3', 'Q-DEC'))',
   '('Cust Linehaul', Period('2022Q4', 'Q-DEC'))',
   '('Load Number', Period('2022Q2', 'Q-DEC'))',
   '('Load Number', Period('2022Q3', 'Q-DEC'))',
   '('Load Number', Period('2022Q4', 'Q-DEC'))'],
  dtype='object')

Then I tried my luck with str.replace method, but can’t get rid of the Period( ) parenthesis.

pattern = '|'.join(["Period","'Q-DEC'",''])
df_incumbent_Q.columns.str.replace(pattern,'',regex=True)

The output is like this

Index(['('Cust Linehaul', ('2022Q2', ))', '('Cust Linehaul', ('2022Q3', ))',
   '('Cust Linehaul', ('2022Q4', ))', '('Load Number', ('2022Q2', ))',
   '('Load Number', ('2022Q3', ))', '('Load Number', ('2022Q4', ))'],
  dtype='object')

Ideally I want to solve it through .join() method if that doesn’t work I want to solve it through .replace method.

>Solution :

Try:

df_incumbent_Q.columns =[f'{a}_{b}' for a, b in df_incumbent_Q.columns]

Prints:

   Cust Linehaul_2022Q2  Cust Linehaul_2022Q3  Cust Linehaul_2022Q4  Load Number_2022Q2  Load Number_2022Q3  Load Number_2022Q4
0                   NaN                   NaN                   NaN                 NaN                 NaN                 NaN
1                   NaN                   NaN                   NaN                 NaN                 NaN                 NaN

Input dataframe:

        Cust Linehaul               Load Number              
Quarter        2022Q2 2022Q3 2022Q4      2022Q2 2022Q3 2022Q4
0                 NaN    NaN    NaN         NaN    NaN    NaN
1                 NaN    NaN    NaN         NaN    NaN    NaN