{"id":416,"date":"2022-08-06T13:56:05","date_gmt":"2022-08-06T05:56:05","guid":{"rendered":"https:\/\/kuo.us.to\/wordpress\/?p=416"},"modified":"2023-01-17T16:48:00","modified_gmt":"2023-01-17T08:48:00","slug":"python%e5%ad%b8%e7%bf%92%e7%ad%86%e8%a8%98-%e6%89%b9%e6%ac%a1%e8%ae%80%e5%8f%96excel%e6%aa%94%e6%a1%88%ef%bc%8c%e5%bd%99%e6%95%b4%e6%88%90%e4%b8%80%e5%80%8bdataframe","status":"publish","type":"post","link":"https:\/\/kuo.us.to\/wordpress\/python%e5%ad%b8%e7%bf%92%e7%ad%86%e8%a8%98\/416\/","title":{"rendered":"Python\u5b78\u7fd2\u7b46\u8a18: \u6279\u6b21\u8b80\u53d6excel\u6a94\u6848\uff08\u540c\u4e00\u76ee\u9304\u4e0b\uff09\uff0c\u5f59\u6574\u6210\u4e00\u500bdataframe"},"content":{"rendered":"

\u6279\u6b21\u8b80\u53d6excel\u6a94\u6848\uff08\u9644\u6a94\u540d\u70baxls\u6216xlsx\uff09<\/h2>\n

\u5be6\u505a\u7684\u8cc7\u6599\u593e\u76ee\u9304\u7d50\u69cb\u5982\u4e0b\uff1a<\/p>\n

\u5217\u51fa\u76ee\u9304\u4e0b\u7684\u6240\u6709\u5b50\u76ee\u9304\u53ca\u6a94\u6848\uff0c\u6307\u4ee4\u70ba\uff1atree\/f<\/p>\n

D:\\Python Data>tree\/f 20220805_108-110\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868<\/p>\n

D:\\PYTHON DATA\\20220805_108-110\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868
\u2502 108\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868.xls
\u2502 109\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868.xlsx
\u2502 110\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868.xlsx
\u2502 Untitled.ipynb
\u2502
\u2514\u2500.ipynb_checkpoints
Untitled-checkpoint.ipynb<\/p>\n

\u7b2c\u4e00\u7a2e\u65b9\u6cd5\uff1a<\/h2>\n\n\n
import pandas as pd\nimport numpy as np\nimport os\n\npath = os.getcwd()\n\nprint(f'\u76ee\u524d\u5de5\u4f5c\u76ee\u9304\u70ba\uff1a{path}')\n# List files:\nfiles = os.listdir(path)\nprint(files)<\/code><\/pre>\n\n\n
\u76ee\u524d\u5de5\u4f5c\u76ee\u9304\u70ba\uff1aD:\\Python Data\\20220805_108-110\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868\n['.ipynb_checkpoints', '108\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868.xls', '109\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868.xlsx', '110\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868.xlsx', 'Untitled.ipynb']<\/pre>\n\n\n
files_xlsx = [f for f in files if f[-4:] == \"xlsx\"]\nfiles_xls = [f for f in files if f[-3:] == \"xls\"]\nfiles_xls.extend(files_xlsx)\n\nprint(f'\u76ee\u524d\u7684\u76ee\u9304\u4e0b\u5171\u6709\uff1a{len(files_xls)}\u500bexcel\u6a94\u6848')\n\nfor i, file_name in enumerate(files_xls):\n    print(f'\u7b2c{i+1}\u500b\u6a94\u6848\u70ba\uff1a{file_name}')<\/code><\/pre>\n\n\n
\u76ee\u524d\u7684\u76ee\u9304\u4e0b\u5171\u6709\uff1a3\u500bexcel\u6a94\u6848\n\u7b2c1\u500b\u6a94\u6848\u70ba\uff1a108\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868.xls\n\u7b2c2\u500b\u6a94\u6848\u70ba\uff1a109\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868.xlsx\n\u7b2c3\u500b\u6a94\u6848\u70ba\uff1a110\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868.xlsx<\/pre>\n\n\n
df = pd.DataFrame() # \u5efa\u7acb\u4e00\u500b\u7a7a\u767d\u7684dataframe\u4f86\u4e32\u63a5\u6240\u6709\u7684\u6a94\u6848\nfor file_name in files_xls:\n    data = pd.read_excel(file_name)\n    print(f'\u6a94\u540d\uff1a{file_name}\u8cc7\u6599\u7b46\u6578\u70ba{len(data)}\u7b46')\n    df = pd.concat([df, data], ignore_index=True)\n\nprint(f'\u5408\u4f75\u5f8c\u7684dataframe\u8cc7\u6599\u7b46\u6578\u5171\u70ba{len(df)}\u7b46')\ndf.head()<\/code><\/pre>\n\n\n
\n
\n
\u6a94\u540d\uff1a108\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868.xls\u8cc7\u6599\u7b46\u6578\u70ba24\u7b46\n\u6a94\u540d\uff1a109\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868.xlsx\u8cc7\u6599\u7b46\u6578\u70ba24\u7b46\n\u6a94\u540d\uff1a110\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868.xlsx\u8cc7\u6599\u7b46\u6578\u70ba24\u7b46\n\u5408\u4f75\u5f8c\u7684dataframe\u8cc7\u6599\u7b46\u6578\u5171\u70ba72\u7b46\n<\/pre>\n<\/div>\n<\/div>\n
\n
\n
\n\n\n\n\n\n\n\n\n
\u6c7d\u7f38\u7e3d\u6392\u6c23\u91cf<\/td>\n\u514d\u7a05\u8f1b\u6578\u7e3d\u8a08<\/td>\n\u61c9\u7a05\u8f1b\u6578\u7e3d\u8a08<\/td>\n\u7a05\u984d\u7e3d\u8a08<\/td>\n\u81ea\u7528\u5c0f\u5ba2\u8eca\u514d\u7a05\u8f1b\u6578<\/td>\n\u81ea\u7528\u5c0f\u5ba2\u8eca\u61c9\u7a05\u8f1b\u6578<\/td>\n\u81ea\u7528\u5c0f\u5ba2\u8eca\u7a05\u984d<\/td>\n…<\/td>\n\u6a5f\u8eca\u61c9\u7a05\u8f1b\u6578<\/td>\n\u6a5f\u8eca\u7a05\u984d<\/td>\n<\/tr>\n
0<\/td>\n\u7e3d\u8a08<\/td>\n34184<\/td>\n249856<\/td>\n######<\/td>\n32213<\/td>\n184478<\/td>\n1E+06<\/td>\n41<\/td>\n8295<\/td>\n<\/tr>\n
1<\/td>\n\u71c3\u6cb9\u8eca\u5408\u8a08<\/td>\n34138<\/td>\n249856<\/td>\n######<\/td>\n32167<\/td>\n184478<\/td>\n1E+06<\/td>\n41<\/td>\n8295<\/td>\n<\/tr>\n
2<\/td>\n150(\u542b\u4ee5\u4e0b)\u7acb\u65b9\u516c\u5206<\/td>\n0<\/td>\n0<\/td>\n######<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
3<\/td>\n151-250\u7acb\u65b9\u516c\u5206<\/td>\n27<\/td>\n5312<\/td>\n######<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n27<\/td>\n5312<\/td>\n<\/tr>\n
4<\/td>\n251-500\u7acb\u65b9\u516c\u5206<\/td>\n1<\/td>\n1279<\/td>\n######<\/td>\n0<\/td>\n1<\/td>\n0<\/td>\n1<\/td>\n1278<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n
 <\/h2>\n
\u7b2c\u4e8c\u7a2e\u65b9\u6cd5\uff1a<\/h2>\n\n\n
import glob\nimport pandas as pd\nimport numpy as np\nimport os\npath = os.getcwd()\nprint(path)\nfiles = glob.glob(\"*.xls\") + glob.glob(\"*.xlsx\")\nfor file in files:\n    print(file)<\/code><\/pre>\n\n\n
D:\\Python Data\\20220805_108-110\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868\n108\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868.xls\n109\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868.xlsx\n110\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868.xlsx<\/pre>\n\n\n
df = pd.DataFrame() # \u5efa\u7acb\u4e00\u500b\u7a7a\u767d\u7684dataframe\u4f86\u4e32\u63a5\u6240\u6709\u7684\u6a94\u6848\nfor file_name in files:\n    data = pd.read_excel(file_name)\n    print(f'\u6a94\u540d\uff1a{file_name}\u8cc7\u6599\u7b46\u6578\u70ba{len(data)}\u7b46')\n    df = pd.concat([df, data], ignore_index=True)\n\nprint(f'\u5408\u4f75\u5f8c\u7684dataframe\u8cc7\u6599\u7b46\u6578\u5171\u70ba{len(df)}\u7b46')\ndf.head()<\/code><\/pre>\n\n\n
\n
\n
\u6a94\u540d\uff1a108\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868.xls\u8cc7\u6599\u7b46\u6578\u70ba24\u7b46\n\u6a94\u540d\uff1a109\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868.xlsx\u8cc7\u6599\u7b46\u6578\u70ba24\u7b46\n\u6a94\u540d\uff1a110\u5e74\u96f2\u6797\u7e23\u4f7f\u7528\u724c\u7167\u7a05\u7a05\u6e90\u7d71\u8a08\u8868.xlsx\u8cc7\u6599\u7b46\u6578\u70ba24\u7b46\n\u5408\u4f75\u5f8c\u7684dataframe\u8cc7\u6599\u7b46\u6578\u5171\u70ba72\u7b46\n<\/pre>\n<\/div>\n<\/div>\n
\n
\n
\n\n\n\n\n\n\n\n\n
\u6c7d\u7f38\u7e3d\u6392\u6c23\u91cf<\/td>\n\u514d\u7a05\u8f1b\u6578\u7e3d\u8a08<\/td>\n\u61c9\u7a05\u8f1b\u6578\u7e3d\u8a08<\/td>\n\u7a05\u984d\u7e3d\u8a08<\/td>\n\u81ea\u7528\u5c0f\u5ba2\u8eca\u514d\u7a05\u8f1b\u6578<\/td>\n\u81ea\u7528\u5c0f\u5ba2\u8eca\u61c9\u7a05\u8f1b\u6578<\/td>\n\u81ea\u7528\u5c0f\u5ba2\u8eca\u7a05\u984d<\/td>\n…<\/td>\n\u6a5f\u8eca\u61c9\u7a05\u8f1b\u6578<\/td>\n\u6a5f\u8eca\u7a05\u984d<\/td>\n<\/tr>\n
0<\/td>\n\u7e3d\u8a08<\/td>\n34184<\/td>\n249856<\/td>\n######<\/td>\n32213<\/td>\n184478<\/td>\n1E+06<\/td>\n41<\/td>\n8295<\/td>\n<\/tr>\n
1<\/td>\n\u71c3\u6cb9\u8eca\u5408\u8a08<\/td>\n34138<\/td>\n249856<\/td>\n######<\/td>\n32167<\/td>\n184478<\/td>\n1E+06<\/td>\n41<\/td>\n8295<\/td>\n<\/tr>\n
2<\/td>\n150(\u542b\u4ee5\u4e0b)\u7acb\u65b9\u516c\u5206<\/td>\n0<\/td>\n0<\/td>\n######<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
3<\/td>\n151-250\u7acb\u65b9\u516c\u5206<\/td>\n27<\/td>\n5312<\/td>\n######<\/td>\n0<\/td>\n0<\/td>\n0<\/td>\n27<\/td>\n5312<\/td>\n<\/tr>\n
4<\/td>\n251-500\u7acb\u65b9\u516c\u5206<\/td>\n1<\/td>\n1279<\/td>\n######<\/td>\n0<\/td>\n1<\/td>\n0<\/td>\n1<\/td>\n1278<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>","protected":false},"excerpt":{"rendered":"
\u6279\u6b21\u8b80\u53d6excel\u6a94\u6848\uff08\u9644\u6a94\u540d\u70baxls\u6216xlsx\uff09 \u5be6\u505a\u7684\u8cc7\u6599\u593e\u76ee\u9304\u7d50\u69cb\u5982\u4e0b\uff1a \u5217\u51fa\u76ee\u9304\u4e0b\u7684\u6240\u6709\u5b50\u76ee\u9304\u53ca\u6a94\u6848\uff0c […]<\/p>\n","protected":false},"author":1,"featured_media":422,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advgb_blocks_editor_width":"","advgb_blocks_columns_visual_guide":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}}},"categories":[4],"tags":[177,180,179,8,9,178],"author_meta":{"display_name":"mackuo","author_link":"https:\/\/kuo.us.to\/wordpress\/author\/mackuo\/"},"featured_img":"https:\/\/kuo.us.to\/wordpress\/wp-content\/uploads\/2022\/08\/qcbz4aslhm8-300x200.jpg","coauthors":[],"tax_additional":{"categories":{"linked":["Python\u5b78\u7fd2\u7b46\u8a18<\/a>"],"unlinked":["Python\u5b78\u7fd2\u7b46\u8a18<\/span>"]},"tags":{"linked":["excel<\/a>","glob\u6a21\u7d44<\/a>","os\u6a21\u7d44<\/a>","Pandas<\/a>","Python<\/a>","\u6279\u6b21<\/a>"],"unlinked":["excel<\/span>","glob\u6a21\u7d44<\/span>","os\u6a21\u7d44<\/span>","Pandas<\/span>","Python<\/span>","\u6279\u6b21<\/span>"]}},"comment_count":"0","relative_dates":{"created":"Posted 4 \u5e74 ago","modified":"Updated 3 \u5e74 ago"},"absolute_dates":{"created":"Posted on 2022 \u5e74 8 \u6708 6 \u65e5","modified":"Updated on 2023 \u5e74 1 \u6708 17 \u65e5"},"absolute_dates_time":{"created":"Posted on 2022 \u5e74 8 \u6708 6 \u65e5 \u4e0b\u5348 1:56","modified":"Updated on 2023 \u5e74 1 \u6708 17 \u65e5 \u4e0b\u5348 4:48"},"featured_img_caption":"","series_order":"","_links":{"self":[{"href":"https:\/\/kuo.us.to\/wordpress\/wp-json\/wp\/v2\/posts\/416"}],"collection":[{"href":"https:\/\/kuo.us.to\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kuo.us.to\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kuo.us.to\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kuo.us.to\/wordpress\/wp-json\/wp\/v2\/comments?post=416"}],"version-history":[{"count":6,"href":"https:\/\/kuo.us.to\/wordpress\/wp-json\/wp\/v2\/posts\/416\/revisions"}],"predecessor-version":[{"id":423,"href":"https:\/\/kuo.us.to\/wordpress\/wp-json\/wp\/v2\/posts\/416\/revisions\/423"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/kuo.us.to\/wordpress\/wp-json\/wp\/v2\/media\/422"}],"wp:attachment":[{"href":"https:\/\/kuo.us.to\/wordpress\/wp-json\/wp\/v2\/media?parent=416"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kuo.us.to\/wordpress\/wp-json\/wp\/v2\/categories?post=416"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kuo.us.to\/wordpress\/wp-json\/wp\/v2\/tags?post=416"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}