if row in multiple column contains 1 how to add new column in dataframe containing column names where value is 1

Question

My question is under Locations column how can i add column names where row value is 1 , for example against Japan/US , Newyork,Osaka should be printed under Locations column....Pls advice how to solve this in Python ?

As a new user you should read [ask], and when it comes to Pandas [How to make good reproducible pandas examples](https://stackoverflow.com/q/20109391/3545273). We often need to reproduce and because of that need copyable (text) data. — Serge Ballesta, Feb 16 '21 at 08:32

score 0 · Answer 1 · answered Feb 16 '21 at 08:34

Try this:

import pandas as pd


DF = pd.DataFrame
S = pd.Series


def construct_df() -> DF:
    data = {
        "Countries": ["Japan/US", "Australia & NZ", "America & India"],
        "Portugal": [0, 0, 0],
        "Newyork": [1, 0, 1],
        "Delhi": [0, 0, 0],
        "Osaka": [1, 0, 1],
        "Bangalore": [0, 0, 0],
        "Sydney": [0, 0, 0],
        "Mexico": [0, 0, 0],
    }
    return pd.DataFrame(data)


def calc_locations(x: DF) -> S:
    x__location_cols_only = x.select_dtypes("integer")
    x__ones_as_location_col_name = x__location_cols_only.apply(
        lambda ser: ser.replace({0: "", 1: ser.name})
    )
    location_cols = x__location_cols_only.columns.tolist()
    ret = x__ones_as_location_col_name[location_cols[0]]
    for colname in location_cols[1:]:
        col = x__ones_as_location_col_name[colname]
        ret = ret.str.cat(col, sep=",")
    ret = ret.str.replace(r",+", ",").str.strip(",")
    return ret


df_final = construct_df().assign(Locations=calc_locations)

assert df_final["Locations"].tolist() == ["Newyork,Osaka", "", "Newyork,Osaka"]

Thanks Austin Ray for the solution. – amrev15 Feb 20 '21 at 17:52 — amrev15, Feb 20 '21 at 17:52

Ankit Doshi · Answer 2 · 2021-02-16T14:07:38.523

Lets say your data is like below

import pandas as pd

data = {'Countries':  ['JP/US', 'Aus/NZ', 'America/India'],
         'Portugal': [0, 0, 0],
         'Newyork': [1, 0, 1],
         'Delhi': [0, 0, 1],
         'Osaka': [1, 0, 0],
         'Sydney': [0, 0, 0],
         'Mexico': [0, 0, 0],
        }
data_df = pd.DataFrame(data)

DF looks like (request you to provide above set of data to build a DF for us to provide you results):

       Countries  Delhi  Mexico  Newyork  Osaka  Portugal  Sydney
0          JP/US      0       0        1      1         0       0
1         Aus/NZ      0       0        0      0         0       0
2  America/India      1       0        1      0         0       0

If you execute below statements


data_df = data_df.set_index('Countries')
data_df['Locations'] = data_df.apply(lambda x: ", ".join(x[x!=0].index.tolist()), axis=1)

Your output will look like below

               Delhi  Mexico  Newyork  Osaka  Portugal  Sydney       Locations
Countries                                                                     
JP/US              0       0        1      1         0       0  Newyork, Osaka
Aus/NZ             0       0        0      0         0       0                
America/India      1       0        1      0         0       0  Delhi, Newyork

Thanks Ankit for the solution, I had two more question Q1 : how can we convert the existing data automatically in below mentioned format data = {'Countries': ['JP/US', 'Aus/NZ', 'America/India'], 'Portugal': [0, 0, 0], 'Newyork': [1, 0, 1], 'Delhi': [0, 0, 1], 'Osaka': [1, 0, 0], 'Sydney': [0, 0, 0], 'Mexico': [0, 0, 0], } data_df = pd.DataFrame(data) — amrev15, Feb 17 '21 at 09:53
https://pandas.pydata.org/pandas-docs/dev/reference/api/pandas.DataFrame.to_dict.html Sorry for the late reply. Also if my answer helped you please do Upvote it. Thanks — Ankit Doshi, Sep 12 '21 at 18:45

score 0 · Answer 3 · answered Feb 16 '21 at 09:19

You can do:

data = {
        "Countries": ["Japan/US", "Australia & NZ", "America & India"],
        "Portugal": [0, 0, 0],
        "Newyork": [1, 0, 1],
        "Delhi": [0, 0, 0],
        "Osaka": [1, 0, 1],
        "Bangalore": [0, 0, 0],
        "Sydney": [0, 0, 0],
        "Mexico": [0, 0, 0],
    }
df=pd.DataFrame(data)
df1=df.iloc[:,1:]*df.iloc[:,1:].columns
df['Location']=df1.values.tolist()
df['Location']=df['Location'].apply(lambda x:','.join([y for y in x if len(y)>1]))

if row in multiple column contains 1 how to add new column in dataframe containing column names where value is 1

3 Answers3