For each row, add column name to list in new column if row value matches a condition

Admin 1
Viewed 751

I have a series of columns, each containing either Y or N.

I would like to create a new column that contains a list of columns (for that particular row) that contain Y.

Old DataFrame

>>> df

     col1        col2        col3         col4        col5
a    Y           N           N            N           Y
b    Y           N           Y            Y           Y
c    N           N           Y            N           N

New Dataframe

>>> df_new

     col1        col2        col3         col4        col5     col6
a    Y           N           N            N           Y        [col1, col5]
b    Y           N           Y            Y           Y        [col1, col3, col4, col5]
c    N           N           Y            N           N        [col3]

So far I can get it working for a single column with:

df["col6"] = ["col1" if val == "Y" else "" for val in df["col1"]]

But ideally I want to do the same for all columns, so I somehow end up with the result above. I could imagine doing some kind of loop, but then how I go about appending the result to the list value in col6 I'm unsure on. Can someone steer me in the right direction please?

pandas dataframe python-3.x
1 Answers

Compare values by Y first, then use DataFrame.dot with Series.str.split:

df["col6"] = df.eq('Y').dot(df.columns + ',').str[:-1].str.split(',')
print (df)
  col1 col2 col3 col4 col5                      col6
a    Y    N    N    N    Y              [col1, col5]
b    Y    N    Y    Y    Y  [col1, col3, col4, col5]
c    N    N    Y    N    N                    [col3]

Or if need better performance use list comprehension with numpy arrays:

cols = df.columns.to_numpy()
df["col6"] = [cols[x].tolist() for x in df.eq('Y').to_numpy()]
user_29010020
Related