MiniMe
MiniMe

Reputation: 1285

How do I test if a string is in a cell of a pandas data frame, cell that contains a list of strings?

So assume that my data would look like this

   A        B
0   2   ['a','b','c']
1   3   ['a','b','d']
2   4   ['a','b','e']

I know for a fact that in my data frame only one line will contain the number 2 and the letter 'c' (this is just an example, in reality I have larger strings)

I have tried this

df.loc[(df['A']==2) & ('c' in df['B'])]

but it returns just empty results

What do I have to do to get the first line in response to my query ?

Edit: the problem seems to be with the data but I can't figure out what it is Here is what my data looks like

`
0      [Eth11/1, Eth11/2, Eth12/1, Eth12/2]
1                                [Eth13/10]
2                                [Eth13/11]
3                      [Eth11/11, Eth12/11]
4                      [Eth11/21, Eth12/21]
                       ...                 
508                                [Eth9/8]
509                               [Eth10/5]
510                      [Eth10/7, Eth10/9]
511        [Eth7/5, Eth7/6, Eth9/5, Eth9/6]
512                        [Eth7/7, Eth9/7]
Name: B, Length: 513, dtype: object

df('B').str.contains('Eth11/1') returns

0     NaN
1     NaN
2     NaN
3     NaN
4     NaN
       ..
508   NaN
509   NaN
510   NaN
511   NaN
512   NaN
Name: phys_iface, Length: 513, dtype: float64
`

df['B'].dtypes 
array([list(['Eth11/1', 'Eth11/2', 'Eth12/1', 'Eth12/2']),
       list(['Eth13/10']), list(['Eth13/11']),
       list(['Eth11/11', 'Eth12/11']), list(['Eth11/21', 'Eth12/21']),
       list(['Eth13/14']), list(['Eth16/23', 'Eth16/24']),
       list(['Eth16/13']), list(['Eth16/14']), list(['Eth16/15']),
       list(['Eth16/16']), list(['Eth16/17']), list(['Eth16/18']),
       list(['Eth16/19']), list(['Eth16/20']),
       list(['Eth11/5', 'Eth12/19']), list(['Eth11/19', 'Eth16/21']),
       list(['Eth11/24']), list(['Eth13/2']), list(['Eth16/22']),
       list(['Eth13/15', 'Eth13/16']), list(['Eth13/38', 'Eth13/39']),
       list(['Eth11/6']), list(['Eth11/17']),
       list(['Eth11/7', 'Eth12/7']), list(['Eth11/8', 'Eth12/8']),
       list(['Eth11/12', 'Eth12/12']), list(['Eth11/10', 'Eth12/10']),
       list(['Eth11/13', 'Eth12/13']), list(['Eth11/20', 'Eth12/20']),
       list(['Eth11/16', 'Eth12/16']), list(['Eth11/15', 'Eth12/15']),
       list(['Eth11/3', 'Eth11/4', 'Eth12/3', 'Eth12/4']),
       list(['Eth14/6', 'Eth14/7']), list(['Eth12/5', 'Eth12/18']),
       list(['Eth14/4', 'Eth14/5']), list(['Eth12/6', 'Eth12/14']),
       list(['Eth14/8', 'Eth14/9']), list(['Eth14/1', 'Eth14/2']),
       list(['Eth13/8', 'Eth13/9']), list(['Eth14/3', 'Eth14/10']),
       list(['Eth11/23', 'Eth12/17']),
       list(['Eth15/1', 'Eth15/2', 'Eth15/3', 'Eth15/4']),
       list(['Eth11/1', 'Eth11/2', 'Eth12/1', 'Eth12/2']),
       list(['Eth13/10']), list(['Eth13/11']),
       list(['Eth11/11', 'Eth12/11']), list(['Eth11/21', 'Eth12/21']),
       list(['Eth13/14']), list(['Eth16/23', 'Eth16/24']),
       list(['Eth16/13']), list(['Eth16/14']), list(['Eth16/15']),
       list(['Eth16/16']), list(['Eth16/17']), list(['Eth16/18']),
       list(['Eth16/19']), list(['Eth16/20']),
       list(['Eth11/5', 'Eth12/19']), list(['Eth11/19', 'Eth16/21']),
       list(['Eth11/24']), list(['Eth13/2']), list(['Eth16/22']),
       list(['Eth13/15', 'Eth13/16']), list(['Eth13/38', 'Eth13/39']),
       list(['Eth11/6']), list(['Eth11/17']),
       list(['Eth11/7', 'Eth12/7']), list(['Eth11/8', 'Eth12/8']),
       list(['Eth11/12', 'Eth12/12']), list(['Eth11/10', 'Eth12/10']),
       list(['Eth11/13', 'Eth12/13']), list(['Eth11/20', 'Eth12/20']),
       list(['Eth11/16', 'Eth12/16']), list(['Eth11/15', 'Eth12/15']),
       list(['Eth11/3', 'Eth11/4', 'Eth12/3', 'Eth12/4']),
       list(['Eth14/6', 'Eth14/7']), list(['Eth12/5', 'Eth12/18']),
       list(['Eth14/4', 'Eth14/5']), list(['Eth12/6', 'Eth12/14']),
       list(['Eth14/8', 'Eth14/9']), list(['Eth14/1', 'Eth14/2']),
       list(['Eth13/8', 'Eth13/9']), list(['Eth14/3', 'Eth14/10']),
       list(['Eth11/23', 'Eth12/17']),
       list(['Eth15/1', 'Eth15/2', 'Eth15/3', 'Eth15/4']), list([]),
       list(['Eth12/23', 'Eth12/24', 'Eth16/2', 'Eth16/6']), list([]),
       list([]), list(['Eth13/25']), list(['Eth4/22']),
       list(['Eth13/18']), list(['Eth13/19']), list(['Eth4/23']),
       list(['Eth16/1']), list(['Eth4/42', 'Eth4/43']), list([]),
       list([]), list(['Eth16/7']), list(['Eth16/8']), list(['Eth16/5']),
       list(['Eth4/37', 'Eth4/38']), list(['Eth4/39', 'Eth4/40']),
       list(['Eth4/26']), list(['Eth16/3']), list(['Eth4/30']),
       list(['Eth16/4']), list(['Eth4/44']), list(['Eth4/45']),
       list(['Eth4/46']), list(['Eth4/47']), list(['Eth4/5', 'Eth4/6']),
       list(['Eth4/11', 'Eth13/29']), list(['Eth4/12', 'Eth13/30']),
       list(['Eth4/19', 'Eth13/45']), list(['Eth4/13', 'Eth13/31']),
       list(['Eth4/14', 'Eth13/32']), list(['Eth4/20', 'Eth13/46']),
       list(['Eth4/21', 'Eth4/29']), list(['Eth4/3', 'Eth4/25']),
       list(['Eth4/2', 'Eth4/24']), list(['Eth4/15', 'Eth13/47']),
       list(['Eth4/16', 'Eth13/48']), list(['Eth4/1', 'Eth4/17']),
       list(['Eth4/18', 'Eth13/43']), list(['Eth4/28', 'Eth13/44']),
       list(['Eth4/7', 'Eth4/8']), list(['Eth4/9', 'Eth4/10']),
       list(['Eth13/27', 'Eth13/28']), list(['Eth14/11', 'Eth15/11']),
       list(['Eth4/33', 'Eth4/34']), list(['Eth4/36', 'Eth4/41']),
       list(['Eth14/12', 'Eth15/12']),
       list(['Eth15/7', 'Eth15/8', 'Eth15/9', 'Eth15/10']),
       list(['Eth12/23', 'Eth12/24', 'Eth16/2', 'Eth16/6']), list([]),
       list([]), list(['Eth13/25']), list(['Eth4/22']),
       list(['Eth13/18']), list(['Eth13/19']), list(['Eth4/23']),
       list(['Eth16/1']), list(['Eth4/42', 'Eth4/43']), list([]),
       list([]), list(['Eth16/7']), list(['Eth16/8']), list(['Eth16/5']),
       list(['Eth4/37', 'Eth4/38']), list(['Eth4/39', 'Eth4/40']),
       list(['Eth4/26']), list(['Eth16/3']), list(['Eth4/30']),
       list(['Eth16/4']), list(['Eth4/44']), list(['Eth4/45']),
       list(['Eth4/46']), list(['Eth4/47']), list(['Eth4/4', 'Eth4/6']),
       list(['Eth4/11', 'Eth13/29']), list(['Eth4/12', 'Eth13/30']),
       list(['Eth4/19', 'Eth13/45']), list(['Eth4/13', 'Eth13/31']),
       list(['Eth4/14', 'Eth13/32']), list(['Eth4/20', 'Eth13/46']),
       list(['Eth4/21', 'Eth4/29']), list(['Eth4/3', 'Eth4/25']),
       list(['Eth4/2', 'Eth4/24']), list(['Eth4/15', 'Eth13/47']),
       list(['Eth4/16', 'Eth13/48']), list(['Eth4/1', 'Eth4/17']),
       list(['Eth4/18', 'Eth13/43']), list(['Eth4/28', 'Eth13/44']),
       list(['Eth4/7', 'Eth4/8']), list(['Eth4/9', 'Eth4/10']),
       list(['Eth13/27', 'Eth13/28']), list(['Eth14/11', 'Eth15/11']),
       list(['Eth4/33', 'Eth4/34']), list(['Eth4/36', 'Eth4/41']),
       list(['Eth14/12', 'Eth15/12']),
       list(['Eth15/7', 'Eth15/8', 'Eth15/9', 'Eth15/10']),
       list(['Eth2/1', 'Eth3/1']), list(['Eth4/14', 'Eth4/16']),
       list(['Eth2/23']), list(['Eth3/24']), list(['Eth4/2']),
       list(['Eth4/4']), list(['Eth4/18']), list(['Eth4/20']),
       list(['Eth4/22']), list(['Eth4/24']), list(['Eth1/47']),
       list(['Eth4/10']), list(['Eth4/12']), list(['Eth2/17', 'Eth3/18']),
       list(['Eth2/19', 'Eth3/20']), list(['Eth1/17', 'Eth1/18']),
       list(['Eth1/19', 'Eth1/20']), list(['Eth1/36', 'Eth1/37']),
       list(['Eth2/1', 'Eth3/1']), list(['Eth4/14', 'Eth4/16']),
       list(['Eth3/24']), list(['Eth2/21']), list(['Eth4/2']),
       list(['Eth4/4']), list(['Eth4/18']), list(['Eth4/20']),
       list(['Eth4/22']), list(['Eth4/24']), list(['Eth1/47']),
       list(['Eth4/10']), list(['Eth4/12']), list(['Eth2/17', 'Eth3/18']),
       list(['Eth2/19', 'Eth3/20']), list(['Eth1/17', 'Eth1/18']),
       list(['Eth1/19', 'Eth1/20']), list(['Eth1/36', 'Eth1/37']),
       list(['Eth2/2', 'Eth3/2']), list([]), list([]), list(['Eth1/48']),
       list(['Eth8/1']), list(['Eth3/21', 'Eth3/23']),
       list(['Eth2/18', 'Eth3/17']), list(['Eth2/20', 'Eth3/19']),
       list(['Eth2/2', 'Eth3/2']), list([]), list([]), list(['Eth1/48']),
       list(['Eth8/1']), list(['Eth3/21', 'Eth3/23']),
       list(['Eth2/18', 'Eth3/17']), list(['Eth2/20', 'Eth3/19']),
       list(['Eth1/2', 'Eth2/2']),
       list(['Eth1/3', 'Eth1/4', 'Eth2/3', 'Eth2/4']),
       list(['Eth1/5', 'Eth1/6', 'Eth2/5', 'Eth2/6']),
       list(['Eth1/11', 'Eth2/11']), list(['Eth1/12', 'Eth2/12']),
       list(['Eth1/2', 'Eth2/2']),
       list(['Eth1/3', 'Eth1/4', 'Eth2/3', 'Eth2/4']),
       list(['Eth1/5', 'Eth1/6', 'Eth2/5', 'Eth2/6']),
       list(['Eth1/11', 'Eth2/11']), list(['Eth1/12', 'Eth2/12']),
       list(['Eth1/2', 'Eth2/2']),
       list(['Eth1/3', 'Eth1/4', 'Eth2/3', 'Eth2/4']),
       list(['Eth1/5', 'Eth1/6', 'Eth2/5', 'Eth2/6']),
       list(['Eth1/11', 'Eth2/11']), list(['Eth1/12', 'Eth2/12']),
       list(['Eth1/2', 'Eth2/2']),
       list(['Eth1/3', 'Eth1/4', 'Eth2/3', 'Eth2/4']),
       list(['Eth1/5', 'Eth1/6', 'Eth2/5', 'Eth2/6']),
       list(['Eth1/11', 'Eth2/11']), list(['Eth1/12', 'Eth2/12']),
       list(['Eth1/53', 'Eth1/54']), list(['Eth1/17', 'Eth1/18']),
       list(['Eth1/3', 'Eth1/4']), list(['Eth1/31', 'Eth1/32']),
       list(['Eth1/42', 'Eth1/43']), list(['Eth1/33', 'Eth1/34']),
       list(['Eth1/9', 'Eth1/10']), list(['Eth1/5', 'Eth1/6']),
       list(['Eth1/23', 'Eth1/24']), list(['Eth1/53', 'Eth1/54']),
       list(['Eth1/17', 'Eth1/18']), list(['Eth1/3', 'Eth1/4']),
       list(['Eth1/31', 'Eth1/32']), list(['Eth1/42', 'Eth1/43']),
       list(['Eth1/33', 'Eth1/34']), list(['Eth1/9', 'Eth1/10']),
       list(['Eth1/5', 'Eth1/6']), list(['Eth1/23', 'Eth1/24']),
       list(['Eth2/25', 'Eth3/25']),
       list(['Eth1/49', 'Eth1/50', 'Eth1/51']),
       list(['Eth2/23', 'Eth3/23']), list(['Eth2/24', 'Eth3/24']),
       list(['Eth2/21', 'Eth3/21']), list(['Eth2/22', 'Eth3/22']),
       list(['Eth1/1']), list(['Eth1/3']), list(['Eth1/5']),
       list(['Eth1/7']), list(['Eth1/25']), list(['Eth1/27']),
       list(['Eth1/29']), list(['Eth1/31']), list(['Eth1/33']),
       list(['Eth1/35']), list(['Eth1/37']), list(['Eth1/39']),
       list(['Eth2/1']), list(['Eth2/3']), list(['Eth2/5']),
       list(['Eth2/7']), list(['Eth2/9']), list(['Eth2/11']),
       list(['Eth2/13']), list(['Eth2/15']), list(['Eth3/1']),
       list(['Eth3/3']), list(['Eth3/5']), list(['Eth3/9']),
       list(['Eth3/11']), list(['Eth2/25', 'Eth3/25']),
       list(['Eth1/49', 'Eth1/50']), list(['Eth2/23', 'Eth3/23']),
       list(['Eth2/24', 'Eth3/24']), list(['Eth2/21', 'Eth3/21']),
       list(['Eth2/22', 'Eth3/22']), list(['Eth1/1']), list(['Eth1/3']),
       list(['Eth1/5']), list(['Eth1/7']), list(['Eth1/25']),
       list(['Eth1/27']), list(['Eth1/29']), list(['Eth1/31']),
       list(['Eth1/33']), list(['Eth1/35']), list(['Eth1/37']),
       list(['Eth1/39']), list(['Eth2/1']), list(['Eth2/3']),
       list(['Eth2/5']), list(['Eth2/7']), list(['Eth2/9']),
       list(['Eth2/11']), list(['Eth2/13']), list(['Eth2/15']),
       list(['Eth3/1']), list(['Eth3/3']), list(['Eth3/5']),
       list(['Eth3/9']), list(['Eth3/11']),
       list(['Eth1/107', 'Eth1/108']), list(['Eth1/94', 'Eth1/95']),
       list(['Eth1/105', 'Eth1/106']), list(['Eth1/107', 'Eth1/108']),
       list(['Eth1/94', 'Eth1/95']), list(['Eth1/105', 'Eth1/106']),
       list(['Eth1/107', 'Eth1/108']), list(['Eth1/94', 'Eth1/95']),
       list(['Eth1/105', 'Eth1/106']), list(['Eth1/107', 'Eth1/108']),
       list(['Eth1/94', 'Eth1/95']), list(['Eth1/105', 'Eth1/106']),
       list(['Eth1/107', 'Eth1/108']), list(['Eth1/94', 'Eth1/95']),
       list(['Eth1/105', 'Eth1/106']), list(['Eth1/107', 'Eth1/108']),
       list(['Eth1/94', 'Eth1/95']), list(['Eth1/105', 'Eth1/106']),
       list(['Eth1/107', 'Eth1/108']), list(['Eth1/94', 'Eth1/95']),
       list(['Eth1/105', 'Eth1/106']), list(['Eth1/1']), list(['Eth1/2']),
       list(['Eth1/3']), list(['Eth1/4']), list(['Eth1/5']),
       list(['Eth1/6']), list(['Eth1/7']), list(['Eth1/8']),
       list(['Eth1/9']), list(['Eth1/10']), list(['Eth1/11']),
       list(['Eth1/12']), list(['Eth1/23']), list(['Eth1/25']),
       list(['Eth1/26']), list(['Eth1/27']), list(['Eth1/28']),
       list(['Eth1/29']), list(['Eth1/30']), list(['Eth1/31']),
       list(['Eth1/32']), list(['Eth1/33']), list(['Eth1/34']),
       list(['Eth1/35']), list(['Eth1/36']),
       list(['Eth1/107', 'Eth1/108']), list(['Eth1/94', 'Eth1/95']),
       list(['Eth1/105', 'Eth1/106']), list(['Eth1/1']), list(['Eth1/2']),
       list(['Eth1/3']), list(['Eth1/4']), list(['Eth1/5']),
       list(['Eth1/6']), list(['Eth1/7']), list(['Eth1/8']),
       list(['Eth1/9']), list(['Eth1/10']), list(['Eth1/11']),
       list(['Eth1/12']), list(['Eth1/23']), list(['Eth1/25']),
       list(['Eth1/26']), list(['Eth1/27']), list(['Eth1/28']),
       list(['Eth1/29']), list(['Eth1/30']), list(['Eth1/31']),
       list(['Eth1/32']), list(['Eth1/33']), list(['Eth1/34']),
       list(['Eth1/35']), list(['Eth1/36']), list(['Eth8/9', 'Eth8/11']),
       list(['Eth9/19', 'Eth9/24']), list(['Eth7/45']), list(['Eth7/46']),
       list(['Eth7/27', 'Eth7/47']), list(['Eth7/36', 'Eth7/48']),
       list(['Eth8/18']), list(['Eth8/19']), list(['Eth8/20']),
       list(['Eth8/21']), list(['Eth9/21']), list(['Eth8/10']),
       list(['Eth7/31', 'Eth7/43']), list(['Eth7/34', 'Eth7/35']),
       list(['Eth7/32', 'Eth7/38']), list(['Eth7/33', 'Eth7/44']),
       list(['Eth8/14', 'Eth8/15']), list(['Eth7/28', 'Eth7/40']),
       list(['Eth7/29', 'Eth7/41']), list(['Eth8/16', 'Eth8/17']),
       list(['Eth8/22', 'Eth8/23']), list(['Eth8/12', 'Eth8/13']),
       list(['Eth1/1', 'Eth1/2', 'Eth1/3', 'Eth1/4']),
       list(['Eth8/9', 'Eth8/11']), list(['Eth9/19', 'Eth9/24']),
       list(['Eth7/45']), list(['Eth7/46']), list(['Eth7/27', 'Eth7/47']),
       list(['Eth7/36', 'Eth7/48']), list(['Eth8/18']), list(['Eth8/19']),
       list(['Eth8/20']), list(['Eth8/21']), list(['Eth9/21']),
       list(['Eth8/10']), list(['Eth7/31', 'Eth7/43']),
       list(['Eth7/34', 'Eth7/35']), list(['Eth7/32', 'Eth7/38']),
       list(['Eth7/33', 'Eth7/44']), list(['Eth8/14', 'Eth8/15']),
       list(['Eth7/28', 'Eth7/40']), list(['Eth7/29', 'Eth7/41']),
       list(['Eth8/16', 'Eth8/17']), list(['Eth8/22', 'Eth8/23']),
       list(['Eth8/12', 'Eth8/13']),
       list(['Eth1/1', 'Eth1/2', 'Eth1/3', 'Eth1/4']),
       list(['Eth8/1', 'Eth8/2']), list([]), list([]), list(['Eth7/2']),
       list(['Eth9/2']), list(['Eth7/5', 'Eth7/15']),
       list(['Eth7/8', 'Eth7/9']), list(['Eth7/6', 'Eth7/12']),
       list(['Eth7/7', 'Eth7/16']), list(['Eth7/21', 'Eth7/22']),
       list(['Eth7/10', 'Eth7/19']), list(['Eth8/3', 'Eth8/4']),
       list(['Eth7/13', 'Eth7/20']), list(['Eth8/5', 'Eth8/6']),
       list(['Eth7/18', 'Eth7/24']), list(['Eth7/11', 'Eth7/23']),
       list(['Eth1/7', 'Eth1/8', 'Eth1/9', 'Eth1/10']), list(['Eth7/17']),
       list(['Eth8/1', 'Eth8/2']), list([]), list([]), list(['Eth7/2']),
       list(['Eth9/1', 'Eth9/2']), list(['Eth7/5', 'Eth7/15']),
       list(['Eth7/8', 'Eth7/9']), list(['Eth7/6', 'Eth7/12']),
       list(['Eth7/7', 'Eth7/16']), list(['Eth7/21', 'Eth7/22']),
       list(['Eth7/10', 'Eth7/19']), list(['Eth8/3', 'Eth8/4']),
       list(['Eth7/13', 'Eth7/20']), list(['Eth8/5', 'Eth8/6']),
       list(['Eth7/18', 'Eth7/24']), list(['Eth7/11', 'Eth7/23']),
       list(['Eth1/7', 'Eth1/8', 'Eth1/9', 'Eth1/10']), list(['Eth7/17']),
       list(['Eth7/4', 'Eth9/4']), list(['Eth10/3', 'Eth10/19']),
       list([]), list([]), list(['Eth7/8']), list(['Eth10/11']),
       list(['Eth9/8']), list(['Eth10/5']), list(['Eth10/7', 'Eth10/9']),
       list(['Eth7/5', 'Eth7/6', 'Eth9/5', 'Eth9/6']),
       list(['Eth7/7', 'Eth9/7']), list(['Eth7/4', 'Eth9/4']),
       list(['Eth10/3', 'Eth10/19']), list([]), list([]),
       list(['Eth7/8']), list(['Eth10/11']), list(['Eth9/8']),
       list(['Eth10/5']), list(['Eth10/7', 'Eth10/9']),
       list(['Eth7/5', 'Eth7/6', 'Eth9/5', 'Eth9/6']),
       list(['Eth7/7', 'Eth9/7'])], dtype=object)

Upvotes: 0

Views: 63

Answers (3)

MiniMe
MiniMe

Reputation: 1285

the answer to my question was close

df.loc[(df['A']==2) & ('c' in df['B'].to_string())]

The issues was that Python would store my arrays in an object even after concatenating the strings of the array in a strin using " ".join() applied to column B

Upvotes: 1

Manualmsdos
Manualmsdos

Reputation: 1547

You can split it with .apply(pd.Series), for example:

df['B'].apply(pd.Series)
    0   1   2
0   a   b   c
1   a   b   d
2   a   b   e

Answer:

df.loc[(df['A']==2) & (df['B'].apply(pd.Series)[2] == 'c')]
    A   B
0   2   [a, b, c]

Upvotes: 1

moys
moys

Reputation: 8033

IICU, you can use this

df.loc[(df['A']==2) & (df['B'].str.contains('c'))]

OR this

df.loc[(df['A']==2) & (df.apply(lambda x: 'c' in x['B'], axis=1))]

Output

    A   B
0   2   ['a','b','c']

Upvotes: 1

Related Questions