How can I iterate over column values in Pandas and create a new observation based on the values of multiple columns in the same row?

user_12370479 0
Viewed 100

So there are many variations of this question posted, but none of them are exactly what I am looking for.

I have a dataset that contains multiple columns that hold boolean values that indicate if a user listed a city name in their place of residence. It is possible for a user to have named multiple city names. See below.

Dateset example:

+----+----------+-------+-------------+------------------------------------+
| ID | New York |  LA   | New Orleans |              New Var               |
+----+----------+-------+-------------+------------------------------------+
|  1 | True     | False | True        | 'New York, NY', 'New Orleans, LA'  |
|  2 | True     | False | False       | 'New York, NY                      |
|  3 | False    | False | False       | 'None'                             |
+----+----------+-------+-------------+------------------------------------+

What I am trying to do is create a new column that is the store the text names for all of the city names that the user listed. I could do it with a million nested if condition, but I'm sure there is a better way to do this in Pandas. Can anyone point me in the right direction?

Dataset Image link:

HERE

pandas data-analysis data-manipulation python
2 Answers

You can multiply booleans by strings, and since pandas' sum() function concatenates strings, you can take the dot product of the data and the column values. Doing both, you can do:

df = pd.DataFrame({'NY':[True, True, False],'SF':[True, False, True]})

delimiter = ", "
tmp= df.rename(columns=lambda x: x+delimiter)
df['Cities'] = tmp.dot(tmp.columns).str[:-len(delimiter)]
df


#~        NY     SF  Cities
#~  0   True   True  NY, SF
#~  1   True  False      NY
#~  2  False   True      SF

If only some of your columns are city names, you can list the city columns and just use those:

cities = ['NY','SF']
tmp= df[cities].rename(columns=lambda x: x+delimiter)
user_138106260

IIUC, If your values are indeed True boolean values you can to use np.where or df.dot

import numpy as np

#to keep the peformance of numpy arrays i would suggest doing
#df.columns = df.columns.map(lambda x : f'{x},')
df['places'] = df.dot(df.columns)


     New York     LA  New Orleans               places
ID                                                    
1.0      True  False         True  New YorkNew Orleans
2.0      True  False        False             New York
3.0     False  False        False

or using numpy.

df['places_2'] = np.where(df, df.columns, '').sum(axis=1)

     New York     LA  New Orleans             places_2
ID                                                    
1.0      True  False         True  New YorkNew Orleans
2.0      True  False        False             New York
3.0     False  False        False
user_93751020
Related