CODEWITHSUNDEEP
pythonpandasdataframe
Geetanjali Kumar
Geetanjali Kumar

Reputation: 27

Python pandas dataframe: dataframe to another dataframe

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

Upvotes: 0

Views: 93

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150745

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

Upvotes: 1

Related Questions