data manipulation example from wide to long in python

Question

I've just placed a similar question here and got an answer but recognised, that by adding a new column to a DataFrame the presented solution fails as the problem is a bit different.

I want to go from here:

import pandas as pd

df = pd.DataFrame({'ID': [1, 2],
                   'Value_2013': [100, 200],
                   'Value_2014': [245, 300],
                   'Value_2016': [200, float('NaN')]})

print(df)

    ID  Value_2013  Value_2014  Value_2016
0   1         100         245       200.0
1   2         200         300         NaN

to:

df_new = pd.DataFrame({'ID': [1, 1, 1, 2, 2],
                       'Year': [2013, 2014, 2016, 2013, 2014],
                       'Value': [100, 245, 200, 200, 300]})

print(df_new)

    ID  Value  Year
0   1    100  2013
1   1    245  2014
2   1    200  2016
3   2    200  2013
4   2    300  2014

Any ideas how I can face this challenge?

Answer 1

The pandas.melt() method gets you halfway there. After that it's just some minor cleaning up.

df = pd.melt(df, id_vars='ID', var_name='Year', value_name='Value')
df['Year'] = df['Year'].map(lambda x: x.split('_')[1])
df = df.dropna().astype(int).sort_values(['ID', 'Year']).reset_index(drop=True)
df = df.reindex_axis(['ID', 'Value', 'Year'], axis=1)

---

print(df)
   ID  Value  Year
0   1    100  2013
1   1    245  2014
2   1    200  2016
3   2    200  2013
4   2    300  2014

Answer 2

You need add set_index first:

df = df.set_index('ID')
df.columns = df.columns.str.split('_', expand=True)
df = df.stack().rename_axis(['ID','Year']).reset_index()
df.Value = df.Value.astype(int)
#if order of columns is important
df = df.reindex_axis(['ID','Value','Year'], axis=1)
print (df)
   ID  Value  Year
0   1    100  2013
1   1    245  2014
2   1    200  2016
3   2    200  2013
4   2    300  2014