Transform Dataframe rows to columns and additional steps

user_9093700 0
Viewed 21

I have a dataframe like this:

import pandas as pd
import numpy as np

data={'UNIT':['UNIT_1','UNIT_2','UNIT_3','UNIT_4'],
      'Name_1':[ 'werner', 'otto', 'karl', 'fritz'], 
      'Name_2':[ 'ottilie', 'anna', 'jasmin', ''],
      'Name_3':[ 'bello', 'kitti', '', '']}

df=pd.DataFrame(data)

df.replace('', np.nan, inplace=True)
display(df)

which looks like this:

enter image description here

The result that I want is this:

enter image description here

The code I have so far looks like this:

for index, row in df.iterrows():
    row_transposed = row.T
    row_transposed.dropna(inplace=True)
    df_row_transposed = pd.DataFrame(row_transposed)
    df_row_transposed_head = df_row_transposed.head(1)
    #display(df_row_transposed)
    #display(row_transposed_head)
    hr_unit = df_row_transposed_head.iloc[0]
    add_unit = (hr_unit[index])
    for index, row in df_row_transposed.iterrows():
        df_row_transposed["UNIT"] = add_unit
    #row_transposed = row_transposed.iloc[index: , :]    
    
    display(df_row_transposed)

which already creates this:

enter image description here

but now I am stuck...

Any help is very much appriciated

transform dataframe
1 Answers

Try df.melt. It will help you to unstack the column.

ddf = df.melt(id_vars='UNIT').sort_values(by='UNIT')

new_df = ddf[["value","UNIT"]]
new_df.dropna().reset_index(drop=True,inplace=True)

new_df
Out[163]: 
     value    UNIT
0   werner  UNIT_1
1  ottilie  UNIT_1
2    bello  UNIT_1
3     otto  UNIT_2
4     anna  UNIT_2
5    kitti  UNIT_2
6     karl  UNIT_3
7   jasmin  UNIT_3
8    fritz  UNIT_4
user_168360780
Related