Vfrdtyky

I have a table below. I would like to return in the haves column for each row in the table, column names where row values equals one, using python and pandas.

Location        House      car    Toys              haves

x                   1        1       3         House, Car

y                   2        1       1          Car, toys

First compare values by eq (==) with dot product with columns names and last remove last separator values by rstrip if performance is important

df['haves'] = df.eq(1).dot(df.columns + ', ').str.rstrip(', ')

#solution with omiting first column

#df['haves'] = df.iloc[:, 1:].eq(1).dot(df.columns[1:] + ', ').str.rstrip(', ')

print (df)

  Location  House  car  Toys       haves

0        x      1    1     3  House, car

1        y      2    1     1   car, Toys

Details:

print (df.eq(1))

   Location  House   car   Toys

0     False   True  True  False

1     False  False  True   True



print (df.eq(1).dot(df.columns + ', '))

0    House, car, 

1     car, Toys, 

dtype: object

Performance: depends of number of 1 values, number of columns and rows, but because dot is vectorized it is faster like loop solutions:

#2k rows

df = pd.concat([df] * 1000, ignore_index=True)



In [183]: %timeit df['haves'] = df.eq(1).dot(df.columns + ', ').str.rstrip(', ')

2.65 ms ± 34.2 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)



#working if no missing values 

In [184]: %timeit df['haves'] = [x.rstrip(', ') for x in df.eq(1).dot(df.columns + ', ')]

2.43 ms ± 38.5 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)



#jpp answer

In [185]: %timeit df['haves'] = [', '.join(df.columns[1:][idx]) for idx in df.iloc[:, 1:].eq(1).values]

86.5 ms ± 4.32 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)



#Naga Kiran removed answer

In [186]: %timeit df['have'] = df.apply(lambda x: ','.join(x[x.eq(1)].index),1)

813 ms ± 8.66 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

Assuming you need to create the haves series, you can use a list comprehension:

df['haves'] = [', '.join(df.columns[1:][idx]) for idx in df.iloc[:, 1:].eq(1).values]



print(df)



  Location  House  car  Toys       haves

0        x      1    1     3  House, car

1        y      2    1     1   car, Toys

I don't believe this task is easily vectorisable since you can have a variable number of values satisfying your condition, and your result will be an object dtype series.

Here is a simple way which is only a little slower than the dot method and may be easier to understand. It does use numpy to create the cols array which speeds things up considerably vs. just using df.columns as a list.

import numpy as np



# numpy array of dataframe column names

cols = np.array(df.columns)

# boolean array to mark where dataframe values equal 1

b = (df.values == 1)

# list comprehension to join column names for each boolean row result

df['haves'] = [', '.join(cols[(row_index)]) for row_index in b]