0

As shown below is a data frame contain Car Price data. The issue is due to there's many Car Model (8 types), the data was not display in a user friendly way. Is there a way to restructure it to following desired output so that the output is more user-friendly.

[Content updated]

PS: Regarding the Car Model , not all car Model present in every year. For instance, for 2000, Car Model Car H was not presented. Using pivot function would result in NaN value. Aside from pivot, is there another way to do it?

Dataframe

import pandas as pd 
j = [
 {
   "Year":"2000",
   "Car Model":"Car A",
   "Car price":"1000"  
},
 {
   "Year":"2000",
   "Car Model":"Car B",
   "Car price":"1200"
},
    { 
   "Year":"2000",
   "Car Model":"Car C",
   "Car price":"1300"
}, 
    {
     "Year":"2000",
   "Car Model":"Car D",
   "Car price":"1100"  
}, 
    {
    "Year":"2000",
   "Car Model":"Car E",
   "Car price":"1400" 
},
    {
       "Year":"2000",
   "Car Model":"Car F",
   "Car price":"1000"  
}, 
    {        
        "Year":"2000",
   "Car Model":"Car G",
   "Car price":"1200" 
},   
    {
   "Year":"2001",
   "Car Model":"Car A",
   "Car price":"1400"
},
       {
   "Year":"2001",
   "Car Model":"Car C",
   "Car price":"1200"
}, 
          {
   "Year":"2001",
   "Car Model":"Car D",
   "Car price":"1300"
},     
             {
   "Year":"2001",
   "Car Model":"Car E",
   "Car price":"1000"
},    
                 {
   "Year":"2001",
   "Car Model":"Car F",
   "Car price":"1200"
},    
                     {
   "Year":"2001",
   "Car Model":"Car G",
   "Car price":"1200"
},          
                     {
   "Year":"2001",
   "Car Model":"Car H",
   "Car price":"1000"
}
]

Get the mean

out = pd.DataFrame(j).groupby(['Year','Car Model'],as_index=False)[['Car price']].agg(lambda x : x.astype(int).mean())
out

output

enter image description here

Reshape data

res = out.pivot(index='Year', columns='Car Model', values='Car price')

res

output with NaN value

enter image description here

Targeted output

enter image description here

epiphany
  • 756
  • 1
  • 11
  • 29
  • Can you post that data in readable format and not as an image please? – Danail Petrov Jan 02 '21 at 11:33
  • 1
    Does [this](https://stackoverflow.com/questions/52541982/pandas-transpose-one-column), or [this](https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe) answer your question? – Thymen Jan 02 '21 at 11:34
  • @Thymen, as not all car model price are available for every year, eg in 2000 car model car H is not available, using pivot would result in NaN values, would you kindly advise, thankyou – epiphany Jan 02 '21 at 13:17
  • 1
    you just need to use fillna() and you should be fine with your example. out.pivot(index='Year', columns='Car Model', values='Car price').fillna('') – Danail Petrov Jan 02 '21 at 14:19

0 Answers0