Ivon Ding Ivon Ding - 2 months ago 38
Python Question

How to reshape this dataframe

I need to reshape the dataframe like this:

nam code date1 date2
0 a 1 1/1 1/2
1 b 3 3/4 4/5


but
df.stack
seems useless in this situation.

Desired output:

nam code date
0 a 1 1/1
1 a 1 1/2
2 b 3 3/4
3 b 3 4/5

Answer Source

You can use lreshape, sort_values by column nam, reindex_axis columns and last reset_index:

print pd.lreshape(df, {'date': ['date1', 'date2']})
        .sort_values('nam')
        .reindex_axis(['nam','code','date'], axis=1)
        .reset_index(drop=True)

  nam  code date
0   a     1  1/1
1   a     1  1/2
2   b     3  3/4
3   b     3  4/5

Another solution with melt, drop for droping column variable, sort_values by column nam and last reset_index:

print pd.melt(df, id_vars=['nam','code'], value_name='date')
        .drop('variable', axis=1)
        .sort_values('nam')
        .reset_index(drop=True)

  nam  code date
0   a     1  1/1
1   a     1  1/2
2   b     3  3/4
3   b     3  4/5

EDIT:

lreshape is now undocumented, but is possible in future will by removed (with pd.wide_to_long too).

Possible solution is merging all 3 functions to one - maybe melt, but now it is not implementated. Maybe in some new version of pandas. Then my answer will be updated.