Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Change shape of dataframe with multiindex

How can change the shape of my multiindexed dataframe from:

enter image description here

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

enter image description here

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

>Solution :

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
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading