Pandas从Excel读取时合并单元格问题

Question

Pandas从Excel读取时合并单元格问题

4

我有一个拥有奇怪样式的xls文件，但是我无能为力，只需要解析它。

如您所见，我有一些合并的单元格。我想要做的是填充合并单元格中的空值("ffill")，但同时保留空单元格原样。

像这样

EIM, C,NI1 Enescu_Ioan, EIM, S,NI11,Enescu_Ioan EIM, C,NI1 Enescu_Ioan, Empty EIM, C,NI1 Enescu_Ioan EIM, S,NI11,Enescu_Ioan EIM, C,NI1,Enescu_Ioan Empty 我现在加载文件的方式如下：

xl = pd.ExcelFile("data/file.xls")
df = xl.parse(0, header=None)

我也尝试以这种方式打开文件并访问合并单元格，但是我得到了一个空列表。

book = xlrd.open_workbook("data/file.xls")
book.sheet_by_index(0).merged_cells # This is empty []

有没有办法实现这个？谢谢！

编辑

关于问题可能会有一些混淆，所以我会尽量更好地解释。附加的图像是一个更大文件的子集，其中列可能以不同的顺序出现。我试图实现的是区分合并单元格NAN值（在合并单元格中只有第一列有值，其余都是nan）和空单元格NAN的方法。

- Radulescu Petru

你尝试过使用pandas的read_excel函数吗？请参考https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html。 - Mahendra Singh

@MahendraSingh 是的，我得到了相同的输出。 - Radulescu Petru

3个回答

0

如果你使用 df = pd.read_excel('path')，DataFrame看起来会像这样

print(df)

                     Col1                     Col2
0  EIM, C,NI1 Enescu_Ioan  EIM, S,NI11,Enescu_Ioan
1                     NaN                      NaN
2                     NaN  EIM, S,NI11,Enescu_Ioan
3                     NaN                      NaN

现在，从合并单元格的第一个条目填写值：

df['Col1'] = df['Col1'].fillna(method = 'ffill')

使用 pandas.DataFrame.replace 将 NaN 替换为空白：

df['Col2'] = df['Col2'].replace(np.nan,'')

一个例子：

df = pd.DataFrame({'Col1' : ['EIM, C,NI1 Enescu_Ioan',np.nan,np.nan,np.nan], 
                   'Col2' : ['EIM, S,NI11,Enescu_Ioan',np.nan,'EIM, S,NI11,Enescu_Ioan',np.nan]})

print(df)

                     Col1                     Col2
0  EIM, C,NI1 Enescu_Ioan  EIM, S,NI11,Enescu_Ioan
1                     NaN                      NaN
2                     NaN  EIM, S,NI11,Enescu_Ioan
3                     NaN                      NaN

df['Col1'] = df['Col1'].fillna(method = 'ffill')
df['Col2'] = df['Col2'].replace(np.nan,'')
print(df)
                     Col1                     Col2
0  EIM, C,NI1 Enescu_Ioan  EIM, S,NI11,Enescu_Ioan
1  EIM, C,NI1 Enescu_Ioan                         
2  EIM, C,NI1 Enescu_Ioan  EIM, S,NI11,Enescu_Ioan
3  EIM, C,NI1 Enescu_Ioan

- ManojK

是的，在这种情况下可以工作，但列可以以任何顺序出现，而这只是文件的一个小子集。我需要的是一种区分合并单元格nan和空单元格nan的方法。 - Radulescu Petru

这符合您预期的输出，请编辑您的问题以包括完整的问题和预期的输出，根据我的理解，当读取任何类型的空单元格时，pandas会返回NaN。 - ManojK

0

我更新了@radulescu-petru的解决方案，使其与openpyxl兼容。

import pandas as pd
import openpyxl

def read_excel(path):
    return openpyxl.load_workbook(path)

def parse_excel(excel_file, sheet_name):
    sheet = excel_file[sheet_name]
    df = pd.read_excel(file_path, sheet_name=sheet_name, header=None)
    return sheet, df
    
def fill_merged_na(sheet, dataframe):
    for merged_cell_range in sheet.merged_cells:
        # Get the start cell's row and column indices
        min_col, min_row, max_col, max_row = openpyxl.utils.cell.range_boundaries(str(merged_cell_range))
        # Get the value from the first cell
        base_value = df.iat[min_row-1, min_col-1]
        dataframe.iloc[min_row-1:max_row, min_col-1:max_col] = base_value
    return dataframe

excel_file = read_excel('some_file.xlsx')
sheet, df = parse_excel(excel_file, 'sheet A')
df_filled = fill_merged_na(sheet, df)
df_filled.head()

- LaserJesus

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Radulescu Petru · Accepted Answer

找到了解决方法。

def read_excel(path):
    excel = None
    if path.endswith('xlsx'):
        excel = pd.ExcelFile(xlrd.open_workbook(path), engine='xlrd')
    elif path.endswith('xls'):
        excel = pd.ExcelFile(xlrd.open_workbook(path, formatting_info=True), engine='xlrd')
    else:
        raise ValueError("Could not read this type of data")
    return excel

def parse_excel(excel_file):
    sheet_0 = excel_file.book.sheet_by_index(0)
    df = excel_file.parse(0, header=None)
    return sheet_0, df

def fill_merged_na(sheet, dataframe):
    for e in sheet.merged_cells:
        rl, rh, cl, ch = e
        base_value = sheet.cell_value(rl, cl)
        dataframe.iloc[rl:rh, cl:ch] = base_value
    return dataframe

一些重要的部分包括使用 formatting_info 参数为 True 打开 Excel 文件，以便读取格式（例如合并单元格），以及使用 fill_merged_na 函数仅填充合并后的 NaN 值，但保留初始的空单元格。