DataFrame: add same data for different column and merge the whole file

Question

I have a DataFrame which looks like this:

Name Year Jan Feb Mar Apr
Bee  1998 26  23  22  19
Cee  1999 43  23  43  23

I want to change the DataFrame into something like this:

Name Year Mon Val
Bee  1998 1   26
Bee  1998 2   23
Bee  1998 3   22
Bee  1998 4   19
Cee  1999 1   43
Cee  1999 2   23
Cee  1999 3   43
Cee  1999 4   23

How do i acquire this in Python with Pandas or any other library?

Possible duplicate of [pandas convert some columns into rows](https://stackoverflow.com/questions/28654047/pandas-convert-some-columns-into-rows) — Georgy, Feb 01 '18 at 17:50

score 0 · Accepted Answer · answered Feb 01 '18 at 17:49

First, reshape your DataFrame with pd.DataFrame.melt:

df = df.melt(id_vars=['Name', 'Year'], var_name='Mon', value_name='Value')

...and then convert your Mon values to datetime values, and extract the month number:

df.loc[:, 'Mon'] = pd.to_datetime(df['Mon'], format='%b').dt.month

#   Name  Year  Mon  Value
# 0  Bee  1998    1     26
# 1  Cee  1999    1     43
# 2  Bee  1998    2     23
# 3  Cee  1999    2     23
# 4  Bee  1998    3     22
# 5  Cee  1999    3     43
# 6  Bee  1998    4     19
# 7  Cee  1999    4     23

score 0 · Answer 2 · answered Feb 01 '18 at 17:52

df = df.set_index(['Name', 'Year'])
df.columns = pd.to_datetime(df.columns, format='%b').month
df.stack()

returns

Name  Year   
Bee   1998  1    26
            2    23
            3    22
            4    19
Cee   1999  1    43
            2    23
            3    43
            4    23
dtype: int64

DataFrame: add same data for different column and merge the whole file

2 Answers2