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.
CodePudding user response:
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