我正在清理一个混乱的数据框,其中某些所需的信息出现在列名称中.该信息应融合到将要创建的单个列中.
index name animal fruit veg
--------------------------------------------------
0 cow animal NaN NaN
1 apple NaN fruit NaN
2 carrot NaN NaN veg
3 dog animal NaN NaN
4 horse animal NaN NaN
5 car NaN NaN NaN
6 pear NaN fruit NaN
7 pepper NaN NaN veg
8 cucumber NaN NaN veg
9 house NaN NaN NaN
我尝试使用pandas.melt()函数,但是它会返回很多行,这些行带有“错误的” NaN值和重复项.
有些行应该显示NaN,但只有那些不适合列名中指定类别的行,因此我不能使用pandas.dropna().
这是我使用的代码：
import pandas as pd
pd.melt(df,id_vars=['index','name'],value_vars=['animal','fruit','veg'],var_name='type')
我需要的结果应如下所示：
index name type
--------------------------------------------------
0 cow animal
1 apple fruit
2 carrot veg
3 dog animal
4 horse animal
5 car NaN
6 pear fruit
7 pepper veg
8 cucumber veg
9 house NaN
最佳答案
您可以执行操作(假设索引不是column,而是索引),在axis = 1上使用
df.ffill()：
df['type']=df[df.columns[1:]].ffill(axis=1).iloc[:,-1]
#alternatively-> df['type']=df.loc[:,['animal','veg']].ffill(axis=1).iloc[:,-1]
df_new=df[['name','type']]
print(df_new)
name type
index
0 cow animal
1 apple fruit
2 carrot veg
3 dog animal
4 horse animal
5 car NaN
6 pear fruit
7 pepper veg
8 cucumber veg
9 house NaN