Hầu hết mọi người có thể có kinh nghiệm với các bảng tổng hợp trong Excel. Pandas cung cấp một chức năng tương tự được gọi là [đủ thích hợp]
df = pd.read_excel["../in/sales-funnel.xlsx"] df.head[]9. Mặc dù nó cực kỳ hữu ích, nhưng tôi thường thấy mình phải vật lộn để nhớ cách sử dụng cú pháp để định dạng đầu ra cho nhu cầu của mình. Bài viết này sẽ tập trung giải thích hàm pandas pivot_table và cách sử dụng hàm này để phân tích dữ liệu của bạn
Nếu bạn không quen với khái niệm này, wikipedia sẽ giải thích nó bằng thuật ngữ cấp cao. BTW , bạn có biết rằng Microsoft đã đăng ký nhãn hiệu PivotTable không? . Không cần phải nói, tôi sẽ nói về bảng tổng hợp chứ không phải PivotTable.
Như một phần thưởng bổ sung, tôi đã tạo một bảng gian lận đơn giản tóm tắt pivot_table. Bạn có thể tìm thấy nó ở cuối bài viết này và tôi hy vọng nó là một tài liệu tham khảo hữu ích. Hãy cho tôi biết nếu nó hữu ích
Dữ liệu
Một trong những thách thức khi sử dụng
df = pd.read_excel["../in/sales-funnel.xlsx"] df.head[]9 của gấu trúc là đảm bảo bạn hiểu dữ liệu của mình và bạn đang cố gắng trả lời những câu hỏi nào bằng bảng tổng hợp. Đây là một chức năng có vẻ đơn giản nhưng có thể tạo ra phân tích rất hiệu quả rất nhanh chóng
Trong trường hợp này, tôi sẽ theo dõi quy trình bán hàng [còn gọi là kênh]. Vấn đề cơ bản là một số chu kỳ bán hàng rất dài [nghĩ rằng “phần mềm doanh nghiệp”, thiết bị vốn, v.v. ] và ban quản lý muốn hiểu chi tiết hơn trong suốt năm
Các câu hỏi điển hình bao gồm
- Có bao nhiêu doanh thu trong đường ống?
- Những sản phẩm nào trong quy trình bán hàng?
- Ai có sản phẩm gì ở giai đoạn nào?
- Khả năng chúng ta chốt giao dịch vào cuối năm là bao nhiêu?
Nhiều công ty sẽ có công cụ CRM hoặc phần mềm khác mà bộ phận bán hàng sử dụng để theo dõi quy trình. Mặc dù họ có thể có các công cụ hữu ích để phân tích dữ liệu, nhưng chắc chắn sẽ có người xuất dữ liệu sang Excel và sử dụng PivotTable để tóm tắt dữ liệu.
Sử dụng bảng tổng hợp của gấu trúc có thể là một giải pháp thay thế tốt vì nó là
- Nhanh hơn [sau khi được thiết lập]
- Tự viết tài liệu [nhìn vào mã và bạn biết nó làm gì]
- Dễ sử dụng để tạo báo cáo hoặc email
- Linh hoạt hơn vì bạn có thể xác định các hàm tổng hợp tùy chỉnh
Đọc trong dữ liệu
Trước tiên, hãy thiết lập môi trường của chúng ta
Các bạn muốn làm theo có thể tải file excel
import pandas as pd import numpy as np
Cảnh báo phiên bản
API pivot_table API đã thay đổi theo thời gian, vì vậy hãy đảm bảo rằng bạn có phiên bản pandas gần đây [ > 0. 15] được cài đặt để ví dụ này hoạt động. Ví dụ này cũng sử dụng loại dữ liệu danh mục cũng yêu cầu phiên bản gần đây.
Đọc dữ liệu kênh bán hàng trong DataFrame của chúng tôi
df = pd.read_excel["../in/sales-funnel.xlsx"] df.head[]
AccountNameRepManagerProductQuantityPriceStatus0714466Trantow-BarrowsCraig BookerDebra Henley CPU 130000presented1714466Trantow-BarrowsCraig BookerDebra HenleySoftware110000presented2714466Trantow-BarrowsCraig BookerDebra HenleyMaintenance25000pending3737550Fritsch, Russel and AndersonCraig BookerDebra Henley CPU135000declined4146832Kiehn-SpinkaDaniel HiltonDebra HenleyCPU265000won
Để thuận tiện, hãy xác định cột trạng thái là một
df["Status"] = df["Status"].astype["category"] df["Status"].cat.set_categories[["won","pending","presented","declined"],inplace=True]1 và đặt thứ tự chúng tôi muốn xem
Điều này không bắt buộc nhưng giúp chúng tôi giữ thứ tự mong muốn khi chúng tôi phân tích dữ liệu
df["Status"] = df["Status"].astype["category"] df["Status"].cat.set_categories[["won","pending","presented","declined"],inplace=True]
Xoay vòng dữ liệu
Khi chúng tôi xây dựng bảng tổng hợp, tôi nghĩ cách dễ nhất là thực hiện từng bước một. Thêm các mục và kiểm tra từng bước để xác minh rằng bạn đang nhận được kết quả như mong đợi. Đừng ngại thử nghiệm với thứ tự và các biến số để xem cách trình bày nào phù hợp nhất với nhu cầu của bạn
Bảng tổng hợp đơn giản nhất phải có một khung dữ liệu và một
df["Status"] = df["Status"].astype["category"] df["Status"].cat.set_categories[["won","pending","presented","declined"],inplace=True]2. Trong trường hợp này, hãy sử dụng Tên làm chỉ mục của chúng tôi
pd.pivot_table[df,index=["Name"]]
AccountPriceQuantityNameBarton LLC 740150350001. 000000 Fritsch, Russell và Anderson 737550350001. 000000Herman LLC 141962650002. 000000Jerde-Hilpert41229050002. 000000Kassulke, Ondricka và Metz30759970003. 000000Keeling LLC 6889811000005. 000000Kiehn-Sinka146832650002. 000000Koepp Ltd729833350002. 000000Kulas Inc218895250001. 500000Purdy-Kunde163416300001. 000000Stokes LLC 23934475001. 000000Trantow-Barrows714466150001. 333333
Bạn cũng có thể có nhiều chỉ mục. Trên thực tế, hầu hết các đối số
df = pd.read_excel["../in/sales-funnel.xlsx"] df.head[]9 có thể nhận nhiều giá trị thông qua danh sách
pd.pivot_table[df,index=["Name","Rep","Manager"]]
AccountPriceQuantityNameRepManagerBarton LLC John SmithDebra Henley740150350001. 000000 Fritsch, Russell và Anderson Craig Booker Debra Henley 737550350001. 000000Herman LLC Cedric MossFred Anderson141962650002. 000000Jerde-HilpertJohn SmithDebra Henley41229050002. 000000Kassulke, Ondricka và MetzWendy YuleFred Anderson30759970003. 000000Keeling LLC Wendy YuleFred Anderson6889811000005. 000000Kiehn-SpinkaDaniel HiltonDebra Henley146832650002. 000000Koepp LtdWendy YuleFred Anderson729833350002. 000000Kulas IncDaniel HiltonDebra Henley218895250001. 500000Purdy-KundeCedric MossFred Anderson163416300001. 000000Stokes LLC Cedric MossFred Anderson23934475001. 000000Trantow-BarrowsCraig BookerDebra Henley714466150001. 333333
Điều này thú vị nhưng không đặc biệt hữu ích. Điều chúng tôi có thể muốn làm là xem xét điều này bởi Người quản lý và Người đại diện. Nó đủ dễ thực hiện bằng cách thay đổi
df["Status"] = df["Status"].astype["category"] df["Status"].cat.set_categories[["won","pending","presented","declined"],inplace=True]2
pd.pivot_table[df,index=["Manager","Rep"]]
AccountPriceQuantityManagerRepDebra HenleyCraig Booker720237. 020000. 0000001. 250000Daniel Hilton194874. 038333. 3333331. 666667John Smith576220. 020000. 0000001. 500000Fred AndersonCedric Moss196016. 527500. 0000001. 250000Wendy Yule614061. 544250. 0000003. 000000
Bạn có thể thấy rằng bảng tổng hợp đủ thông minh để bắt đầu tổng hợp dữ liệu và tóm tắt dữ liệu đó bằng cách nhóm các đại diện với người quản lý của họ. Bây giờ, chúng ta bắt đầu tìm hiểu sơ qua về những gì bảng tổng hợp có thể làm cho chúng ta
Với mục đích này, các cột Tài khoản và Số lượng không thực sự hữu ích. Hãy loại bỏ nó bằng cách xác định rõ ràng các cột mà chúng ta quan tâm bằng cách sử dụng trường
df["Status"] = df["Status"].astype["category"] df["Status"].cat.set_categories[["won","pending","presented","declined"],inplace=True]5
pd.pivot_table[df,index=["Manager","Rep"],values=["Price"]]
Giám đốc giáRepDebra HenleyCraig Booker20000Daniel Hilton38333John Smith20000Fred AndersonCedric Moss27500Wendy Yule44250
Cột giá tự động lấy dữ liệu trung bình nhưng chúng ta có thể thực hiện đếm hoặc tính tổng. Việc thêm chúng rất đơn giản bằng cách sử dụng
df["Status"] = df["Status"].astype["category"] df["Status"].cat.set_categories[["won","pending","presented","declined"],inplace=True]6 và
df["Status"] = df["Status"].astype["category"] df["Status"].cat.set_categories[["won","pending","presented","declined"],inplace=True]7
pd.pivot_table[df,index=["Manager","Rep"],values=["Price"],aggfunc=np.sum]
Giám đốc giáRepDebra HenleyCraig Booker80000Daniel Hilton115000John Smith40000Fred AndersonCedric Moss110000Wendy Yule177000
df["Status"] = df["Status"].astype["category"] df["Status"].cat.set_categories[["won","pending","presented","declined"],inplace=True]6 có thể lấy một danh sách các chức năng. Hãy thử tính trung bình bằng cách sử dụng hàm numpy
df["Status"] = df["Status"].astype["category"] df["Status"].cat.set_categories[["won","pending","presented","declined"],inplace=True]9 và
pd.pivot_table[df,index=["Name"]]0 để lấy số lượng
pd.pivot_table[df,index=["Manager","Rep"],values=["Price"],aggfunc=[np.mean,len]]
nghĩa là lenGiáGiá cảNgười quản lýDebra HenleyCraig Booker200004Daniel Hilton383333John Smith200002Fred AndersonCedric Moss275004Wendy Yule442504
Nếu chúng ta muốn xem doanh số bán hàng được chia nhỏ theo sản phẩm, biến
pd.pivot_table[df,index=["Name"]]1 cho phép chúng ta xác định một hoặc nhiều cột
Cột so với. giá trị
Tôi nghĩ một trong những điểm khó hiểu với
df = pd.read_excel["../in/sales-funnel.xlsx"] df.head[]9 là việc sử dụng
pd.pivot_table[df,index=["Name"]]1 và
df["Status"] = df["Status"].astype["category"] df["Status"].cat.set_categories[["won","pending","presented","declined"],inplace=True]5. Hãy nhớ rằng,
pd.pivot_table[df,index=["Name"]]1 là tùy chọn - chúng cung cấp một cách bổ sung để phân đoạn các giá trị thực tế mà bạn quan tâm. Các chức năng tổng hợp được áp dụng cho danh sách của bạn
df["Status"] = df["Status"].astype["category"] df["Status"].cat.set_categories[["won","pending","presented","declined"],inplace=True]5
pd.pivot_table[df,index=["Manager","Rep"],values=["Price"], columns=["Product"],aggfunc=[np.sum]]
sumPriceSản phẩm CPU MaintenanceMonitorSoftwareManagerRepDebra HenleyCraig Booker650005000NaN10000Daniel Hilton105000NaNNaN10000John Smith350005000NaNNaNFred AndersonCedric Moss950005000NaN1000050a00000Wendy Yule10500706
NaN hơi mất tập trung. Nếu muốn xóa chúng, chúng tôi có thể sử dụng
pd.pivot_table[df,index=["Name"]]7 để đặt chúng thành 0
df = pd.read_excel["../in/sales-funnel.xlsx"] df.head[]0
sumPriceSản phẩm CPU MaintenanceMonitorSoftwareManagerRepDebra HenleyCraig Booker650005000010000Daniel Hilton1050000010000John Smith35000500000Fred AndersonCedric Moss950005000010000Wendy Yule1650500000
Tôi nghĩ rằng nó cũng sẽ hữu ích để thêm số lượng. Thêm Số lượng vào danh sách
df["Status"] = df["Status"].astype["category"] df["Status"].cat.set_categories[["won","pending","presented","declined"],inplace=True]5
df = pd.read_excel["../in/sales-funnel.xlsx"] df.head[]1
sumPriceQuantityProduct CPU MaintenanceMonitorSoftware CPU MaintenanceMonitorSoftwareManagerRepDebra HenleyCraig Booker6500050000100002201Daniel Hilton10500000100004001John Smith350005000F0
Điều thú vị là bạn có thể di chuyển các mục vào chỉ mục để có được một hình ảnh đại diện khác. Xóa Sản phẩm khỏi
pd.pivot_table[df,index=["Name"]]1 và thêm vào
df["Status"] = df["Status"].astype["category"] df["Status"].cat.set_categories[["won","pending","presented","declined"],inplace=True]2
df = pd.read_excel["../in/sales-funnel.xlsx"] df.head[]2
sumPriceQuantityManagerRepProductDebra HenleyCraig Booker CPU 650002Maintenance50002Software100001Daniel Hilton CPU 1050004Software100001John Smith CPU350001Maintenance50002Fred AndersonCedric MossCPU950003Maintenance50001Software100001Wendy YuleCPU1650007Maintenance70003Monitor50002
Đối với tập dữ liệu này, biểu diễn này có ý nghĩa hơn. Bây giờ, nếu tôi muốn xem một số tổng thì sao?
df = pd.read_excel["../in/sales-funnel.xlsx"] df.head[]3
summeanPriceQuantityPriceQuantityManagerRepProductDebra HenleyCraig Booker CPU 65000232500. 0000001. 000000Bảo trì500025000. 0000002. 000000Phần mềm10000110000. 0000001. 000000Daniel Hilton CPU 105000452500. 0000002. 000000Phần mềm10000110000. 0000001. 000000John Smith CPU 35000135000. 0000001. 000000Bảo trì500025000. 0000002. 000000Fred AndersonCedric Moss CPU 95000347500. 0000001. 500000Bảo trì500015000. 0000001. 000000Phần mềm10000110000. 0000001. 000000Wendy Yule CPU 165000782500. 0000003. 500000Bảo trì700037000. 0000003. 000000Màn hình500025000. 0000002. 000000Tất cả5220003030705. 8823531. 764706
Hãy chuyển phân tích lên một cấp độ và xem xét quy trình của chúng tôi ở cấp độ người quản lý. Lưu ý cách trạng thái được sắp xếp dựa trên định nghĩa danh mục trước đây của chúng tôi
df = pd.read_excel["../in/sales-funnel.xlsx"] df.head[]4
sumPriceManagerStatusDebra Henleydeclined70000pending50000presented50000won65000Fred Andersondeclined65000pending5000presented45000won172000All522000
Một tính năng thực sự tiện dụng là khả năng chuyển từ điển tới
df["Status"] = df["Status"].astype["category"] df["Status"].cat.set_categories[["won","pending","presented","declined"],inplace=True]6 để bạn có thể thực hiện các chức năng khác nhau trên từng giá trị bạn chọn. Điều này có tác dụng phụ là làm cho nhãn sạch hơn một chút
df = pd.read_excel["../in/sales-funnel.xlsx"] df.head[]5
PriceQuantityProduct CPU MaintenanceMonitorSoftware CPU MaintenanceMonitorSoftwareManagerStatusDebra Henleydeclined700000002000pending4000010000001200presented3000000200001002won650000001000Fred Andersondeclined650000001000pending05000000100presented3000005000100001011won1650007000002100