Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Combine multiple categorical columns into one, when each row has only one non-NaN value, in Pandas

I have

import pandas as pd
import numpy as np
df = pd.DataFrame({"x": ["red", "blue", np.nan, np.nan, np.nan, np.nan, np.nan, ],
                   "y": [np.nan, np.nan, np.nan, 'cold', 'warm', np.nan, np.nan, ],
                   "z": [np.nan, np.nan,  np.nan, np.nan, np.nan, 'charm', 'strange'],
                  }).astype("category")

giving

     x     y        z
0   red   NaN      NaN
1  blue   NaN      NaN
2   NaN   NaN      NaN
3   NaN  cold      NaN
4   NaN  warm      NaN
5   NaN   NaN    charm
6   NaN   NaN  strange

I would like to add a new categorical column with unordered values red,blue,hot,cold,warm, charm, strange, filled in appropriately. I have many such columns, not just three.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

Some possiblities:

  • astype(str) and concatenating and then re-creating a categorical
  • creating a new categorical type using union_categoricals and then cast each column to that type? and then serially fillna() them?

I can’t make those or anything else work.

Notes:
using .astype(pd.CategoricalDtype(ordered=True)) in place of .astype("category") in defining df also works with the answer below.

>Solution :

Edited answer

As specified by the OP, in case there are rows where all values are np.NaN we could try the following solution:

df['new_col'] = df.dropna(how='all').apply(lambda x: x.loc[x.first_valid_index()], axis=1)
df['new_col'] = pd.Categorical(df.new_col)
df

      x     y        z  new_col
0   red   NaN      NaN      red
1  blue   NaN      NaN     blue
2   NaN   NaN      NaN      NaN
3   NaN  cold      NaN     cold
4   NaN  warm      NaN     warm
5   NaN   NaN    charm    charm
6   NaN   NaN  strange  strange
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading