How to find rows matching a dict property if the column contains dict

Question

I have to find rows matching we = 2

import pandas as pd

# Create mock dataframe
df = pd.DataFrame([
    [20, 30, {'ab':1, 'we':2, 'as':3}, 'String1'],
    [21, 31, {'ab':4, 'we':5, 'as':6}, 'String2'],
    [22, 32, {'ab':7, 'we':2, 'as':9}, 'String2'],
], columns=['Col A', 'Col B', 'Col C', 'Col D'])

How can I do this in this case because the Col C contains a dict

Answer 1

We can use the str accesssor to access the value in the dict then use normal comparison to test for the value. In this example, select we and eq(2). The mask (m) can be used to filter the DataFrame to find matches:

m = df['Col C'].str['we'].eq(2)
filtered_df = df[m]

---

If going to assign to filtered_df use copy to avoid a later SettingWithCopyWarning:

filtered_df = df[m].copy()

---

Naturally this can be done in one line without a separate variable:

filtered_df = df[df['Col C'].str['we'].eq(2)].copy()

filtered_df:

   Col A  Col B                        Col C    Col D
0     20     30  {'ab': 1, 'we': 2, 'as': 3}  String1
2     22     32  {'ab': 7, 'we': 2, 'as': 9}  String2