How To Pivot Table By Parentauction Column In Such A Way That I Get Output Shown In Picture

this a csv file visualized in ms excel how do i use pandas pivot table to get output in such a way that all the Make gets segmented by their respective ParentAuction values like th

Solution 1:

Let's try this:

df.set_index(['Make','ParentAuction']).unstack().swaplevel(0,1,axis=1).sort_index(axis=1)

Output:

ParentAuction          Copart                                   IAA          \
              AVG GrossProfit AVG PMV  Loss%  Sales AVG GrossProfit AVG PMV   
Make                                                                          
Acura                  112.99  -15.53  36.46   96.0             NaN     NaN   
Audi                   150.85  -13.04  32.95   88.0             NaN     NaN   
BMW                    134.39  -14.65  34.91  212.0          185.62  -11.92   
Buick                    6.35  -29.42  46.97   66.0           90.90  -26.47   
Cadillac                91.71  -17.88  41.46   82.0             NaN     NaN   
Chevrolet              133.87  -14.06  35.82  776.0          150.29  -12.04   
Chrysler                83.15   17.14  38.66  194.0             NaN     NaN   
Dodge                   99.07  -18.68  37.60  383.0          154.23  -12.10   
Ford                   122.57  -15.88  37.79  979.0          169.51  -12.58   
GMC                    107.94  -16.63  41.45  152.0          113.92  -13.19   

ParentAuction                 
               Loss%   Sales  
Make                          
Acura            NaNNaN  
Audi             NaNNaN  
BMW            27.14210.0  
Buick          47.2272.0  
Cadillac         NaNNaN  
Chevrolet      29.82912.0  
Chrysler         NaNNaN  
Dodge          31.46426.0  
Ford           30.691284.0  
GMC            33.08133.0

Solution 2:

Instead of values as 'ParentAuction' use it as columns parameter i.e much like @Scott but using pivot table.

df.pivot_table(index='Make',columns=['ParentAuction']).swaplevel(0,1,axis=1).sort_index(axis=1)

ParentAuction          Copart                                   IAA          \
              AVG GrossProfit AVG PMV  Loss%  Sales AVG GrossProfit AVG PMV   
Make                                                                          
Acura                  112.99  -15.53  36.46   96.0             NaN     NaN   
Audi                   150.85  -13.04  32.95   88.0             NaN     NaN   
BMW                    134.39  -14.65  34.91  212.0          185.62  -11.92   
Buick                    6.35  -29.42  46.97   66.0           90.90  -26.47   
Cadillac                91.71  -17.88  41.46   82.0             NaN     NaN   
Chevrolet              133.87  -14.06  35.82  776.0          150.29  -12.04   
Chrysler                83.15   17.14  38.66  194.0             NaN     NaN   
Dodge                   99.07  -18.68  37.60  383.0          154.23  -12.10   
Ford                   122.57  -15.88  37.79  979.0          169.51  -12.58   
GMC                    107.94  -16.63  41.45  152.0          113.92  -13.19   

ParentAuction                 
               Loss%   Sales  
Make                          
Acura            NaN     NaN  
Audi             NaN     NaN  
BMW            27.14   210.0  
Buick          47.22    72.0  
Cadillac         NaN     NaN  
Chevrolet      29.82   912.0  
Chrysler         NaN     NaN  
Dodge          31.46   426.0  
Ford           30.69  1284.0  
GMC            33.08   133.0  

Solution 3:

You need to add the 'aggfunc' parameter. Something like this:

pd.pivot_table(df,columns=['Make','Sales','AVG PMV','AVG GrossProfit','Loss%'],values=['ParentAuction'], aggfunc = 'count')