{"id":2255,"date":"2023-08-20T22:18:08","date_gmt":"2023-08-20T14:18:08","guid":{"rendered":"http:\/\/viplao.com\/?p=2255"},"modified":"2023-08-20T22:19:05","modified_gmt":"2023-08-20T14:19:05","slug":"python-%e6%97%a5%e5%b8%b8%e6%95%b0%e6%8d%ae%e5%88%86%e6%9e%90-excel%e5%ae%9e%e6%88%98","status":"publish","type":"post","link":"http:\/\/viplao.com\/index.php\/2023\/08\/20\/python-%e6%97%a5%e5%b8%b8%e6%95%b0%e6%8d%ae%e5%88%86%e6%9e%90-excel%e5%ae%9e%e6%88%98\/","title":{"rendered":"PYTHON \u65e5\u5e38\u6570\u636e\u5206\u6790 \u2013 excel\u5b9e\u6218"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code><strong>excel \u6587\u4ef6\u5408\u5e76\u5b9e\u6218\n<\/strong>import os,zipfile\nimport pandas as pd\nzip_files = &#91;'sales_data.zip','traffic_data.zip']\nfor file in zip_files:\n    fz = zipfile.ZipFile(file)\n    fz.extractall()\ndef read_data(file_name):\n    data = pd.read_csv(file_name)\n    data&#91;'\u65e5\u671f']=os.path.splitext(file_name)&#91;0]&#91;-8:]\n    return data\ntwo_data_path = &#91;'traffic_data','sales_data']\ntwo_df_list = &#91;]\nfor each_path in two_data_path:\n    files = &#91;os.path.join(each_path,i) for i in os.listdir(each_path)] # \u83b7\u53d6\u6bcf\u4e2a\u6587\u4ef6\u8def\u5f84    \n    df_list = &#91;read_data(i) for i in files] # \u83b7\u53d6\u6bcf\u4e2a\u6587\u4ef6\u6570\u636e\n    two_df_list.append(pd.concat(df_list))\n# \u5408\u5e76\u4e24\u4e2adf\nmerge_df = pd.merge(two_df_list&#91;0],two_df_list&#91;1],on=&#91;'\u65e5\u671f','\u4ea7\u54c1SKU'],how='outer')\nprint(merge_df.head(3))\n# \u683c\u5f0f\u8c03\u6574\nmerge_df2  = merge_df&#91;&#91;'\u65e5\u671f','\u4ea7\u54c1SKU', '\u4ea7\u54c1\u6d4f\u89c8\u91cf', '\u4ea7\u54c1\u52a0\u5165\u8d2d\u7269\u8f66\u6b21\u6570',  '\u8ba2\u5355\u4ea7\u54c1\u6570\u91cf', '\u8ba2\u5355\u4ea7\u54c1\u6536\u5165']]\nmerge_df2&#91;'\u65e5\u671f']= &#91;pd.datetime.strptime(i,'%Y%m%d') for i in merge_df2&#91;'\u65e5\u671f']]\nprint(merge_df2.head(3))\n# \u8f93\u51fa\u6570\u636e\u5230\u5355\u72ec\u6587\u4ef6\nmerge_df2.to_excel('merge_data.xlsx',index=False)<\/code><\/pre>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[28],"views":1020,"_links":{"self":[{"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts\/2255"}],"collection":[{"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/comments?post=2255"}],"version-history":[{"count":1,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts\/2255\/revisions"}],"predecessor-version":[{"id":2269,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts\/2255\/revisions\/2269"}],"wp:attachment":[{"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/media?parent=2255"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/categories?post=2255"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/tags?post=2255"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}