pandas有强大的excel数据处理和导入处理功能，本文简单介绍pandas在csv和excel等格式方面处理的应用及绘制图表等功能。

pandas处理excel依赖xlutils, OpenPyXL, XlsxWriter等库。

python处理excel库的参考：https://github.com/xurongzhong/mobile_data

本文代码地址：https://github.com/xurongzhong/mobile_data/tree/master/pandas/excel_demo

本文最新版本地址：http://dwz.cn/7ig569

淘宝天猫可以把链接发给qq850766020，为你生成优惠券，降低你的购物成本！

快来领取支付宝跨年红包！1月1日起还有机会额外获得专享红包哦！复制此消息，打开最新版支付宝就能领取！2C56CV70sA

更多参考资料：

https://www.dataquest.io/blog/excel-and-pandas/

Using Pandas to Read Large Excel Files in Python 中文

CSV

使用pandas读写csv

pandas_parsing_and_write.py

import pandas as pd  input_file = r"supplier_data.csv" output_file = r"output_files\1output.csv"  data_frame = pd.read_csv(input_file) print(data_frame) data_frame.to_csv(output_file, index=False)

当然也可以用python实现：

1csv_simple_parsing_and_write.py

input_file = r"supplier_data.csv" output_file = r"output_files\1output.csv"  with open(input_file, newline='') as filereader:     with open(output_file, 'w', newline='') as filewriter:         for row in filereader:             filewriter.write(row)

2csv_reader_parsing_and_write.py

import csv  input_file = r"supplier_data.csv" output_file = r"output_files\2output.csv"  with open(input_file, 'r', newline='') as csv_in_file:     with open(output_file, 'w', newline='') as csv_out_file:         filereader = csv.reader(csv_in_file, delimiter=',')         filewriter = csv.writer(csv_out_file, delimiter=',')         for row_list in filereader:             filewriter.writerow(row_list)

过滤特定行

• 选择供应商名字包含Z或者Cost大于600的行

pandas_value_meets_condition.py

import pandas as pd  input_file = r"supplier_data.csv" output_file = r"output_files\3output.csv"  data_frame = pd.read_csv(input_file)  data_frame['Cost'] = data_frame['Cost'].str.strip('$').astype(float) data_frame_value_meets_condition = data_frame.loc[(data_frame['Supplier Name']\ .str.contains('Z')) | (data_frame['Cost'] > 600.0), :]  data_frame_value_meets_condition.to_csv(output_file, index=False)

注意pandas的strip连里面的内容都可以清除, 有点类似replace的功能。

• 选择符合一个集合的数据：

选择日期为'1/20/14', '1/30/14'的行

import pandas as pd  input_file = r"supplier_data.csv" output_file = r"output_files\4output.csv"  data_frame = pd.read_csv(input_file)  important_dates = ['1/20/14', '1/30/14'] data_frame_value_in_set = data_frame.loc[data_frame['Purchase Date']\ .isin(important_dates), :]  data_frame_value_in_set.to_csv(output_file, index=False)

• 用正则表达式选择数据

pandas_value_matches_pattern.py

import pandas as pd  input_file = r"supplier_data.csv" output_file = r"output_files\4output.csv"  data_frame = pd.read_csv(input_file) data_frame_value_matches_pattern = data_frame.ix[data_frame['Invoice Number']\ .str.startswith("001-"), :]  data_frame_value_matches_pattern.to_csv(output_file, index=False)

过滤特定列

• 选择0,3列

pandas_column_by_index.py

import pandas as pd import sys  input_file = r"supplier_data.csv" output_file = r"output_files\6output.csv"  data_frame = pd.read_csv(input_file) data_frame_column_by_index = data_frame.iloc[:, [0, 3]] data_frame_column_by_index.to_csv(output_file, index=False)

pandas_column_by_index.py

import pandas as pd  input_file = r"supplier_data.csv" output_file = r"output_files\7output.csv"  data_frame = pd.read_csv(input_file) data_frame_column_by_name = data_frame.loc[     :, ['Invoice Number', 'Purchase Date']] data_frame_column_by_name.to_csv(output_file, index=False)

pandas_select_contiguous_rows.py

import pandas as pd  input_file = r"supplier_data_unnecessary_header_footer.csv" output_file = r"output_files\11output.csv"  data_frame = pd.read_csv(input_file, header=None) data_frame = data_frame.drop([0,1,2,16,17,18]) data_frame.columns = data_frame.iloc[0] data_frame = data_frame.reindex(data_frame.index.drop(3)) data_frame.to_csv(output_file, index=False)

添加行头

pandas_add_header_row.py

import pandas as pd  input_file = r"supplier_data_no_header_row.csv" output_file = r"output_files\11output.csv" header_list = ['Supplier Name', 'Invoice Number', \ 'Part Number', 'Cost', 'Purchase Date'] data_frame = pd.read_csv(input_file, header=None, names=header_list) data_frame.to_csv(output_file, index=False)

合并多个文件

pandas_concat_rows_from_multiple_files.py

import pandas as pd import glob import os  input_path = r"D:\code\foundations-for-analytics-with-python\csv" output_file = r"output_files\12output.csv"  all_files = glob.glob(os.path.join(input_path,'sales_*')) all_data_frames = [] for file in all_files:     data_frame = pd.read_csv(file, index_col=None)     all_data_frames.append(data_frame) data_frame_concat = pd.concat(all_data_frames, axis=0, ignore_index=True) data_frame_concat.to_csv(output_file, index = False)

求和和求平均值

pandas_sum_average_from_multiple_files.py

import pandas as pd import glob import os  input_path = r"D:\code\foundations-for-analytics-with-python\csv" output_file = r"output_files\12output.csv"  all_files = glob.glob(os.path.join(input_path,'sales_*')) all_data_frames = [] for input_file in all_files:     print(input_file)     data_frame = pd.read_csv(input_file, index_col=None)      print(data_frame)      sales = pd.DataFrame([float(str(value).strip('$').replace(',',''))        for value in data_frame.loc[:, 'Sale Amount']])      total_cost = sales.sum()     average_cost = sales.mean()      data = {'file_name': os.path.basename(input_file),             'total_sales': total_cost,             'average_sales': average_cost}      all_data_frames.append(pd.DataFrame(         data, columns=['file_name', 'total_sales', 'average_sales']))  data_frames_concat = pd.concat(all_data_frames, axis=0, ignore_index=True) data_frames_concat.to_csv(output_file, index = False)

XLS

使用pandas读写xls

pandas_parsing_and_write_keep_dates.py

import pandas as pd  input_file = "sales_2013.xlsx" output_file = "pandas_output.xls" data_frame = pd.read_excel(input_file, sheetname='january_2013')  writer = pd.ExcelWriter(output_file) data_frame.to_excel(writer, sheet_name='jan_13_output', index=False) writer.save()

过滤特定行

• 销售额大于1400的记录

pandas_value_meets_condition.py

import pandas as pd  input_file = "sales_2013.xlsx" output_file = "pandas_output.xls"  data_frame = pd.read_excel(input_file, 'january_2013', index_col=None) data_frame_value_meets_condition = \     data_frame[data_frame['Sale Amount'].astype(float) > 1400.0]  writer = pd.ExcelWriter(output_file) data_frame_value_meets_condition.to_excel(     writer, sheet_name='jan_13_output', index=False) writer.save()

• 指定日期的

pandas_value_in_set.py

import string  input_file = "sales_2013.xlsx" output_file = "pandas_output.xls"  data_frame = pd.read_excel(input_file, 'january_2013', index_col=None)  important_dates = ['01/24/2013','01/31/2013'] data_frame_value_in_set = data_frame[data_frame['Purchase Date'].isin(important_dates)]  writer = pd.ExcelWriter(output_file) data_frame_value_in_set.to_excel(writer, sheet_name='jan_13_output', index=False) writer.save()

• 其他条件

startswith , endswith , match和search等。

pandas_value_matches_pattern.py

import pandas as pd  input_file = "sales_2013.xlsx" output_file = "pandas_output.xls"  data_frame = pd.read_excel(input_file, 'january_2013', index_col=None)  data_frame_value_matches_pattern = data_frame[     data_frame['Customer Name'].str.startswith("J")]  writer = pd.ExcelWriter(output_file) data_frame_value_matches_pattern.to_excel(     writer, sheet_name='jan_13_output', index=False) writer.save()

选取特定列

• iloc基于ｉｎｄｅｘ选取第２和第５列

import pandas as pd  input_file = "sales_2013.xlsx" output_file = "pandas_output.xls"  data_frame = pd.read_excel(input_file, 'january_2013', index_col=None)  data_frame_column_by_index = data_frame.iloc[:, [1, 4]]  writer = pd.ExcelWriter(output_file) data_frame_column_by_index.to_excel(     writer, sheet_name='jan_13_output', index=False) writer.save()

• loc基于列名选取第２和第５列

pandas_column_by_name.py

import pandas as pd  input_file = "sales_2013.xlsx" output_file = "pandas_output.xls"  data_frame = pd.read_excel(input_file, 'january_2013', index_col=None)  data_frame_column_by_name = data_frame.loc[:, ['Customer ID', 'Purchase Date']]  writer = pd.ExcelWriter(output_file) data_frame_column_by_name.to_excel(     writer, sheet_name='jan_13_output', index=False) writer.save()

操作所有sheet

• 选取销售额大于2000的行

pandas_value_meets_condition_all_worksheets.py

import pandas as pd  input_file = "sales_2013.xlsx" output_file = "pandas_output.xls"  data_frame = pd.read_excel(input_file, sheetname=None, index_col=None)  row_output = [] for worksheet_name, data in data_frame.items():     row_output.append(data[data['Sale Amount'].replace('$', '').                            replace(',', '').astype(float) > 2000.0]) filtered_rows = pd.concat(row_output, axis=0, ignore_index=True)  writer = pd.ExcelWriter(output_file) filtered_rows.to_excel(writer, sheet_name='sale_amount_gt2000', index=False) writer.save()

• loc基于列名选取所有sheet的第2和第5列

pandas_value_meets_condition_all_worksheets.py

import pandas as pd  input_file = "sales_2013.xlsx" output_file = "pandas_output.xls"  data_frame = pd.read_excel(input_file, sheet_name=None, index_col=None)  column_output = [] for worksheet_name, data in data_frame.items():     column_output.append(data.loc[:, ['Customer Name', 'Sale Amount']]) selected_columns = pd.concat(column_output, axis=0, ignore_index=True)  writer = pd.ExcelWriter(output_file) selected_columns.to_excel(         writer, sheet_name='selected_columns_all_worksheets', index=False) writer.save()

操作部分sheet

• 选取销售额大于２０００的行

pandas_value_meets_condition_set_of_worksheets.py

import pandas as pd  input_file = "sales_2013.xlsx" output_file = "pandas_output.xls"  my_sheets = [0,1] threshold = 1900.0  data_frame = pd.read_excel(input_file, sheetname=my_sheets, index_col=None)  row_list = [] for worksheet_name, data in data_frame.items():     row_list.append(data[data['Sale Amount'].replace('$', '').                          replace(',', '').astype(float) > threshold]) filtered_rows = pd.concat(row_list, axis=0, ignore_index=True)  writer = pd.ExcelWriter(output_file) filtered_rows.to_excel(writer, sheet_name='set_of_worksheets', index=False) writer.save()

处理多个excel

• 连接concat

pandas_concat_data_from_multiple_workbooks.py

import pandas as pd import glob import os  input_path = "/media/andrew/6446FA2346F9F5A0/code/foundations-for-analytics-\ with-python/excel" output_file = "pandas_output.xls"  all_workbooks = glob.glob(os.path.join(input_path,'*.xls*')) data_frames = [] for workbook in all_workbooks:     all_worksheets = pd.read_excel(             workbook, sheet_name=None, index_col=None)     for worksheet_name, data in all_worksheets.items():         data_frames.append(data) all_data_concatenated = pd.concat(data_frames, axis=0, ignore_index=True)  writer = pd.ExcelWriter(output_file) all_data_concatenated.to_excel(         writer, sheet_name='all_data_all_workbooks', index=False) writer.save()

• 求和

pandas_sum_average_multiple_workbooks.py

import pandas as pd import glob import os  input_path = "/media/andrew/6446FA2346F9F5A0/code/foundations-for-analytics-\ with-python/excel" output_file = "pandas_output.xls"  all_workbooks = glob.glob(os.path.join(input_path,'*.xls*')) data_frames = [] for workbook in all_workbooks:     all_worksheets = pd.read_excel(workbook, sheetname=None, index_col=None)     workbook_total_sales = []     workbook_number_of_sales = []     worksheet_data_frames = []     worksheets_data_frame = None     workbook_data_frame = None     for worksheet_name, data in all_worksheets.items():         total_sales = pd.DataFrame(             [float(str(value).strip('$').replace(',','')) for value in               data.ix[:, 'Sale Amount']]).sum()         number_of_sales = len(data.loc[:, 'Sale Amount'])         average_sales = pd.DataFrame(total_sales / number_of_sales)          workbook_total_sales.append(total_sales)         workbook_number_of_sales.append(number_of_sales)          data = {'workbook': os.path.basename(workbook),                 'worksheet': worksheet_name,                 'worksheet_total': total_sales,                 'worksheet_average': average_sales}          worksheet_data_frames.append(             pd.DataFrame(data,                           columns=['workbook', 'worksheet', 'worksheet_total',                                    'worksheet_average']))     worksheets_data_frame = pd.concat(         worksheet_data_frames, axis=0, ignore_index=True)      workbook_total = pd.DataFrame(workbook_total_sales).sum()     workbook_total_number_of_sales = pd.DataFrame(         workbook_number_of_sales).sum()     workbook_average = pd.DataFrame(         workbook_total / workbook_total_number_of_sales)      workbook_stats = {'workbook': os.path.basename(workbook),                       'workbook_total': workbook_total,                       'workbook_average': workbook_average}      workbook_stats = pd.DataFrame(workbook_stats,                                    columns=['workbook', 'workbook_total',                                            'workbook_average'])     workbook_data_frame = pd.merge(         worksheets_data_frame, workbook_stats, on='workbook', how='left')     data_frames.append(workbook_data_frame)  all_data_concatenated = pd.concat(data_frames, axis=0, ignore_index=True)  writer = pd.ExcelWriter(output_file) all_data_concatenated.to_excel(     writer, sheet_name='sums_and_averages', index=False) writer.save()

使用excel绘制图表

import pandas as pd import random  # Some sample data to plot. cat_1 = ['y1', 'y2', 'y3', 'y4'] index_1 = range(0, 21, 1) multi_iter1 = {'index': index_1} for cat in cat_1:     multi_iter1[cat] = [random.randint(10, 100) for x in index_1]  # Create a Pandas dataframe from the data. index_2 = multi_iter1.pop('index') df = pd.DataFrame(multi_iter1, index=index_2) df = df.reindex(columns=sorted(df.columns))  # Create a Pandas Excel writer using XlsxWriter as the engine. excel_file = 'legend.xlsx' sheet_name = 'Sheet1'  writer = pd.ExcelWriter(excel_file, engine='xlsxwriter') df.to_excel(writer, sheet_name=sheet_name)  # Access the XlsxWriter workbook and worksheet objects from the dataframe. workbook = writer.book worksheet = writer.sheets[sheet_name]  # Create a chart object. chart = workbook.add_chart({'type': 'line'})  # Configure the series of the chart from the dataframe data. for i in range(len(cat_1)):     col = i + 1     chart.add_series({         'name':       ['Sheet1', 0, col],         'categories': ['Sheet1', 1, 0, 21, 0],         'values':     ['Sheet1', 1, col, 21, col],     })  # Configure the chart axes. chart.set_x_axis({'name': 'Index'}) chart.set_y_axis({'name': 'Value', 'major_gridlines': {'visible': False}})  # Insert the chart into the worksheet. worksheet.insert_chart('G2', chart)  # Close the Pandas Excel writer and output the Excel file. writer.save()

参考资料：http://pandas-xlsxwriter-charts.readthedocs.io/