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

Time:12-10

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.

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.

CodePudding user response:

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
  • Related