Python pandas dataframe: dataframe to another dataframe

Question

I need help in Python pandas dataframe. I have dataframe in below format(refer Current dataframe) and my requirement is to covert into another data frame in the different format which i have pasted below to it(refer desired dataframe). Can any one help me how can i achieve the desired output.

Current Dataframe

Name1                   Name2                       Score
BELFIUS INSURANCE       BELFIUS INSURANCES          0.79
BELFIUS INSURANCE       BELFIUS                     0.50
BELFIUS INSURANCE       BELFIUS T                   0.31
AIR PRODUCTS            AIR PRODUCT                 0.78
AIR PRODUCTS            AIR PRO                     0.63
AIR PRODUCTS            PRODUCTS                    0.39
ARDAGH GLASS            ARDAGH                      0.60

Desired dataframe

Name1               M1                 Score1    M2        Score2     M3                  Score3
BELFIUS INSURANCE   BELFIUS INSURANCES  0.79    BELFIUS    0.50       BELFIUS T           0.31
AIR PRODUCTS        AIR PRODUCT         0.78    AIR PRO    0.63       PRODUCTS            0.39
ARDAGH GLASS        ARDAGH              0.60    nan        nan        nan                 nan

Quang Hoang · Accepted Answer · 2020-09-30 16:34:18Z

1

This is essentially pivot by one column:

out_df = (df.assign(col=df.groupby('Name1').cumcount()+1)
   .pivot_table(index='Name1', columns='col', aggfunc='first')
   .swaplevel(0,1,axis=1)
   .sort_index(axis=1)
)
out_df.columns = [f"{y}{x}" if y=="Score" else f"M{x}" for x,y in out_df.columns]
out_df = out_df.reset_index()

Output:

    Name1              M1                    Score1  M2         Score2  M3           Score3
--  -----------------  ------------------  --------  -------  --------  ---------  --------
 0  AIR PRODUCTS       AIR PRODUCT             0.78  AIR PRO      0.63  PRODUCTS       0.39
 1  ARDAGH GLASS       ARDAGH                  0.6   nan        nan     nan          nan
 2  BELFIUS INSURANCE  BELFIUS INSURANCES      0.79  BELFIUS      0.5   BELFIUS T      0.31

answered Sep 30, 2020 at 16:34

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

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

Collectives™ on Stack Overflow

Python pandas dataframe: dataframe to another dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related