我有一组csv文件保存在一个文件夹中,我想循环遍历,转换成pandas数据框,执行一系列操作,然后保存到一个Excel文件中,每个数据框保存到它自己的工作表中。
这是我到目前为止的代码。
from pathlib import Path
import pandas as pd
dir_b = r'/Desktop/MyProjects'
writer = pd.ExcelWriter('Compiled File.xlsx')
for csv in Path(dir_b).glob('*.csv'):
df_list = []
df = pd.read_csv(csv, encoding = 'ISO-8859-1', engine='python', delimiter = ',')
car_column = df.pop('car')
df.insert(9, 'car', car_column)
df_list.append(df)
for i, df in enumerate(df_list):
df.to_excel(writer, sheet_name = 'Sheet' + str(i+1), index = False)
writer.save()
一切似乎工作,除了保存到Excel文件。当我运行代码时没有错误,但最终的Excel文件只显示来自1个数据帧的1个工作表。
每次迭代都清空数组。试试这个:
from pathlib import Path
import pandas as pd
dir_b = r'/Desktop/MyProjects'
writer = pd.ExcelWriter('Compiled File.xlsx')
df_list = []
for csv in Path(dir_b).glob('*.csv'):
df = pd.read_csv(csv, encoding = 'ISO-8859-1', engine='python', delimiter = ',')
car_column = df.pop('car')
df.insert(9, 'car', car_column)
df_list.append(df)
for i, df in enumerate(df_list):
df.to_excel(writer, sheet_name = 'Sheet' + str(i+1), index = False)
writer.save()
df_list
定义应该在for循环之外。否则,它将在每次迭代中被清空-
df_list = []
for csv in Path(dir_b).glob('*.csv'):
df = pd.read_csv(csv, encoding = 'ISO-8859-1', engine='python', delimiter = ',')
car_column = df.pop('car')
df.insert(9, 'car', car_column)
df_list.append(df)
for i, df in enumerate(df_list):
df.to_excel(writer, sheet_name = 'Sheet' + str(i+1), index = False)
writer.save()