7

I have a onehot-encoded columns,df with zeros as "nan". I'm trying to convert onehot encoded columns to a single column.

Assume the below dataframe, df

    p1   |   p2  |   p3   |  p4   |  p5   |
---------------------------------------
0   cat     nan     nan     nan      nan
1   nan     dog     nan     nan      nan
2   nan     nan     horse   nan      nan
3   nan     nan     nan     donkey   nan
4   nan     nan     nan     nan      pig

Required Output

    animals
-----------------
0   cat
1   dog
2   horse
3   donkey
4   pig
Improve this question
1
  • Please be more descriptive with your question, it will help a lot with the other developers to read and share solutions to this problem. e.g. whether you are looking for a SQL query or code in some language to do this? and how currently you are doing? Will recommend following StackOverflow Q&A guidelines: stackoverflow.com/help/how-to-ask Commented Nov 28, 2019 at 9:19

3 Answers 3

Reset to default
6

If there is always only one non missing value per rows use forward filling missing values (like DataFrame.fillna with method='ffill') and then select last column by position with DataFrame.iloc, also for one column DataFrame add Series.to_frame:

df = df.ffill(axis=1).iloc[:, -1].to_frame('new')
print (df)
      new
0     cat
1     dog
2   horse
3  donkey
4     pig

If possible more non missing values per rows use DataFrame.stack with join per first level:

print (df)
    p1   p2     p3      p4    p5
0  cat  NaN    NaN     NaN  lion
1  NaN  dog    NaN     NaN   NaN
2  NaN  NaN  horse     NaN   NaN
3  NaN  NaN    NaN  donkey   NaN
4  NaN  NaN    NaN     NaN   pig

df2 = df.stack().groupby(level=0).apply(', '.join).to_frame('new')
print (df2)
         new
0  cat, lion
1        dog
2      horse
3     donkey
4        pig

Or lambda function:

df2 = df.apply(lambda x: x.dropna().str.cat(sep=', '), axis=1).to_frame('new')
print (df2)
         new
0  cat, lion
1        dog
2      horse
3     donkey
4        pig
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Given the OP's example even np.diag(df) would work... but that's not likely not to be a practical solution...
0

If you have one word per row you can fill NaN with empty strings and sum by row:

df.fillna('').sum(axis=1)

Result:

0       cat
1       dog
2     horse
3    donkey
4       pig
dtype: object
Improve this answer

Comments

0

Silly but working. Not sure what you expect if you have >1 not NA for the same index.

df['animals'] = df[df.columns[0]]
for c in df.columns[1:]:
   df['animals'].fillna(df[c], inplace=True)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.