Python在Excel工作表中循环，将工作表名称添加到列表中，然后全部合并 [英] Python loop through excel sheets, add sheetname to list, then concat all

问题描述

我正在遍历Excel工作表并将其附加到列表中.循环结束后，我使用Pandas将其连接到单个数据帧.我遇到的问题是将工作表名称添加到适当的列表中.

I am looping through Excel worksheets and appending them to a list. When the loop finishes, I use Pandas to concat to a single dataframe. The problem I'm having is adding the worksheet name into the appropriate list.

# infile is a filepath variable    
xls = xlrd.open_workbook(infile, on_demand=True)



dfList = []
for sheet_name in xls.sheet_names():
    df = pd.read_excel(infile, sheet_name, header = 0)
    #df['Well_name'] = sheet_name
    dfList.append(df)
    print(sheet_name + " appended.")
    #time.sleep(2)
print("Loop complete")
# Concatenating the appended lists
dfs = pd.concat(dfList, axis=0)

我尝试在df中创建一个新列，但是这造成了长度不匹配，并且也无法正常工作，因为它不断地被重写为循环中的最后一个工作表名称.

I tried creating a new column in df but that created a length mismatch and it also didn't work because it was constantly rewritten to the last worksheet name in the loop.

有什么想法或建议吗?

推荐答案

似乎您正在遇到一些范围界定问题.避免此问题的一种方法是使用列表理解.您还可以使用pd.DataFrame.assign在列表理解范围内添加系列:

Seems like you are meeting some scoping issues. One way to avoid this problem is to use a list comprehension. You can also use pd.DataFrame.assign to add a series within your list comprehension:

dfList = [pd.read_excel(infile, sheet_name, header=0).assign(Well_name=sheet_name) \
          for sheet_name in xls.sheet_names()]

dfs = pd.concat(dfList, axis=0)

这篇关于Python在Excel工作表中循环，将工作表名称添加到列表中，然后全部合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！