CODEWITHSUNDEEP
pythonpandasdataframe
Edward
Edward

Reputation: 4623

Count the number of dict elements in Pandas column containing a list of dictionaries

I have a dataframe like this

df['likes']
0    {'data': [{'id': '651703178310339', 'name': 'A...
1    {'data': [{'id': '798659570200808', 'name': 'B...
2    {'data': [{'id': '10200132902001105', 'name': ...
3    {'data': [{'id': '10151983313320836', 'name': ...
4                                                  NaN
5    {'data': [{'id': '1551927888235503', 'name': '...
6    {'data': [{'id': '10204089171847031', 'name': ...
7    {'data': [{'id': '399992547089295', 'name': 'В...
8    {'data': [{'id': '10201813292573808', 'name': ...
9                                                  NaN

Some cells have several elements 'id' 

df['likes'][0]
{'data': [{'id': '651703178310339', 'name': 'A'},
  {'id': '10204089171847031', 'name': 'B'}],
 'paging': {'cursors': {'after': 'MTAyMDQwODkxNzE4NDcwMzEZD',
   'before': 'NjUxNzAzMTc4MzEwMzM5'}}}

Some cells have zero. I want to get a new variable 

df['number']
0 2
1 4
2 3
4 0

That contains number of elements 'id'. df['likes'] was obtained from dict. I tried to count 'id'

df['likes'].apply(lambda x: x.count('id'))
AttributeError: 'dict' object has no attribute 'count'

So I tried like this

df['likes'].apply(lambda x: len(x.keys()))
AttributeError: 'float' object has no attribute 'keys'

How to fix it?

I was asked to publish a full set of data, I publish three lines so as not to take up much space

`df['likes']`
`0 {'data': [{'id': '651703178310339', 'name': 'A'},
  {'id': '10204089171847031', 'name': 'B'}],
 'paging': {'cursors': {'after': 'MTAyMDQwODkxNzE4NDcwMzEZD',
   'before': 'NjUxNzAzMTc4MzEwMzM5'}}}
1 {'data': [{'id': '798659570200808', 'name': 'C'},
  {'id': '574668895969867', 'name': 'D'},
  {'id': '651703178310339', 'name': 'A'},
  {'id': '1365088683555195', 'name': 'G'}],
 'paging': {'cursors': {'after': 'MTM2NTA4ODY4MzU1NTE5NQZDZD',
   'before': 'Nzk4NjU5NTcwMjAwODA4'}}}
2 NaN`

Upvotes: 2

Views: 2254

Answers (2)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210852

Option 1:

In [120]: df.likes.apply(pd.Series)['data'].apply(lambda x: pd.Series(x).notnull()).sum(1)
Out[120]:
0    2.0
1    4.0
2    0.0
dtype: float64

Option 2:

In [146]: df['count'] = [sum('id' in d for d in x.get('data',[]))
                         if pd.notna(x) else 0
                         for x in df['likes']]

In [147]: df
Out[147]:
                                               likes  count
0  {'data': [{'id': '651703178310339', 'name': 'A...      2
1  {'data': [{'id': '798659570200808', 'name': 'C...      4
2                                                NaN      0

Data set:

In [137]: df.to_dict('r')
Out[137]:
[{'likes': {'data': [{'id': '651703178310339', 'name': 'A'},
    {'id': '10204089171847031', 'name': 'B'}],
   'paging': {'cursors': {'after': 'MTAyMDQwODkxNzE4NDcwMzEZD',
     'before': 'NjUxNzAzMTc4MzEwMzM5'}}}},
 {'likes': {'data': [{'id': '798659570200808', 'name': 'C'},
    {'id': '574668895969867', 'name': 'D'},
    {'id': '651703178310339', 'name': 'A'},
    {'id': '1365088683555195', 'name': 'G'}],
   'paging': {'cursors': {'after': 'MTM2NTA4ODY4MzU1NTE5NQZDZD',
     'before': 'Nzk4NjU5NTcwMjAwODA4'}}}},
 {'likes': nan}]

Upvotes: 1

John Zwinck
John Zwinck

Reputation: 249223

This almost works:

df['likes'].apply(lambda x: len(x['data']))

Note the error:

> AttributeError: 'float' object has no attribute 'keys'

That happens because you have some NaN values (which are represented as float NAN). So:

df['likes'][df['likes'].notnull()].apply(lambda x: len(x['data']))

Upvotes: 1

Related Questions