python - 熊猫。基于缺失值和列名的新列
问题描述
假设我们有以下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' 列中将有最近一年的值,在 'year' 列中应该有最近一年没有缺失值:
+---+---------+---------+--------+-------+---------+------+
| | 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 |
+---+---------+---------+--------+-------+---------+------+
你能帮我解决它吗?谢谢!
解决方案
用于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
推荐阅读
- php - 检查时间是否在营业时间(比较逻辑)
- vue.js - 删除 v-image-input 上的奇怪填充/边距
- node.js - Mosca MQTT 代理(node.js)与 MongdoDB
- python - 从列表中转换列名
- postgresql - 如何为 docker-compose 和 DockerFile 设置不同的上下文
- python - 如何提高 pandas Pivot 速度?
- yarnpkg - 从 CodeArtifact 安装私有 npm 包失败,带有 yarn 和 yarn.lock 的“401 Unauthorized”
- python - 为什么计算机会生成这么多随机位置?
- azure - az acr build 要求
范围 - java - 如何正确设置此端点?