Unmelt a pandas dataframe to remove NaN

Question

Given the following data:

s = '{"PassengerId":{"0":1,"1":2,"2":3},"Survived":{"0":0,"1":1,"2":1},"Pclass":{"0":3,"1":1,"2":3}}'
df = pd.read_json(s)

Which looks as:

   PassengerId  Survived  Pclass
0            1         0       3
1            2         1       1
2            3         1       3

Assume that it has been melted to

m = df.melt()
print(m)

      variable  value
0  PassengerId      1
1  PassengerId      2
2  PassengerId      3
3     Survived      0
4     Survived      1
5     Survived      1
6       Pclass      3
7       Pclass      1
8       Pclass      3

I would like to know how to revert the melted m to the original df.

I have tried something similar to the following:

m=df.melt().pivot(columns='variable', values='value').reset_index(drop=True)
m.columns.name = None

which gives

   PassengerId  Pclass  Survived
0          1.0     NaN       NaN
1          2.0     NaN       NaN
2          3.0     NaN       NaN
3          NaN     NaN       0.0
4          NaN     NaN       1.0
5          NaN     NaN       1.0
6          NaN     3.0       NaN
7          NaN     1.0       NaN
8          NaN     3.0       NaN

as can be seen, each row contains information about a single column only, there are lots of NaN values in there that I would like to lose.

@YOBEN_S this is *not* a duplicate - there is not an ID column in the example I have given — baxx, Apr 26 '20 at 14:08
*How do I convert a DataFrame from long to wide by pivoting on ONLY two columns? Given,* — BENY, Apr 26 '20 at 14:10
@YOBEN_S the answer has additional information that's relevant - are you going to update the thread you've linked with that information? — baxx, Apr 26 '20 at 14:13

score 3 · Accepted Answer · answered Apr 26 '20 at 13:15

Use GroupBy.cumcount for new column used for index parameter in DataFrame.pivot:

m['new'] = m.groupby('variable').cumcount()

df = m.pivot(columns='variable', values='value', index='new')
print (df)

variable  PassengerId  Pclass  Survived
new                                    
0                   1       3         0
1                   2       1         1
2                   3       3         1

Or:

df = (m.assign(new = m.groupby('variable').cumcount())
       .pivot(columns='variable', values='value', index='new'))
print (df)

variable  PassengerId  Pclass  Survived
new                                    
0                   1       3         0
1                   2       1         1
2                   3       3         1

Unmelt a pandas dataframe to remove NaN

1 Answers1