对比Excel,学习pandas数据透视表
大数据DT
共 3610字,需浏览 8分钟
· 2021-06-08
![](https://filescdn.proginn.com/6128ace7e13fb0a8ca7f5759191d298e/c28e13c555e21411a0715fc58a5589fe.webp)
导读:更简单了呢~
01 Excel中做数据透视表
![](https://filescdn.proginn.com/75038525a7c433318b7f488640d4ca1b/3df12d3581e0b870535bf968d8dfcfc4.webp)
![](https://filescdn.proginn.com/6bf7dcc386dd6f427d683008a9c0d559/0c92a3a04fc834f0e2a25dd698ca1e98.webp)
![](https://filescdn.proginn.com/c2cf3bd50295a5a321cb711d09ab902d/8d2aa7e5208821241013daa83b11d46f.webp)
![](https://filescdn.proginn.com/444cf9d5ddc8f7b1cd56adefdec021ef/1354041bf3ec7e4df7e4b33c1b826d39.webp)
![](https://filescdn.proginn.com/af8485c28fa4a030b78693cae0df95bc/fdbc1a9dcdd0a11f0f2066fc3e66d1c2.webp)
02 pandas用pivot_table()做数据透视表
pd.pivot_table(data,index=None,columns=None,
values=None,aggfunc='mean',
margins=False,margins_name='All',
dropna=True,fill_value=None)
![](https://filescdn.proginn.com/d0f7230479053befba4dbfa6d4862e85/7b79319ba3965b0b5477b6e6353554c7.webp)
data 相当于Excel中的"选中数据源"; index 相当于上述"数据透视表字段"中的行; columns 相当于上述"数据透视表字段"中的列; values 相当于上述"数据透视表字段"中的值; aggfunc 相当于上述"结果"中的计算类型; margins 相当于上述"结果"中的总计; margins_name 相当于修改"总计"名,为其它名称。
dropna 表示是否删除缺失值,如果为True时,则把一整行全作为缺失值删除; fill_value 表示将缺失值,用某个指定值填充。
03 案例说明
![](https://filescdn.proginn.com/02fb8660ec0c4ddcee11579e6a9c0f22/5901b068b02a234501390d4e1428c443.webp)
df = pd.read_excel(r"C:\Users\黄伟\Desktop\pivot_table.xlsx")
display(df.sample(5))
df.insert(1,"月份",df["销售日期"].apply(lambda x:x.month))
display(df.sample(5))
df1 = pd.pivot_table(df,index="品牌",columns="月份",
values="销售数量",aggfunc=np.sum)
display(df1)
![](https://filescdn.proginn.com/8925a2a2b0e1c9c446abc065db17465f/62eff5fa6323f682218c3f025e2cb0f7.webp)
![](https://filescdn.proginn.com/8478d58938ecc9127ff6f24a3dc39d86/f46b3b5bbfcd34e7c00395faab6a88e8.webp)
df = pd.read_excel(r"C:\Users\黄伟\Desktop\pivot_table.xlsx")
display(df.sample(5))
df.insert(1,"月份",df["销售日期"].apply(lambda x:x.month))
display(df.sample(5))
df1 = pd.pivot_table(df,index="品牌",columns=["销售区域","月份"],
values="销售数量",aggfunc=np.sum)
display(df1)
![](https://filescdn.proginn.com/b9fa00f1ae2e0883e56c30a80c68546d/f3711fdef4a7593fbcff5fb27d6c79e2.webp)
![](https://filescdn.proginn.com/03ba043c6e3be2acae62102be8e7d28d/5397c6935baae495760aff72ea21a970.webp)
df = pd.read_excel(r"C:\Users\黄伟\Desktop\pivot_table.xlsx")
display(df.sample(5))
df.insert(1,"月份",df["销售日期"].apply(lambda x:x.month))
display(df.sample(5))
df1 = pd.pivot_table(df,index=["品牌","销售区域"],columns="月份",
values="销售数量",aggfunc=np.sum)
display(df1)
![](https://filescdn.proginn.com/ca85afe30db85a00bcef926fd7d8c3c4/674d15a58434203993667ca3f259c4a3.webp)
![](https://filescdn.proginn.com/b36e10bdb13981c209db9135a1d781d5/5f33ddffffeed29eba38c421192df29a.webp)
df = pd.read_excel(r"C:\Users\黄伟\Desktop\pivot_table.xlsx")
display(df.sample(5))
df.insert(1,"月份",df["销售日期"].apply(lambda x:x.month))
display(df.sample(5))
df1 = pd.pivot_table(df,index="品牌",columns="月份",
values=["销售数量","货号"],
aggfunc={"销售数量":"sum","货号":"count"},
margins=True,margins_name="总计")
display(df1)
![](https://filescdn.proginn.com/862f4fe0d7853c3eb7e279c8f5d800b7/fce69238815a58e06fd04dab0a100a2f.webp)
![音符](https://filescdn.proginn.com/ea9daec556e1de67571db3c2e05b40f4/f8567143f87d3120586cdde5fdfa5dd3.webp)
![](https://filescdn.proginn.com/3d42873fd1626cd993f07138906b72bf/f5c189e4e2264af3ebb0eba87aece61f.webp)
评论