converse columns and elements in pandas DataFrame-CodePudding

I have a pandas data frame like this:

list_dict=[{'good':'Ahmad', 'bad':'Reza', 'ugly':'Ali'},
           {'good':'David', 'bad':'Frank', 'ugly':'Alice'},
           {'good':'Ali', 'bad':'Alice', 'ugly':'Bob'}]
df=pd.DataFrame(list_dict)

    good       bad         ugly
0  'Ahmad'    'Reza'      'Ali'
1  'David'    'Frank'     'Alice'
2  'Ali'      'Alice'     'Bob'

And need to converse columns and elements in this data frame like this:

     Ahmad      Reza    Ali     David    Frank    Alice   Bob
0    'good'     'bad'   'ugly'  NaN       NaN      NaN    NaN
1     NaN        NaN     NaN    'good'   'bad'    'ugly'  NaN
2     NaN        NaN    'good'  NaN       NaN     'bad'  'ugly'

One way is convert each rows of the initial dataframe to dictionary, Then swap dictionary items and finally create the target data frame from new dictionaries. could anyone help me if there is a better way to solve this problem?

CodePudding user response：

Use DataFrame.melt with DataFrame.pivot:

df1 = (df.melt(ignore_index=False)
        .reset_index()
        .pivot(index='index', columns='value', values='variable')
        .rename_axis(index=None, columns=None))
print (df1)
  Ahmad   Ali Alice   Bob David Frank Reza
0  good  ugly   NaN   NaN   NaN   NaN  bad
1   NaN   NaN  ugly   NaN  good   bad  NaN
2   NaN  good   bad  ugly   NaN   NaN  NaN

If order is important add DataFrame.reindex by not sorted unique values of DataFrame:

df2 = (df.melt(ignore_index=False)
        .reset_index()
        .pivot(index='index', columns='value', values='variable')
        .rename_axis(index=None, columns=None)
        .reindex(pd.unique(np.ravel(df)), axis=1))

print (df2)
  Ahmad Reza   Ali David Frank Alice   Bob
0  good  bad  ugly   NaN   NaN   NaN   NaN
1   NaN  NaN   NaN  good   bad  ugly   NaN
2   NaN  NaN  good   NaN   NaN   bad  ugly