使用Pandas可以从Excel文件中读取多个表格吗? 类似这样: 从第0行到第100行读取table1 从第102行到第202行读取table2 ...
我编写了以下代码,可以自动识别多个表格,以便您处理许多文件并且不想查看每个文件以获取正确的行号。该代码还会在每个表格上方查找非空行,并将其读取为表格元数据。
def parse_excel_sheet(file, sheet_name=0, threshold=5):
'''parses multiple tables from an excel sheet into multiple data frame objects. Returns [dfs, df_mds], where dfs is a list of data frames and df_mds their potential associated metadata'''
xl = pd.ExcelFile(file)
entire_sheet = xl.parse(sheet_name=sheet_name)
# count the number of non-Nan cells in each row and then the change in that number between adjacent rows
n_values = np.logical_not(entire_sheet.isnull()).sum(axis=1)
n_values_deltas = n_values[1:] - n_values[:-1].values
# define the beginnings and ends of tables using delta in n_values
table_beginnings = n_values_deltas > threshold
table_beginnings = table_beginnings[table_beginnings].index
table_endings = n_values_deltas < -threshold
table_endings = table_endings[table_endings].index
if len(table_beginnings) < len(table_endings) or len(table_beginnings) > len(table_endings)+1:
raise BaseException('Could not detect equal number of beginnings and ends')
# look for metadata before the beginnings of tables
md_beginnings = []
for start in table_beginnings:
md_start = n_values.iloc[:start][n_values==0].index[-1] + 1
md_beginnings.append(md_start)
# make data frames
dfs = []
df_mds = []
for ind in range(len(table_beginnings)):
start = table_beginnings[ind]+1
if ind < len(table_endings):
stop = table_endings[ind]
else:
stop = entire_sheet.shape[0]
df = xl.parse(sheet_name=sheet_name, skiprows=start, nrows=stop-start)
dfs.append(df)
md = xl.parse(sheet_name=sheet_name, skiprows=md_beginnings[ind], nrows=start-md_beginnings[ind]-1).dropna(axis=1)
df_mds.append(md)
return dfs, df_mds
df_mds
列表是什么?因为所有的df都被放置在dfs
中。 - bl79假设我们有以下Excel文件:
解决方案: 我们正在解析第一个工作表(索引:0
)。
xl = pd.ExcelFile(fn)
nrows = xl.book.sheet_by_index(0).nrows
df1 = xl.parse(0, skipfooter= nrows-(10+1)).dropna(axis=1, how='all')
df2 = xl.parse(0, skiprows=12).dropna(axis=1, how='all')
编辑: skip_footer
被替换成 skipfooter
。
结果:
In [123]: df1
Out[123]:
a b c
0 78 68 33
1 62 26 30
2 99 35 13
3 73 97 4
4 85 7 53
5 80 20 95
6 40 52 96
7 36 23 76
8 96 73 37
9 39 35 24
In [124]: df2
Out[124]:
c1 c2 c3 c4
0 78 88 59 a
1 82 4 64 a
2 35 9 78 b
3 0 11 23 b
4 61 53 29 b
5 51 36 72 c
6 59 36 45 c
7 7 64 8 c
8 1 83 46 d
9 30 47 84 d
首先读取整个 csv
文件:
import pandas as pd
df = pd.read_csv('path_to\\your_data.csv')
接着,可以通过以下方式获取单独的帧:
df1 = df.iloc[:100,:]
df2 = df.iloc[100:200,:]
skiprows
和nrows
参数来处理。不幸的是,pd.read_excel
函数还没有实现nrows
参数。 - MaxU - stand with Ukraine