如何获得每本书的平均价格

Question

如下图所示，它是一个包含图书数据的json，书店每年都会调整图书价格。目标是获得每本书的年平均书价，并将其显示在表格中

Json

[
 {
   "Year":"2009",
   "Book store":"ABC Store",
   "Book name":"Happy Prince"
   "Book price":"10"  
},
 {
   "Year":"2009",
   "Book store":"ABC Store",
   "Book name":"Happy Prince"
   "Book price":"14"
 
},
  "Year":"2009",
   "Book store":"ABC Store",
   "Book name":"Happy Prince"
   "Book price":"13"
}, 

"Year":"2009",
   "Book store":"ABC Store",
   "Book name":"Jane"
   "Book price":"10"  
}, 
"Year":"2009",
   "Book store":"ABC Store",
   "Book name":"Jane"
   "Book price":"12"  
},

"Year":"2010",
   "Book store":"ABC Store",
   "Book name":"Happy Prince"
   "Book price":"15"  
}, 

"Year":"2010",
   "Book store":"ABC Store",
   "Book name":"Happy Prince"
   "Book price":"10"  
},

"Year":"2010",
   "Book store":"ABC Store",
   "Book name":"Jane"
   "Book price":"15"
   
},

"Year":"2010",
   "Book store":"ABC Store",
   "Book name":"Jane"
   "Book price":"11"  
},
]

每本书的年平均价格表

Answer 1

传递给DataFrame构造函数，然后使用groupby，注意这里我们将数值列作为字符串类型，所以我们需要在使用mean函数之前将其转换回来

out = pd.DataFrame(j).groupby(['Year','Book name'],as_index=False)[['Book price']].agg(lambda x : x.astype(int).mean())
out
Out[348]: 
   Year     Book name  Book price
0  2009  Happy Prince   12.333333
1  2009          Jane   11.000000
2  2010  Happy Prince   12.500000
3  2010          Jane   13.000000

数据固定和输入

j = [
 {
   "Year":"2009",
   "Book store":"ABC Store",
   "Book name":"Happy Prince",
   "Book price":"10"  
},
 {
   "Year":"2009",
   "Book store":"ABC Store",
   "Book name":"Happy Prince",
   "Book price":"14"
 
},
    { "Year":"2009",
   "Book store":"ABC Store",
   "Book name":"Happy Prince",
   "Book price":"13"
}, 
    {"Year":"2009",
   "Book store":"ABC Store",
   "Book name":"Jane",
   "Book price":"10"  
}, 
    {"Year":"2009",
   "Book store":"ABC Store",
   "Book name":"Jane",
   "Book price":"12"  
},
    {"Year":"2010",
   "Book store":"ABC Store",
   "Book name":"Happy Prince",
   "Book price":"15"  
}, 
    {"Year":"2010",
   "Book store":"ABC Store",
   "Book name":"Happy Prince",
   "Book price":"10"  
},
    {"Year":"2010",
   "Book store":"ABC Store",
   "Book name":"Jane",
   "Book price":"15"
   
},
    {"Year":"2010",
   "Book store":"ABC Store",
   "Book name":"Jane",
   "Book price":"11"  
},
]

Answer 2

除了BENY的，您也可以使用pivot表（这是我给出的示例，在BENY回答之前，因为我正忙于修复json:-）

所以我觉得还是值得分享的

pd.pivot_table(df,index=['Year','Book name'], values='Book price', aggfunc=np.mean)

                   Book price
Year Book name
2009 Happy Prince   12.333333
     Jane           11.000000
2010 Happy Prince   12.500000
     Jane           13.000000