csv 文件按列分表、数据透视
1. 单个 excel (或csv)文件 按列分表
import pandas as pd
import xlsxwriter
import glob
input_path = "/Users/libin/Desktop/libin/"
ouput_path = "/Users/libin/Desktop/libin/"
file_name = "test.csv"
df = pd.read_csv(input_path + file_name, skiprows = 4, nrows = 1000)
writer = pd.ExcelWriter(ouput_path + file_name[:-4] + '.xlsx',engine='xlsxwriter')
for name_sheet,group_sheet in df.groupby("要分组的列名"):
group_sheet.to_excel(writer, sheet_name = name_sheet, index = False)
writer.save()
2. 目录下所有的文件按列分表
import pandas as pd
import xlsxwriter
import glob
input_path = "/Users/libin/Desktop/libin/"
ouput_path = "/Users/libin/Desktop/libin/"
for file_name in glob.glob("*.csv"):
df = pd.read_csv(input_path + file_name, skiprows = 4 )
writer = pd.ExcelWriter(ouput_path + file_name[:-4] + '.xlsx',engine='xlsxwriter')
for name_sheet,group_sheet in df.groupby("要分组的列名"):
group_sheet.to_excel(writer, sheet_name = name_sheet, index = False)
writer.save()
3. 目录下所有的文件按列分表,并创建数据透视
import pandas as pd
import numpy as np
import xlsxwriter
import glob
input_path = "/Users/libin/Desktop/libin/"
ouput_path = "/Users/libin/Desktop/libin/"
for file_name in glob.glob("*.csv"):
df = pd.read_csv(input_path + file_name, skiprows = 4 )
writer = pd.ExcelWriter(ouput_path + file_name[:-4] + '.xlsx',engine='xlsxwriter')
for name_sheet,group_sheet in df.groupby("Specie"):
df_table = group_sheet.pivot_table(
index=['City'],
columns=['Date'],
dropna = False,
values=['median'])
df_table.to_excel(writer, sheet_name = name_sheet)
writer.save()