1

I currently have a list and 1 table with 2 col names

list = [a,b,c]

TableA has colA and colB.

I want to set the value in colB depending on my value in colA.

if list is in col A, set col B to 1. Else, set col B to 0.

I've tried this loop but it's inefficient due to my large dataset. 

for i in TableA['colA']:
    if '' in i :
        TableA['colB'] = '0'
    elif 'list value 1' in i:
        TableA['colB'] = '0'
    elif 'list value 2' in i:
        TableA['colB'] = '0'
    elif 'list value 3' in i:
        TableA['colB'] = '0'
    else:
       TableA['colB'] = '1'

Please help with something more efficient.

Adam Kern
  • 566
  • 4
  • 14
Lauren
  • 23
  • 3
  • Looping over a dataframe is almost never the best option. In this case, I believe the built-in method you're looking for is [isin()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.isin.html) – G. Anderson Feb 24 '20 at 20:10
  • Could you please assit with syntax? I'm very new to Python. Something like, if df[[colA]] in List then colB = 1, else colB=0? – Lauren Feb 24 '20 at 20:13
  • Does this answer your question? [How to check if a value is in the list in selection from pandas data frame?](https://stackoverflow.com/questions/18250298/how-to-check-if-a-value-is-in-the-list-in-selection-from-pandas-data-frame) – G. Anderson Feb 24 '20 at 20:16
  • Basically, `df['colb']=df['cola'].isin(my_list).astype(int)` (don't name variables things like `list`, as that will shadow the built-in and frustrate you later, andthe `astype` will force the bool True, False to 1 and 0) – G. Anderson Feb 24 '20 at 20:18
  • Your comment above is not quite what I am trying to do. I have attempted this which is what I want …. (if df['cola'].isin(List): df['colb'] = '1' else: df['cola'] = '2') …. but I get this error..... (ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().) – Lauren Feb 24 '20 at 21:05

2 Answers2

0

I would probably write if '' == i rather than in i. i is the element of the list, not the entire list, so it would not be "in" the element, it would be equal to the element.

Samantha Garcia
  • 490
  • 6
  • 13
0

Let's break down what you intend to do: 1) Filter by criteria 2) Set values accordingly

For 1), you can create a boolean Series to represent the criteria like @G. Anderson suggested in the comments, like so:

TableA['colA'].isin(value_list)

Side note: for the not isin, add ~ like so: ~TableA['colA'].isin(value_list)

Proceed to 2), default colB to zero:

TableA['colB'] = 0

Then set values to 1 according to criteria:

TableA.loc[TableA['colA'].isin(value_list), 'colB'] = 1

Last note: please don't use variable names that collide with built-in functions like list.

BGHV
  • 92
  • 4