我必须读取多个大的excel文件来尝试清理数据。
最后一个问题是,有些单元格中有多行,或者我猜有些单元格跨越了多行。
它是这样的:
Index Col1 Col2 Col3
1 row1 row1 row1
2 row1.1
3 row1.2
4 row2 row2 row3当我使用Pandas.read_excel(文件名)或Pandas.ExcelFile,然后使用sheet.parse(sheetname)时,它当然会读取索引2和3,其中大部分是空白行。
根据Col1的跨度,我该如何将索引2和索引3合并为1?
为了清楚起见,我的问题是:我怎样才能读入一个excel文件,并根据第一列的跨度合并行?这有可能吗?
谢谢
发布于 2021-11-04 18:55:37
我不知道这个功能是不是内置在Pandas中的,因为坦率地说,Excel不打算这样使用,但人们仍然倾向于滥用它。伙计,我讨厌Excel.but,这是另一个话题。
我认为这里最好的方法是根据您所知道的应用于这些文件的逻辑来定义一个自定义函数。由于我目前正在进行一个项目,处理各种不同且格式不佳的Excel文件,我对这种垃圾太熟悉了。
这是我的建议,基于我对数据的理解和你所问的。它可能需要根据您的文件的具体情况进行更改。
last_valid = None
check_cols = [] # if only need to check a subset of cols for validity, do it here
for i, s in df.iterrows(): # This is slow, but probably necessary in this case
""" If all the rows are valid, we want to keep it as a reference in case
the following rows are not """
if all(s[check_cols].notna()):
lvi, last_valid = i, s
# need to store index and series so we can go back and replace it
continue
else: # here is the critical part
extra_vals = s[s.notna()] # find cells in row that have actual values
for col in extra_vals.index:
""" I'm creating a list and appending here since I don't know
your values or how they need to be handled exactly"""
last_valid[col] = list(last_valid[col]).append(extra_vals[col])
# replace that row in the dataframe
df.iloc[lvi, :] = last_valid
# drop extra rows:
df = df.dropna(axis=0, subset=check_cols)希望这对你有用!
发布于 2021-11-05 20:12:18
@LiamFiddler的答案是正确的,但需要一些调整才能在我的情况下工作,因为我在同一行上组合数字,并将csv作为字符串。我把我的贴出来,以防对来这里的人有帮助。
last_valid = None
check_cols = ['Col1'] # if only need to check a subset of cols for validity, do it here
df = df.astype(str) #convert all columns to strings as I have to combine numbers in the same cell
df = df.replace('nan','') #get rid of the nan created back to a blank string
for i, s in df.iterrows(): # This is slow, but probably necessary in this case
""" If all the rows are valid, we want to keep it as a reference in case
the following rows are not """
if all(s[check_cols] != ''):
lvi, last_valid = i, s
# need to store index and series so we can go back and replace it
continue
else: # here is the critical part
extra_vals = s[s != ''] # find cells in row that have actual values
for col in extra_vals.index:
""" I'm creating a list and appending here since I don't know
your values or how they need to be handled exactly"""
last_valid[col] = last_valid[col] + "," + extra_vals[col] #separate by whatever you wish, list was causing issues
# replace that row in the dataframe
df.iloc[lvi, :] = last_valid
# drop extra rows:
df = df[df['Col1'] != ''].reset_index(drop=True)https://stackoverflow.com/questions/69844101
复制相似问题