Change shape of dataframe with multiindex-CodePudding

How can change the shape of my multiindexed dataframe from:

to something like this, but with all cells values, not only of the first index:

I have tried to do it but somehow receive only the dataframe as above with this code:

numbers = [100,50,20,10,5,2,1]
for number in numbers:
    dfj[number] = df['First_column_value_name'].xs(key=number, level='Second_multiindex_column_name')

list_of_columns_position = []
for number in numbers:
    R_string = '{}_R'.format(number)
    list_of_columns_position.append(R_string)

df_positions_as_columns = pd.concat(dfj.values(), ignore_index=True, axis=1) df_positions_as_columns.columns = list_of_columns_position

CodePudding user response：

Split your first columns into 2 parts then join the result with the second column and finally pivot your dataframe:

Setup:

data = {'A': ['TLM_1/100', 'TLM_1/50', 'TLM_1/20',
              'TLM_2/100', 'TLM_2/50', 'TLM_2/20'],
        'B': [11, 12, 13, 21, 22, 23]}
df = pd.DataFrame(data)
print(df)

# Output:
           A   B
0  TLM_1/100  11
1   TLM_1/50  12
2   TLM_1/20  13
3  TLM_2/100  21
4   TLM_2/50  22
5   TLM_2/20  23

>>> df[['B']].join(df['A'].str.split('/', expand=True)) \
             .pivot(index=0, columns=1, values='B') \
             .rename_axis(index=None, columns=None) \
             .add_suffix('_R')

       100_R  20_R  50_R
TLM_1     11    13    12
TLM_2     21    23    22

CodePudding user response：

use a regular expression to split the label column into two columns a and b then group by column a and unstack the grouping.