python - Pandas 。基于缺失值和列名的新列

Question

假设我们有以下 df:

+---+---------+---------+--------+-------+
|   |  2016   |  2017   |  2018  | 2019  |
+---+---------+---------+--------+-------+
| 0 | 26560.0 | 26810.0 | NaN    | NaN   |
| 1 |   570.0 | NaN     | 550.0  | 540.0 |
| 2 |  3770.0 | 3450.0  | 3210.0 | NaN   |
| 3 |  4320.0 | NaN     | NaN    | NaN   |
+---+---------+---------+--------+-------+

我想添加两个额外的列“值(value)”和“年份”。在“值”列中将有最近一年的值，在“年”列中应该有没有缺失值的最近一年:

+---+---------+---------+--------+-------+---------+------+
|   |  2016   |  2017   |  2018  | 2019  |  value  | year |
+---+---------+---------+--------+-------+---------+------+
| 0 | 26560.0 | 26810.0 | NaN    | NaN   | 26810.0 | 2017 |
| 1 |   570.0 | NaN     | 550.0  | 540.0 |   540.0 | 2019 |
| 2 |  3770.0 | 3450.0  | 3210.0 | NaN   |  3210.0 | 2018 |
| 3 |  4320.0 | NaN     | NaN    | NaN   |  4320.0 | 2016 |
+---+---------+---------+--------+-------+---------+------+

你能帮我解决一下吗？谢谢!

Answer 1

使用DataFrame.assign对于新列，首先按位置选择最后一列向前填充每行缺失值，然后通过 DataFrame.idxmax 获取最后一个非缺失值，但有必要通过索引更改列的顺序:

df1 = df.assign(value = df.ffill(axis=1).iloc[:, -1],
                year = df.notna().iloc[:, ::-1].idxmax(axis=1))
print (df1)
      2016     2017    2018   2019    value  year
0  26560.0  26810.0     NaN    NaN  26810.0  2017
1    570.0      NaN   550.0  540.0    540.0  2019
2   3770.0   3450.0  3210.0    NaN   3210.0  2018
3   4320.0      NaN     NaN    NaN   4320.0  2016

上述解决方案仅在至少存在非错误值时才有效，用于一般解决方案使用 numpy.where对于缺失值，如果不存在 val:

print (df)
      2016     2017    2018   2019
0  26560.0  26810.0     NaN    NaN
1    570.0      NaN   550.0  540.0
2   3770.0   3450.0  3210.0    NaN
3      NaN      NaN     NaN    NaN

mask = df.notna()
df2 = df.assign(value = df.ffill(axis=1).iloc[:, -1],
               year = np.where(mask.any(axis=1), mask.iloc[:, ::-1].idxmax(axis=1), np.nan))
print (df2)
      2016     2017    2018   2019    value  year
0  26560.0  26810.0     NaN    NaN  26810.0  2017
1    570.0      NaN   550.0  540.0    540.0  2019
2   3770.0   3450.0  3210.0    NaN   3210.0  2018
3      NaN      NaN     NaN    NaN      NaN   NaN

关于 DataFrame.stack 的另一个想法和 DataFrame.drop_duplicates如果某些行仅包含缺失值，也可以工作:

df2 = df.join(df.stack()
                .reset_index(name='value')
                .drop_duplicates('level_0', keep='last')
                .rename(columns={'level_1':'year'})
                .set_index('level_0')
                [['value','year']])
print (df2)
      2016     2017    2018   2019    value  year
0  26560.0  26810.0     NaN    NaN  26810.0  2017
1    570.0      NaN   550.0  540.0    540.0  2019
2   3770.0   3450.0  3210.0    NaN   3210.0  2018
3   4320.0      NaN     NaN    NaN   4320.0  2016

---

df2 = df.join(df.stack()
                .reset_index(name='value')
                .drop_duplicates('level_0', keep='last')
                .rename(columns={'level_1':'year'})
                .set_index('level_0')
                [['value','year']])
print (df2)
      2016     2017    2018   2019    value  year
0  26560.0  26810.0     NaN    NaN  26810.0  2017
1    570.0      NaN   550.0  540.0    540.0  2019
2   3770.0   3450.0  3210.0    NaN   3210.0  2018
3      NaN      NaN     NaN    NaN      NaN   NaN