PYTHON 日常数据分析 – excel实战

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<strong>excel 文件合并实战

</strong>import os,zipfile

import pandas as pd

zip_files = &#091;'sales_data.zip','traffic_data.zip']

for file in zip_files:

    fz = zipfile.ZipFile(file)

    fz.extractall()

def read_data(file_name):

    data = pd.read_csv(file_name)

    data&#091;'日期']=os.path.splitext(file_name)&#091;0]&#091;-8:]

    return data

two_data_path = &#091;'traffic_data','sales_data']

two_df_list = &#091;]

for each_path in two_data_path:

    files = &#091;os.path.join(each_path,i) for i in os.listdir(each_path)] # 获取每个文件路径    

    df_list = &#091;read_data(i) for i in files] # 获取每个文件数据

    two_df_list.append(pd.concat(df_list))

# 合并两个df

merge_df = pd.merge(two_df_list&#091;0],two_df_list&#091;1],on=&#091;'日期','产品SKU'],how='outer')

print(merge_df.head(3))

# 格式调整

merge_df2  = merge_df&#091;&#091;'日期','产品SKU', '产品浏览量', '产品加入购物车次数',  '订单产品数量', '订单产品收入']]

merge_df2&#091;'日期']= &#091;pd.datetime.strptime(i,'%Y%m%d') for i in merge_df2&#091;'日期']]

print(merge_df2.head(3))

# 输出数据到单独文件

merge_df2.to_excel('merge_data.xlsx',index=False)