CODEWITHSUNDEEP
python-3.xpandasdataframedata-analysis
VeilEclipse
VeilEclipse

Reputation: 2856

Pandas Pivot table without aggregating

I have a dataframe df as:

Acct_Id  Acct_Nm   Srvc_Id   Phone_Nm   Phone_plan_value   Srvc_Num
51       Roger     789       Pixel      30                 1
51       Roger     800       iPhone     25                 2
51       Roger     945       Galaxy     40                 3
78       Anjay     100       Nokia      50                 1
78       Anjay     120       Oppo       30                 2
32       Rafa      456       HTC        35                 1

I want to transform the dataframe so I can have 1 row per Acct_Id and Acct_Nm as:

    Acct_Id   Acct_Nm    Srvc_Num_1                             Srvc_Num_2                              Srvc_Num_3
                         Srvc_Id   Phone_Nm   Phone_plan_value  Srvc_Id   Phone_Nm   Phone_plan_value   Srvc_Id   Phone_Nm   Phone_plan_value
          51  Roger      789       Pixel      30                800       iPhone     25                 945       Galaxy     40
          78  Anjay      100       Nokia      50                120       Oppo       30
          32  Rafa       456       HTC        35

I am not sure how to achieve the same in pandas.

Upvotes: 1

Views: 4999

Answers (2)

BENY
BENY

Reputation: 323316

More like a pivot problem , but need swaplevel and sort_index

df.set_index(['Acct_Id','Acct_Nm','Srvc_Num']).\
   unstack().\
   swaplevel(1,0,axis=1).\
   sort_index(level=0,axis=1).add_prefix('Srvc_Num_')


Out[289]: 

Srvc_Num               Srvc_Num_1                                             \
                Srvc_Num_Phone_Nm Srvc_Num_Phone_plan_value Srvc_Num_Srvc_Id   
Acct_Id Acct_Nm                                                                
32      Rafa                  HTC                      35.0            456.0   
51      Roger               Pixel                      30.0            789.0   
78      Anjay               Nokia                      50.0            100.0   
Srvc_Num               Srvc_Num_2                                             \
                Srvc_Num_Phone_Nm Srvc_Num_Phone_plan_value Srvc_Num_Srvc_Id   
Acct_Id Acct_Nm                                                                
32      Rafa                 None                       NaN              NaN   
51      Roger              iPhone                      25.0            800.0   
78      Anjay                Oppo                      30.0            120.0   
Srvc_Num               Srvc_Num_3                                             
                Srvc_Num_Phone_Nm Srvc_Num_Phone_plan_value Srvc_Num_Srvc_Id  
Acct_Id Acct_Nm                                                               
32      Rafa                 None                       NaN              NaN  
51      Roger              Galaxy                      40.0            945.0  
78      Anjay                None                       NaN              NaN

And here is pivot_table 

pd.pivot_table(df,index=['Acct_Id','Acct_Nm'],columns=['Srvc_Num'],values=['Phone_Nm','Phone_plan_value','Srvc_Id'],aggfunc='first')

Upvotes: 4

smj
smj

Reputation: 1284

How about:

df.set_index(['Acct_Id', 'Acct_Nm', 'Srvc_Num']).unstack().swaplevel(0, 1, axis = 1).sort_index(axis = 1)

Upvotes: 2

Related Questions