将Excel读取到从第5行开始并包括标头的python数据框 [英] reading excel to a python data frame starting from row 5 and including headers

问题描述

如何将excel数据导入python中的数据框.

how do I import excel data into a dataframe in python.

基本上，当前的excel工作簿在打开时会运行一些vba，这会刷新数据透视表并执行其他一些操作.

Basically the current excel workbook runs some vba on opening which refreshes a pivot table and does some other stuff.

然后，我希望将数据透视表刷新的结果导入python中的数据框以进行进一步分析.

Then I wish to import the results of the pivot table refresh into a dataframe in python for further analysis.

import xlrd

wb = xlrd.open_workbook('C:\Users\cb\Machine_Learning\cMap_Joins.xlsm')

#sheetnames
print wb.sheet_names()

#number of sheets
print wb.nsheets

刷新和打开文件可以正常工作.但是，我该如何从第5行的第一张纸中选择数据，包括从标题到最后一条记录n的数据.

The refreshing and opening of the file works fine. But how do i select the data from the first sheet from say row 5 including header down to last record n.

推荐答案

您可以使用熊猫的ExcelFile

You can use pandas' ExcelFile parse method to read Excel sheets, see io docs:

xls = pd.ExcelFile('C:\Users\cb\Machine_Learning\cMap_Joins.xlsm')

df = xls.parse('Sheet1', skiprows=4, index_col=None, na_values=['NA'])

skiprows将忽略前4行(即从行索引4开始)，以及一些

skiprows will ignore the first 4 rows (i.e. start at row index 4), and several other options.

这篇关于将Excel读取到从第5行开始并包括标头的python数据框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！