pandas, melt, unmelt preserve index

Question

I've got a table of clients (coper) and asset allocation (asset)

A = [[1,2],[3,4],[5,6]]
idx = ['coper1','coper2','coper3']
cols = ['asset1','asset2']

df = pd.DataFrame(A,index = idx, columns = cols)

so my data look like

        asset1  asset2
coper1       1       2
coper2       3       4
coper3       5       6

and I want to run them through a linear optimization (i've got constraints- somtehing like sum of all of asset_i <= amount_on_hand_i and sum of coper_j = price_j)

so I have to turn this 2D matrix into a 1D vector. Which is easy with melt

df2 = pd.melt(df,value_vars=['asset1','asset2'])

But now, when I try to unmelt it, I get a 6-row array with lots of blanks!

df2.pivot(columns = 'variable', values = 'value')


variable  asset1  asset2
0            1.0     NaN
1            3.0     NaN
2            5.0     NaN
3            NaN     2.0
4            NaN     4.0
5            NaN     6.0

Is there any way to preserve the 'coper' part of my indexing while using melt?

Answer 1

You need preserve index values by reset_index and parameter id_vars:

df2 = pd.melt(df.reset_index(), id_vars='index',value_vars=['asset1','asset2']) print (df2)     index variable  value 0  coper1   asset1      1 1  coper2   asset1      3 2  coper3   asset1      5 3  coper1   asset2      2 4  coper2   asset2      4 5  coper3   asset2      6 

Then pivot working nice:

print(df2.pivot(index='index',columns = 'variable', values = 'value')) variable  asset1  asset2 index                    coper1         1       2 coper2         3       4 coper3         5       6 

Another possible solution with stack:

df2 = df.stack().reset_index() df2.columns = list('abc') print (df2)         a       b  c 0  coper1  asset1  1 1  coper1  asset2  2 2  coper2  asset1  3 3  coper2  asset2  4 4  coper3  asset1  5 5  coper3  asset2  6  print(df2.pivot(index='a',columns = 'b', values = 'c')) b       asset1  asset2 a                      coper1       1       2 coper2       3       4 coper3       5       6 

Answer 2

set the ignore_index to be False to preserve the index, e.g.

df = df.melt(var_name=‘species’, value_name=‘height’, ignore_index = False) 

Answer 3

Looks like "optional argument keep_index to dataframe melt method" got into release 1.1: https://github.com/pandas-dev/pandas/issues/17440