1

I have data in dataframe in the following format: 

df=pd.DataFrame([
    [42,{"tags":["illustration","logo","design","ui"]}],
    [81,{"tags":["typography","icon","vector","ux"]}],
    [98,{"tags":["branding","app"]}],
    [52,{"tags":["animation","web","flat"]}],
    [17,{"tags":["type","lettering"]}],
    [37,{"tags":["illustration","typography","branding","typography","branding"]}],
    [63,{"tags":["logo","icon","app","web","lettering"]}],
    [47,{"tags":["ui","ux"]}],
    [6,{"tags":["design","vector","icon","flat","lettering","branding","app"]}],
    [53,{"tags":["ui","ux","lettering","branding","app","animation","web","flat"]}],
    [64,{"tags":["branding","app","typography","branding"]}],
    [89,{"tags":["typography","branding","ux","lettering","branding"]}]
],columns=["_id","tags"])

I want to count the number of 'id' with specific number of tags (distribution of this number), so for the data above it would be: 

Number of posts    Number of tags 
     3                 2
     1                 3
     3                 4 
     3                 5
     1                 7

How should I handle the text tags in the given format for this task?

Thank you

user40
  • 1,361
  • 5
  • 19
  • 34

1 Answers1

2

Use DataFrame constructor + Counter with list comprehension for count lengths of each tags as lists:

from collections import Counter

c = Counter([len(x['tags']) for x in df['tags']])

df = pd.DataFrame({'Number of posts':list(c.values()), ' Number of tags ': list(c.keys())})
print (df)
   Number of posts   Number of tags 
0                3                 4
1                3                 2
2                1                 3
3                3                 5
4                1                 7
5                1                 8

Or use apply with value_counts:

df = (df['tags'].apply(lambda x: len(x['tags']))
                .value_counts()
                .rename_axis('Number of tags')
                .reset_index(name='Number of posts')
                [['Number of posts','Number of tags']])
print (df)
   Number of posts  Number of tags
0                3               5
1                3               4
2                3               2
3                1               8
4                1               7
5                1               3
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • Thank you. I just noticed that some data may not be in the format I specified. The data may or may not include some other information. Please check here: pastebin.com/Pv4mXN8e Could you please let me know how to change the code in this case? Thanks! @jezrael – user40 Jun 06 '18 at 15:29
  • Sorry, now I am offline, on phone only. So try find solution later. – jezrael Jun 06 '18 at 15:35
  • so I could obtain a dataframe with the tags only (like in the original question). when I run both approaches I get same error: TypeError: string indices must be integers – user40 Jun 07 '18 at 12:47