Change table to high format with panda (UNPIVOT)

Question

I have a table like this

   user         company company2 company3 company4
    1           Mac     Lenovo    Hp      null              
    2           Mac       MSI     Sony                          

And using pandas, I would like it to be

     user    company
     1          Mac
     1          Lenovo
     1          Hp
     2         Mac

etc. Here I tried, but did not work with pandas pivot.

dataframe = pd.read_csv('data.csv')
dataframe.fillna(value='', inplace=True)
#dataframe.pivot(index='user', columns='company')

The above code does not work and gives an error.

Answer 1

you can use pd.melt method:

In [211]: pd.melt(df, id_vars='user', value_vars=df.columns.drop('user').tolist())
Out[211]:
   user  variable   value
0     1   company     Mac
1     2   company     Mac
2     1  company2  Lenovo
3     2  company2     MSI
4     1  company3      Hp
5     2  company3    Sony
6     1  company4    null
7     2  company4     NaN

or

In [213]: pd.melt(df,
                  id_vars='user', value_vars=df.columns.drop('user').tolist(),
                  value_name='Company') \
            .drop('variable',1)
Out[213]:
   user Company
0     1     Mac
1     2     Mac
2     1  Lenovo
3     2     MSI
4     1      Hp
5     2    Sony
6     1    null
7     2     NaN

UPDATE: discards NaN and sorts the resulting DF with user:

In [218]: pd.melt(df,
     ...:         id_vars='user', value_vars=df.columns.drop('user').tolist(),
     ...:         value_name='Company') \
     ...:   .drop('variable',1) \
     ...:   .dropna() \
     ...:   .sort_values('user')
     ...:
Out[218]:
   user Company
0     1     Mac
2     1  Lenovo
4     1      Hp
6     1    null
1     2     Mac
3     2     MSI
5     2    Sony

PS, if you want to get rid of the values null, use df.replace('null', np.nan)instead df:

In [219]: pd.melt(df.replace('null', np.nan),
     ...:         id_vars='user', value_vars=df.columns.drop('user').tolist(),
     ...:         value_name='Company') \
     ...:   .drop('variable',1) \
     ...:   .dropna() \
     ...:   .sort_values('user')
     ...:
Out[219]:
   user Company
0     1     Mac
2     1  Lenovo
4     1      Hp
1     2     Mac
3     2     MSI
5     2    Sony

Answer 2

You can use it for it stack(I don’t know if this is more efficient melt:

dataframe.set_index("user").stack().reset_index(-1, drop=True)

user
1       Mac
1    Lenovo
1        Hp
2       MSI
2       Mac
2      Sony

( MultiIndex) - , DataFrame. , DataFrame

   C1 C2
0  A  B
1  a  b

stack() Series

0  C1 A
0  C2 B
1  C1 a
1  C2 b