Cách hợp nhất hai tệp excel trong python pandas
|
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ệuNế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
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ệuBâ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<silver8786968Frami, Hills and Schmidtsilver9239344Stokes LLCgold10672390Kuhn-Gusikowskisilver11141962Herman LLCgold12424914White-Trantowsilver13527099Sanford and Sonsbronze14642753Pollich LLCbronze15257198Cronin, Oberbrunner and Spencergold Chúng tôi muốn hợp nhất dữ liệu này với bộ dữ liệu bán hàng được nối của chúng tôi. Sử dụng hàm !ls ../in/sales*.xlsx8 của gấu trúc và yêu cầu nó thực hiện phép nối trái, tương tự như hàm vlookup của Excel !ls ../in2 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 Điều này có vẻ khá tốt nhưng hãy xem xét một tài khoản cụ thể !ls ../in3 số tài khoảntênskusố lượngđơn giágiá ngoàingàytrạng thái9737550 Fritsch, Russell và AndersonS2-824231481. 921146. 882014-01-03 19. 07. 37NaN14737550Fritsch, Russel và AndersonB1-531022371. 561645. 882014-01-04 08. 57. 48NaN26737550Fritsch, Russel và AndersonB1-536364242. 061766. 522014-01-08 00. 02. 11 NaN32737550 Fritsch, Russell và Anderson S1-277222029. 54590. 802014-01-09 13. 20. 40NaN42737550Fritsch, Russel và AndersonS1-936832271. 681576. 962014-01-11 23. 47. 36NaN Số tài khoản này không có trong tệp trạng thái của chúng tôi, vì vậy chúng tôi có rất nhiều NaN. Chúng ta có thể quyết định cách chúng ta muốn xử lý tình huống này. Đối với trường hợp cụ thể này, hãy gắn nhãn tất cả các tài khoản bị thiếu là đồng. Sử dụng hàm !ls ../in/sales*.xlsx9 để dễ dàng thực hiện điều này trên cột trạng thái !ls ../in4 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 Kiểm tra dữ liệu chỉ để đảm bảo tất cả chúng ta đều ổn !ls ../in3 số tài khoảntênskusố lượngđơn giágiá ngoàingàytrạng thái9737550 Fritsch, Russell và AndersonS2-824231481. 921146. 882014-01-03 19. 07. 37 huy chương đồng14737550 Fritsch, Russell và Anderson B1-531022371. 561645. 882014-01-04 08. 57. 48 huy chương đồng 26737550 Fritsch, Russell và Anderson B1-536364242. 061766. 522014-01-08 00. 02. 11đồng32737550 Fritsch, Russell và Anderson S1-277222029. 54590. 802014-01-09 13. 20. 40 đồng42737550 Fritsch, Russell và Anderson S1-936832271. 681576. 962014-01-11 23. 47. 36 đồng Bây giờ chúng tôi có tất cả dữ liệu cùng với cột trạng thái được điền vào. Chúng tôi có thể thực hiện các thao tác dữ liệu bình thường của mình bằng cách sử dụng bộ khả năng pandas đầy đủ Sử dụng Danh mụcMột trong những chức năng tương đối mới trong gấu trúc là hỗ trợ dữ liệu phân loại. Từ gấu trúc, tài liệu Phân loại là một kiểu dữ liệu gấu trúc, tương ứng với các biến phân loại trong thống kê. một biến, chỉ có thể nhận một số lượng giá trị có thể có giới hạn và thường cố định (các danh mục; cấp độ trong R). Ví dụ như giới tính, tầng lớp xã hội, nhóm máu, quốc gia liên kết, thời gian quan sát hoặc xếp hạng qua thang đo Likert Đối với mục đích của chúng tôi, trường trạng thái là ứng cử viên sáng giá cho loại danh mục Cảnh báo phiên bản Bạn phải đảm bảo rằng bạn có phiên bản gấu trúc mới nhất ( > 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ệuBướ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ậnVí 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 |
