Pandas transform table to occurrence

Question

Hi I have a table similar to below

  Fruits     America Europe Asia
0 Apple      Good   N/A   Bad
1 Orange     N/A    Bad  Good

and I would like to transform into something like this

 Fruits and Region   Good Bad N/A
0 Apple America         1   0  0
1 Apple Europe.         0.  0. 1
2 Apple Asia.           0   1. 0
3 Orange America.       0.  0. 1
4 Orange Europe.        0.  1. 0
5 Orange Asia.          1.  0. 0

I have tried the stack function but it doesn't work as expected.

Thank you

Answer 1

You can melt and crosstab:

df2 = df.melt('Fruits', var_name='Region')

out = (
  pd.crosstab(df2['Fruits'].add(' '+df2['Region']).rename('Fruits and Region'),
              df2['value'].fillna('N/A'), dropna=False)
    .rename_axis(columns=None).reset_index()  
)

Output:

  Fruits and Region  Bad  Good  N/A
0     Apple America    0     1    0
1        Apple Asia    1     0    0
2      Apple Europe    0     0    1
3    Orange America    0     0    1
4       Orange Asia    0     1    0
5     Orange Europe    1     0    0