Python Count JSON key values

Question

I have a dataframe df with column 'ColumnA'. How do i count the keys in this column using python.

df = pd.DataFrame({
    'ColA': [{
        "a": 10,
        "b": 5,
        "c": [1, 2, 3],
        "d": 20
    }, {
        "f": 1,
        "b": 3,
        "c": [0],
        "x": 71
    }, {
        "a": 1,
        "m": 99,
        "w": [8, 6],
        "x": 88
    }, {
        "a": 9,
        "m": 99,
        "c": [3],
        "x": 55
    }]
})

Here i want to calculate count for each key like this. Then visualise the frequency using a chart

Expected Answers :

 a=3,
 b=2,
 c=3,
 d=1,
 f=1,
 x=3,
 m=2,
 w=1

[Please don't post images of code/data](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question). Take a look at [how-to-make-good-reproducible-pandas-examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples). — Shubham Sharma, Jul 10 '20 at 15:17

sushanth · Accepted Answer · 2020-07-10T15:47:28.740

1

try this, Series.explode transform's list-like to a row, Series.value_counts to get counts of unique values, Series.plot to create plot out of the series generated.

df.ColA.apply(lambda x : list(x.keys())).explode().value_counts()

a    3
c    3
x    3
b    2
m    2
f    1
d    1
w    1
Name: ColA, dtype: int64

edited Jul 10 '20 at 15:47

answered Jul 10 '20 at 15:34

sushanth

8,275
3
17
28

I does work in the above sample. But I am not able to do it in my actual data set. It gives an error which says "AttributeError: 'str' object has no attribute 'keys'" You can find my dataset here https://docs.google.com/spreadsheets/d/1fRXV0Jcgzi-IVy5oF_oT3kSoZU2vp9XUvdc31qnkzT4/edit#gid=0 i want to calculate the number of mid, passes_char etc – saran p Jul 10 '20 at 16:21
1

It's not a dict instead it is json string you will first need to convert to dict, replace ``list(x.keys())`` >> ``list(json.loads(x).keys())`` (include ``import json``) should fix the issue. – sushanth Jul 10 '20 at 16:33
Awesome that worked. One last question. My column has multiple keys but i need only the result for the key "pass_details". How do I get the total count for each value for the key "pass_details" in my column "ColA". Eg. "pass_details":["attack","defend"] "pass_details":["attack","defend","concede"] expected result : attack =2, defend =2, concede=1 – saran p Jul 10 '20 at 17:20
instead of ``.keys()`` use ``.values()`` & small tweak should give what you need. – sushanth Jul 10 '20 at 17:25
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/217615/discussion-between-saran-p-and-sushanth). – saran p Jul 10 '20 at 17:50

Python Count JSON key values

1 Answers1