Styjun

I am trying to retrieve a row from a pandas dataframe where the cell value is a list. I have tried isin, but it looks like it is performing OR operation, not AND operation.

>>> import pandas as pd
>>> df = pd.DataFrame([['100', 'RB','stacked'], [['101','102'], 'CC','tagged'], ['102', 'S+C','tagged']],
 columns=['vlan_id', 'mode' , 'tag_mode'],index=['dinesh','vj','mani'])

>>> df
 vlan_id mode tag_mode
dinesh 100 RB stacked
vj [101, 102] CC tagged
mani 102 S+C tagged

>>> df.loc[df['vlan_id'] == '102']; # Fetching string value match
 vlan_id mode tag_mode
mani 102 S+C tagged

>>> df.loc[df['vlan_id'].isin(['100','102'])]; # Fetching if contains either 100 or 102

 vlan_id mode tag_mode
dinesh 100 RB stacked
mani 102 S+C tagged
>>> df.loc[df['vlan_id'] == ['101','102']]; # Fails ? 
Traceback (most recent call last):
 File "<stdin>", line 1, in <module>
 File "C:Python27libsite-packagespandascoreops.py", line 1283, in wrapper
 res = na_op(values, other)
 File "C:Python27libsite-packagespandascoreops.py", line 1143, in na_op
 result = _comp_method_OBJECT_ARRAY(op, x, y)
 File "C:Python27libsite-packagespandascoreops.py", line 1120, in _comp_method_OBJECT_ARRAY
 result = libops.vec_compare(x, y, op)
 File "pandas_libsops.pyx", line 128, in pandas._libs.ops.vec_compare
ValueError: Arrays were different lengths: 3 vs 2

I can get the values to a list and compare it. Instead, Is there any way available where we can check it against a list value using .loc method itself?

To find a list you can iterate over the values of vlan_id and compare each value using np.array_equal:

df.loc[[np.array_equal(x, ['101','102']) for x in df.vlan_id.values]]


 vlan_id mode tag_mode
vj [101, 102] CC tagged

Although, it's advised to avoid using lists as cell values in a dataframe.

DataFrame.loc can use a list of labels or a Boolean array to access rows and columns. The list comprehension above contructs a Boolean array.

I am not sure if this is the best way to do this, or if there is a good way to do this, since as far as I know pandas doesn't really support storing lists in Series. Still:

l = ['101', '102']

df.loc[pd.concat([df['vlan_id'].str[i] == l[i] for i in range(len(l))], axis=1).all(axis=1)]

Output:

 vlan_id mode tag_mode
vj [101, 102] CC tagged

Another workaround would be to transform your vlan_id columns so that it can be queried as a string. You can do that by joining your vlan_id list values into comma-separated strings.

df['proxy'] = df['vlan_id'].apply(lambda x: ','.join(x) if type(x) is list else ','.join([x]) )

l = ','.join(['101', '102'])
print(df.loc[df['proxy'] == l])