pandas :使用合并的单元格读取Excel [英] Pandas: Reading Excel with merged cells

问题描述

我有包含多个工作表的Excel文件，每个文件看起来都像这样(但更长):

I have Excel files with multiple sheets, each of which looks a little like this (but much longer):

        Sample  CD4     CD8
Day 1   8311    17.3    6.44
        8312    13.6    3.50
        8321    19.8    5.88
        8322    13.5    4.09
Day 2   8311    16.0    4.92
        8312    5.67    2.28
        8321    13.0    4.34
        8322    10.6    1.95

第一列实际上是四个垂直合并的单元格.

The first column is actually four cells merged vertically.

当我使用pandas.read_excel读取此内容时，我得到一个如下所示的DataFrame:

When I read this using pandas.read_excel, I get a DataFrame that looks like this:

       Sample    CD4   CD8
Day 1    8311  17.30  6.44
NaN      8312  13.60  3.50
NaN      8321  19.80  5.88
NaN      8322  13.50  4.09
Day 2    8311  16.00  4.92
NaN      8312   5.67  2.28
NaN      8321  13.00  4.34
NaN      8322  10.60  1.95

如何让熊猫了解合并的细胞，或者快速轻松地删除NaN并按适当的值分组? (一种方法是重置索引，逐步查找值并将NaN替换为值，传入天数列表，然后将索引设置为该列.但似乎应该有一个更简单的方法.) /p>

How can I either get Pandas to understand merged cells, or quickly and easily remove the NaN and group by the appropriate value? (One approach would be to reset the index, step through to find the values and replace NaNs with values, pass in the list of days, then set the index to the column. But it seems like there should be a simpler approach.)

推荐答案

您可以使用

You could use the Series.fillna method to forword-fill in the NaN values:

df.index = pd.Series(df.index).fillna(method='ffill')

---

例如，

---

For example,

In [42]: df
Out[42]: 
       Sample    CD4   CD8
Day 1    8311  17.30  6.44
NaN      8312  13.60  3.50
NaN      8321  19.80  5.88
NaN      8322  13.50  4.09
Day 2    8311  16.00  4.92
NaN      8312   5.67  2.28
NaN      8321  13.00  4.34
NaN      8322  10.60  1.95

[8 rows x 3 columns]

In [43]: df.index = pd.Series(df.index).fillna(method='ffill')

In [44]: df
Out[44]: 
       Sample    CD4   CD8
Day 1    8311  17.30  6.44
Day 1    8312  13.60  3.50
Day 1    8321  19.80  5.88
Day 1    8322  13.50  4.09
Day 2    8311  16.00  4.92
Day 2    8312   5.67  2.28
Day 2    8321  13.00  4.34
Day 2    8322  10.60  1.95

[8 rows x 3 columns]

这篇关于 pandas :使用合并的单元格读取Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！