Dataframe transformation by taking month columns into rows

Question

The original dataframe is as follows:

And I would like to change it into this way:

Answer 1

You can try zipping the number columns to list with row index and explode the zipped column then convert row index and value to multiple columns

out = pd.concat([df['year'], df[df.columns[1:]].apply(lambda row: list(enumerate(row.tolist())), axis=1)], axis=1)
out = out.explode(0, ignore_index=True)
out = pd.concat([out['year'], pd.DataFrame(out[0].to_list(), columns=['gid','amount'])], axis=1)

print(out)

   year  gid  amount
0  2010    0       1
1  2010    1       4
2  2010    2       7
3  2010    0       2
4  2010    1       5
5  2010    2       8
6  2010    0       3
7  2010    1       6
8  2010    2       9

At last, add the row index to year column with padding

out['year'] = out['year'].astype(str) + '-' + out['gid'].add(1).astype(str).str.zfill(2)
out = out.drop(columns='gid')

print(out)

      year  amount
0  2010-01       1
1  2010-02       4
2  2010-03       7
3  2010-01       2
4  2010-02       5
5  2010-03       8
6  2010-01       3
7  2010-02       6
8  2010-03       9

---

You can also try melt

out = df.melt(id_vars=['year'], value_name='amount')
out['year'] = out['year'].astype(str) + '-' + out.groupby(out.groupby('variable').cumcount()).cumcount().add(1).astype(str).str.zfill(2)
out = out.drop(columns='variable')

print(out)

      year  amount
0  2010-01       1
1  2010-01       2
2  2010-01       3
3  2010-02       4
4  2010-02       5
5  2010-02       6
6  2010-03       7
7  2010-03       8
8  2010-03       9