当Pandas DataFrame中下一行的所有列都为NaN时，更新行索引

Question

我有一个Pandas DataFrame，它是用tabula py从PDF中提取的。

PDF是这样的：

+--------------+--------+-------+
|     name     | letter | value |
+--------------+--------+-------+
| A short name | a      |     1 |
+-------------------------------+
| Another      | b      |     2 |
+-------------------------------+
| A very large | c      |     3 |
| name         |        |       |
+-------------------------------+
| other one    | d      |     4 |
+-------------------------------+
| My name is   | e      |     5 |
| big          |        |       |
+--------------+--------+-------+

正如您所看到的，A very large name有一个换行符，并且由于原始pdf没有边框，所以在DataFrame中创建了一个带有['name', NaN, NaN]的行和另一个带['A very large', 'c', 3]的行，而我只想要一个带有内容的行：['A very large name', 'c', 3]。

My name is big也是如此

由于这发生在几行中，我试图实现的是当该行中的其他单元格为NaN时，将name单元格的内容与前一个单元格连接起来。然后删除NaN行。

但任何其他获得同样结果的策略都是受欢迎的。

import pandas as pd
import numpy as np
data = {
"name": ["A short name", "Another", "A very large", "name", "other one", "My name is", "big"],
"letter": ["a", "b", "c", np.NaN, "d", "e", np.NaN],
"value": [1, 2, 3, np.NaN, 4, 5, np.NaN],
}
df = pd.DataFrame(data)
data_expected = {
"name": ["A short name", "Another", "A very large name", "other one", "My name is big"],
"letter": ["a", "b", "c", "d", "e"],
"value": [1, 2, 3, 4, 5],
}
df_expected = pd.DataFrame(data_expected)

我正在尝试这样的代码，但不起作用

# Not works and not very `pandastonic`
nan_indexes = df[df.iloc[:, 1:].isna().all(axis='columns')].index 
df.loc[nan_indexes - 1, "name"] = df.loc[nan_indexes - 1, "name"].str.cat(df.loc[nan_indexes, "name"], ' ')
# remove NaN rows

Answer 1

根据列的不同，您可以尝试使用groupby.agg和join或first。通过检查列字母和值中的CCD_ 11和CCD_。

print (df.groupby(df[['letter', 'value']].notna().any(1).cumsum())
.agg({'name': ' '.join, 'letter':'first', 'value':'first'})
)
name letter  value
1       A short name      a    1.0
2            Another      b    2.0
3  A very large name      c    3.0
4          other one      d    4.0
5     My name is big      e    5.0