Rearrange dataframe using python

Question

I have a dataset that looks like this:

     Category     Date_x      Value_x     Date_y     Value_y
        A         01/01/2015   3          02/01/2015   5
        B         01/01/2015   6          02/01/2015   10
        C         01/01/2015   7          02/01/2015   5

Using Python, How can I change this data frame such that it appears like below:

     Category     Date_x  Value_x
        A         01/01/2015   3          
        A         02/01/2015   5
        B         01/01/2015   6          
        B         02/01/2015   10
        C         01/01/2015   7         
        C         02/01/2015   5

I'm still new to Pandas and your help will be greatly appreciated.

Edit: I only showed a part of the dataset. It actually is a big dataset that has like 100+ categories.

JoeCondron · Accepted Answer · 2015-07-11 18:02:54Z

1

It seems as though you merged or concatenated two dataframes with date, value and category columns on the horizoontal axis. In that case you should go back a step and concatenate them on the vertical axis. If not, assuming the index is 'Category', you can do

df_1 = df[['Date_x', 'Value_x']].rename(columns={'Date_x': 'Date', 'Value_x': 'Value'})
df_2 = df[['Date_y', 'Value_y']].rename(columns={'Date_y': 'Date', 'Value_y': 'Value'})

df = pd.concat([df1, df2]).sort_index()

answered Jul 11, 2015 at 18:02

JoeCondron

8,9263 gold badges29 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bob Haffner · Accepted Answer · 2015-07-11 18:13:23Z

I would probably use concat() for this

Starting with your frame

  Category      Date_x  Value_x      Date_y  Value_y
0        A  01/01/2015        3  02/01/2015        5
1        B  01/01/2015        6  02/01/2015       10
2        C  01/01/2015        7  02/01/2015        5

then select the y values and store in a new frame. rename them as well

dfY = df[["Category","Date_y","Value_y"]]
dfY.columns = ["Category","Date_x","Value_x"]
dfY

  Category      Date_x  Value_x
0        A  02/01/2015        5
1        B  02/01/2015       10
2        C  02/01/2015        5

then the concat with the x values from the original frame and the new "y" frame

pd.concat([df[["Category","Date_x","Value_x"]],dfY],ignore_index=True).sort(["Category","Date_x"])

       Category      Date_x           Value_x
0        A           01/01/2015        3
3        A           02/01/2015        5
1        B           01/01/2015        6
4        B           02/01/2015       10
2        C           01/01/2015        7
5        C           02/01/2015        5

Jianxun Li · Accepted Answer · 2015-07-11 18:27:03Z

It's much easier to accomplish your task if you rename your columns to multi-level columns first, and then reshape your dataframe using .stack().

import pandas as pd

# your data
# ===================================
print(df)


  Category      Date_x  Value_x      Date_y  Value_y
0        A  01/01/2015        3  02/01/2015        5
1        B  01/01/2015        6  02/01/2015       10
2        C  01/01/2015        7  02/01/2015        5

# processing
# =========================================
# put category column into index
df = df.set_index('Category')
# construct multi-level index based on the original columns
multi_level_columns = pd.MultiIndex.from_arrays(np.array([x.split('_') for x in df.columns.values]).T)
# replace the current column names with this new multi-level columns
df.columns = multi_level_columns


                Date Value        Date Value
                   x     x           y     y
Category                                    
A         01/01/2015     3  02/01/2015     5
B         01/01/2015     6  02/01/2015    10
C         01/01/2015     7  02/01/2015     5

# do stack
df.stack(level=1).reset_index(level=1, drop=True)


                Date  Value
Category                   
A         01/01/2015      3
A         02/01/2015      5
B         01/01/2015      6
B         02/01/2015     10
C         01/01/2015      7
C         02/01/2015      5

Collectives™ on Stack Overflow

Rearrange dataframe using python

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest