Nhiệm vụ chung của python và pandas là tự động hóa quy trình tổng hợp dữ liệu từ nhiều tệp và bảng tính
Bài viết này sẽ hướng dẫn quy trình cơ bản cần thiết để phân tích cú pháp nhiều tệp Excel, kết hợp dữ liệu, làm sạch và phân tích dữ liệu. Sự kết hợp giữa python + pandas có thể cực kỳ hiệu quả đối với các hoạt động này và có thể là một giải pháp thay thế rất hữu ích cho các quy trình thủ công hoặc các tập lệnh VBA đau đớn thường được sử dụng trong doanh nghiệp .
Vấn đề
Trước khi tôi đi vào các ví dụ, đây là một sơ đồ đơn giản thể hiện những thách thức với quy trình phổ biến được sử dụng trong các doanh nghiệp trên toàn thế giới để hợp nhất dữ liệu từ nhiều tệp Excel, làm sạch dữ liệu và thực hiện một số phân tích
Nếu bạn đang đọc bài viết này, tôi nghi ngờ bạn đã gặp phải một số vấn đề nêu trên. Việc cắt và dán dữ liệu hoặc viết mã VBA đau đớn sẽ nhanh chóng trở nên lỗi thời. Có phải là một cách tốt hơn.
Python + pandas có thể là một giải pháp thay thế tuyệt vời có khả năng mở rộng và mạnh mẽ hơn nhiều
Bằng cách sử dụng tập lệnh python, bạn có thể phát triển một giải pháp hợp lý hơn và có thể lặp lại cho nhu cầu xử lý dữ liệu của mình. Phần còn lại của bài viết này sẽ cho thấy một ví dụ đơn giản về cách thức hoạt động của quy trình này. Tôi hy vọng nó sẽ cung cấp cho bạn ý tưởng về cách áp dụng những công cụ này vào tình huống độc đáo của bạn
Thu thập Dữ liệu
Nếu bạn muốn theo dõi, đây là các tệp excel và liên kết đến sổ ghi chép
- Tháng 1 năm 2014
- Tháng 2 năm 2014
- Tháng 3 năm 2014
- tình trạng
- sổ tay
Bước đầu tiên trong quy trình là thu thập tất cả dữ liệu vào một nơi
Đầu tiên, nhập gấu trúc và numpy
import pandas as pd import numpy as np
Hãy cùng xem các tệp trong thư mục đầu vào của chúng ta, sử dụng các lệnh shell tiện lợi trong ipython
!ls ../in
address-state-example.xlsx report.xlsx sample-address-new.xlsx customer-status.xlsx sales-feb-2014.xlsx sample-address-old.xlsx excel-comp-data.xlsx sales-jan-2014.xlsx sample-diff-1.xlsx my-diff-1.xlsx sales-mar-2014.xlsx sample-diff-2.xlsx my-diff-2.xlsx sample-address-1.xlsx sample-salesv3.xlsx my-diff.xlsx sample-address-2.xlsx pricing.xlsx sample-address-3.xlsx
Có rất nhiều tệp, nhưng chúng tôi chỉ muốn xem doanh số bán hàng. tệp xlsx
!ls ../in/sales*.xlsx
../in/sales-feb-2014.xlsx ../in/sales-jan-2014.xlsx ../in/sales-mar-2014.xlsx
Sử dụng mô-đun
!ls ../in/sales*.xlsx1 của python để dễ dàng liệt kê các tệp chúng tôi cần
import glob glob.glob["../in/sales*.xlsx"]
['../in/sales-jan-2014.xlsx', '../in/sales-mar-2014.xlsx', '../in/sales-feb-2014.xlsx']
Điều này mang lại cho chúng tôi những gì chúng tôi cần. Hãy nhập từng tệp của chúng tôi và kết hợp chúng thành một tệp.
!ls ../in/sales*.xlsx2 và
!ls ../in/sales*.xlsx3 của Panda có thể làm điều này cho chúng tôi. Tôi sẽ sử dụng
!ls ../in/sales*.xlsx3 trong ví dụ này
Đoạn mã bên dưới sẽ khởi tạo một Khung dữ liệu trống, sau đó nối tất cả các tệp riêng lẻ vào Khung dữ liệu
!ls ../in/sales*.xlsx5
all_data = pd.DataFrame[] for f in glob.glob["../in/sales*.xlsx"]: df = pd.read_excel[f] all_data = all_data.append[df,ignore_index=True]
Bây giờ chúng tôi có tất cả dữ liệu trong Khung dữ liệu
!ls ../in/sales*.xlsx5 của chúng tôi. Bạn có thể sử dụng
!ls ../in/sales*.xlsx7 để xem xét và đảm bảo rằng dữ liệu của bạn có vẻ tốt
all_data.describe[]
số tài khoảnsố lượngđơn giágiá ngoàisố lượng1742. 0000001742. 0000001742. 0000001742. 000000mean485766. 48794524. 31917354. 9854541349. 229392std223750. 66079214. 50275926. 1084901094. 639319min141962. 000000-1. 00000010. 030000-97. 16000025%257198. 00000012. 00000032. 132500468. 59250050%527099. 00000025. 00000055. 4650001049. 70000075%714466. 00000037. 00000077. 6075002074. 972500max786968. 00000049. 00000099. 8500004824. 540000
Nhiều dữ liệu này có thể không có nhiều ý nghĩa đối với tập dữ liệu này nhưng tôi quan tâm nhất đến hàng đếm để đảm bảo số lượng phần tử dữ liệu có ý nghĩa. Trong trường hợp này, tôi thấy tất cả các hàng dữ liệu mà tôi mong đợi
all_data.head[]
số tài khoảnnameskuquantityđơn giáext pricedate0740150Barton LLC B1-200003986. 693380. 912014-01-01 07. 21. 511714466Trantow-BarrowsS2-77896-163. 16-63. 162014-01-01 10. 00. 472218895Kulas IncB1-699242390. 702086. 102014-01-01 13. 24. 583307599Kassulke, Ondricka và MetzS1-654814121. 05863. 052014-01-01 15. 05. 224412290Jerde-HilpertS2-34077683. 21499. 262014-01-01 23. 26. 55
Nó không quan trọng trong ví dụ này nhưng cách tốt nhất là chuyển đổi cột ngày thành đối tượng ngày giờ
!ls ../in0
Kết hợp Dữ liệu
Bây giờ chúng tôi có tất cả dữ liệu vào một DataFrame, chúng tôi có thể thực hiện bất kỳ thao tác nào mà DataFrame hỗ trợ. Trong trường hợp này, điều tiếp theo chúng tôi muốn làm là đọc trong một tệp khác chứa trạng thái khách hàng theo tài khoản. Bạn có thể coi đây là chiến lược phân khúc khách hàng của một công ty hoặc một số cơ chế khác để xác định khách hàng của họ
Đầu tiên, chúng tôi đọc trong dữ liệu
!ls ../in1
account numbernamestatus0740150Barton LLC gold1714466Trantow-Barrowssilver2218895Kulas Incbronze3307599Kassulke, Ondricka and Metzbronze4412290Jerde-Hilpertbronze5729833Koepp Ltdsilver6146832Kiehn-Spinkasilver7688981Keeling LLC 0. 15] được cài đặt để ví dụ này hoạt động
!ls ../in6
!ls ../in7
Đầu tiên, chúng tôi đánh máy cột đó thành một danh mục bằng cách sử dụng
../in/sales-feb-2014.xlsx ../in/sales-jan-2014.xlsx ../in/sales-mar-2014.xlsx0
!ls ../in8
Điều này dường như chưa thay đổi bất cứ điều gì ngay lập tức
!ls ../in9
số tài khoảnnameskuquantityđơn giáext pricedatestatus0740150Barton LLC B1-200003986. 693380. 912014-01-01 07. 21. 51gold1714466Trantow-BarrowsS2-77896-163. 16-63. 162014-01-01 10. 00. 47bạc2218895Kulas IncB1-699242390. 702086. 102014-01-01 13. 24. 58đồng3307599Kassulke, Ondricka và MetzS1-654814121. 05863. 052014-01-01 15. 05. 22đồng4412290Jerde-HilpertS2-34077683. 21499. 262014-01-01 23. 26. 55đồng
Mua bạn có thể thấy rằng đó là một loại dữ liệu mới
address-state-example.xlsx report.xlsx sample-address-new.xlsx customer-status.xlsx sales-feb-2014.xlsx sample-address-old.xlsx excel-comp-data.xlsx sales-jan-2014.xlsx sample-diff-1.xlsx my-diff-1.xlsx sales-mar-2014.xlsx sample-diff-2.xlsx my-diff-2.xlsx sample-address-1.xlsx sample-salesv3.xlsx my-diff.xlsx sample-address-2.xlsx pricing.xlsx sample-address-3.xlsx0
address-state-example.xlsx report.xlsx sample-address-new.xlsx customer-status.xlsx sales-feb-2014.xlsx sample-address-old.xlsx excel-comp-data.xlsx sales-jan-2014.xlsx sample-diff-1.xlsx my-diff-1.xlsx sales-mar-2014.xlsx sample-diff-2.xlsx my-diff-2.xlsx sample-address-1.xlsx sample-salesv3.xlsx my-diff.xlsx sample-address-2.xlsx pricing.xlsx sample-address-3.xlsx1
Các danh mục trở nên thú vị hơn khi bạn chỉ định thứ tự cho các danh mục. Ngay bây giờ, nếu chúng ta gọi
../in/sales-feb-2014.xlsx ../in/sales-jan-2014.xlsx ../in/sales-mar-2014.xlsx1 trên cột, nó sẽ sắp xếp theo bảng chữ cái
address-state-example.xlsx report.xlsx sample-address-new.xlsx customer-status.xlsx sales-feb-2014.xlsx sample-address-old.xlsx excel-comp-data.xlsx sales-jan-2014.xlsx sample-diff-1.xlsx my-diff-1.xlsx sales-mar-2014.xlsx sample-diff-2.xlsx my-diff-2.xlsx sample-address-1.xlsx sample-salesv3.xlsx my-diff.xlsx sample-address-2.xlsx pricing.xlsx sample-address-3.xlsx2
số tài khoảnnameskuquantityđơn giágiá ngoàingàytrạng thái1741642753Pollich LLC B1-04202895. 86766. 882014-02-28 23. 47. 32đồng1232218895Kulas IncS1-065322942. 751239. 752014-09-21 11. 27. 55đồng579527099Sanford and SonsS1-277224187. 863602. 262014-04-14 18. 36. 11bronze580383080Will LLC B1-200004051. 732069. 202014-04-14 22. 44. 58bronze581383080Will LLC S2-103421576. 751151. 252014-04-15 02. 57. 43đồng
Chúng tôi sử dụng
../in/sales-feb-2014.xlsx ../in/sales-jan-2014.xlsx ../in/sales-mar-2014.xlsx2 để cho biết thứ tự chúng tôi muốn sử dụng cho đối tượng danh mục này. Trong trường hợp này, chúng tôi sử dụng thứ tự huy chương Olympic
address-state-example.xlsx report.xlsx sample-address-new.xlsx customer-status.xlsx sales-feb-2014.xlsx sample-address-old.xlsx excel-comp-data.xlsx sales-jan-2014.xlsx sample-diff-1.xlsx my-diff-1.xlsx sales-mar-2014.xlsx sample-diff-2.xlsx my-diff-2.xlsx sample-address-1.xlsx sample-salesv3.xlsx my-diff.xlsx sample-address-2.xlsx pricing.xlsx sample-address-3.xlsx3
Bây giờ, chúng ta có thể sắp xếp nó để màu vàng hiển thị trên cùng
address-state-example.xlsx report.xlsx sample-address-new.xlsx customer-status.xlsx sales-feb-2014.xlsx sample-address-old.xlsx excel-comp-data.xlsx sales-jan-2014.xlsx sample-diff-1.xlsx my-diff-1.xlsx sales-mar-2014.xlsx sample-diff-2.xlsx my-diff-2.xlsx sample-address-1.xlsx sample-salesv3.xlsx my-diff.xlsx sample-address-2.xlsx pricing.xlsx sample-address-3.xlsx2
số tài khoảnnameskuquantityđơn giáext pricedatestatus0740150Barton LLC B1-200003986. 693380. 912014-01-01 07. 21. 51vàng1193257198Cronin, Oberbrunner và SpencerS2-824232352. 901216. 702014-09-09 03. 06. 30gold1194141962Herman LLC B1-864814552. 782375. 102014-09-09 11. 49. 45vàng1195257198Cronin, Oberbrunner và SpencerB1-508093051. 961558. 802014-09-09 21. 14. 31gold1197239344Stokes LLC B1-655514315. 24655. 322014-09-10 11. 10. 02vàng
Phân tích dữ liệu
Bước cuối cùng trong quy trình là phân tích dữ liệu. Bây giờ nó đã được hợp nhất và làm sạch, chúng tôi có thể xem liệu có bất kỳ thông tin chi tiết nào cần học hay không
address-state-example.xlsx report.xlsx sample-address-new.xlsx customer-status.xlsx sales-feb-2014.xlsx sample-address-old.xlsx excel-comp-data.xlsx sales-jan-2014.xlsx sample-diff-1.xlsx my-diff-1.xlsx sales-mar-2014.xlsx sample-diff-2.xlsx my-diff-2.xlsx sample-address-1.xlsx sample-salesv3.xlsx my-diff.xlsx sample-address-2.xlsx pricing.xlsx sample-address-3.xlsx5
address-state-example.xlsx report.xlsx sample-address-new.xlsx customer-status.xlsx sales-feb-2014.xlsx sample-address-old.xlsx excel-comp-data.xlsx sales-jan-2014.xlsx sample-diff-1.xlsx my-diff-1.xlsx sales-mar-2014.xlsx sample-diff-2.xlsx my-diff-2.xlsx sample-address-1.xlsx sample-salesv3.xlsx my-diff.xlsx sample-address-2.xlsx pricing.xlsx sample-address-3.xlsx6
Ví dụ: nếu bạn muốn xem nhanh hiệu quả hoạt động của các khách hàng cấp cao nhất so với cấp dưới cùng. Sử dụng
../in/sales-feb-2014.xlsx ../in/sales-jan-2014.xlsx ../in/sales-mar-2014.xlsx3 để lấy giá trị trung bình của các giá trị
address-state-example.xlsx report.xlsx sample-address-new.xlsx customer-status.xlsx sales-feb-2014.xlsx sample-address-old.xlsx excel-comp-data.xlsx sales-jan-2014.xlsx sample-diff-1.xlsx my-diff-1.xlsx sales-mar-2014.xlsx sample-diff-2.xlsx my-diff-2.xlsx sample-address-1.xlsx sample-salesv3.xlsx my-diff.xlsx sample-address-2.xlsx pricing.xlsx sample-address-3.xlsx7
số lượngđơn giágiá ngoàitình trạnggold24. 68072352. 4312051325. 566867bạc23. 81424155. 7242411339. 477539đồng24. 58900555. 4707331367. 757736
Tất nhiên, bạn có thể chạy nhiều chức năng tổng hợp trên dữ liệu để có được thông tin thực sự hữu ích
address-state-example.xlsx report.xlsx sample-address-new.xlsx customer-status.xlsx sales-feb-2014.xlsx sample-address-old.xlsx excel-comp-data.xlsx sales-jan-2014.xlsx sample-diff-1.xlsx my-diff-1.xlsx sales-mar-2014.xlsx sample-diff-2.xlsx my-diff-2.xlsx sample-address-1.xlsx sample-salesv3.xlsx my-diff.xlsx sample-address-2.xlsx pricing.xlsx sample-address-3.xlsx8
quantityunit priceext pricesummeanstdsummeanstdsummeanstdstatusgold819424. 68072314. 47867017407. 1652. 43120526. 244516440088. 201325. 5668671074. 564373bạc1538423. 81424114. 51904435997. 8655. 72424126. 053569865302. 491339. 4775391094. 908529đồng1878624. 58900514. 50651542379. 6455. 47073326. 0621491044966. 911367. 7577361104. 129089
Vì vậy, điều này nói với bạn những gì? . Ngay cả khi bạn nhìn vào tổng giá trị đô la liên quan đến đồng so với. vàng, có vẻ lạ là chúng tôi bán cho khách hàng bằng đồng nhiều hơn vàng
Có lẽ chúng ta nên xem chúng ta có bao nhiêu khách hàng bằng đồng và xem chuyện gì đang xảy ra?
Điều tôi dự định làm là lọc ra các tài khoản duy nhất và xem có bao nhiêu khách hàng vàng, bạc và đồng
Tôi đang cố tình xâu chuỗi nhiều lệnh lại với nhau, điều này không nhất thiết phải là cách thực hành tốt nhất nhưng nó cho thấy gấu trúc có thể mạnh như thế nào. Vui lòng xem lại bài viết trước của tôi tại đây và tại đây để hiểu rõ hơn. Tự chơi với lệnh này để hiểu cách các lệnh tương tác
address-state-example.xlsx report.xlsx sample-address-new.xlsx customer-status.xlsx sales-feb-2014.xlsx sample-address-old.xlsx excel-comp-data.xlsx sales-jan-2014.xlsx sample-diff-1.xlsx my-diff-1.xlsx sales-mar-2014.xlsx sample-diff-2.xlsx my-diff-2.xlsx sample-address-1.xlsx sample-salesv3.xlsx my-diff.xlsx sample-address-2.xlsx pricing.xlsx sample-address-3.xlsx9
!ls ../in/sales*.xlsx0
Vâng. Điều này có ý nghĩa hơn một chút. Chúng tôi thấy rằng chúng tôi có 9 khách hàng bằng đồng và chỉ có 4 khách hàng. Đó có lẽ là lý do tại sao khối lượng lại nghiêng về phía khách hàng bằng đồng của chúng tôi. Kết quả này hợp lý vì chúng tôi đã mặc định là đồng cho nhiều khách hàng của mình. Có lẽ chúng ta nên phân loại lại một số trong số họ?
Phần kết luận
Ví dụ này chỉ đề cập đến việc tổng hợp 4 tệp Excel đơn giản chứa dữ liệu ngẫu nhiên. Tuy nhiên, các nguyên tắc có thể được áp dụng cho các tập dữ liệu lớn hơn nhiều nhưng bạn vẫn có thể giữ cho cơ sở mã rất dễ quản lý. Ngoài ra, bạn có toàn bộ sức mạnh của python trong tầm tay nên bạn có thể làm được nhiều việc hơn là chỉ thao tác trên dữ liệu
Tôi khuyến khích bạn thử áp dụng một số khái niệm này trong các tình huống của mình và xem liệu bạn có thể tìm ra cách tự động hóa tác vụ Excel đau đầu luôn ám ảnh bạn hàng ngày, hàng tuần hoặc hàng tháng hay không