CODEWITHSUNDEEP
python-3.xpandas
Britt
Britt

Reputation: 581

Counting occurrence of strings in a dataframe

I have a dataframe that has several columns, including a relevance (rel) column and a cpc (cpc) column. The higher the rel, the more relevant the values in the cpc are. I have written code that counts the occurrence of each value in the cpc column, but what I would like to do is multiply each cpc string by the rel so that i weight the more relevant cpc higher than less relevant cpc. For example, in the first row, the rel is 74, so each string H01L51/5036, H01L51/006, and H01L51/5016 would be counted 74 times instead of just once.

The code I was using to count is:

from collections import Counter
flat_cpcSet = [item for sublist in cpcSet for item in sublist]
result = Counter(flat_cpcSet)

cpcSet was a list of lists. I've since added the list of cpc to the dataframe instead of a seperate list.

The dataframe looks like this:

>df
    appID   rel au  x-num   cpc
0   12552285    74  1719    66561   ['H01L51/5036', 'H01L51/006', 'H01L51/5016']
1   11266356    57  2621    89783   ['C22B7/006', 'B01B1/005', 'C22B3/02', 'C22B3/065', 'C22B7/007', 'C22B11/042', 'C22B11/048', 'C22B59/00', 'Y02P10/214', 'Y02P10/234']
2   14273884    55  2864    69308   ['A46B9/021']
3   12524394    50  2459    60344   ['F02B37/013', 'F01D17/105', 'F01D25/24', 'F01N13/10', 'F02B37/02', 'F02B37/183', 'F02C6/12', 'F02B37/004', 'F02M26/16', 'F05D2270/58', 'Y02T50/671', 'Y02T10/144', 'F05D2230/21']
4   12023698    39  1757    68832   ['F01K23/101', 'Y02E20/16']
5   12421790    36  1635    68488   ['G09G3/3685', 'G09G3/3611', 'G09G3/20', 'G09G2330/021', 'G09G2330/06', 'G09G2370/08']
6   13177981    24  1631    83216   ['C07D209/88', 'A61K31/403', 'C07D209/82', 'A61K31/404', 'A61K31/4045', 'A61K31/437', 'A61K31/4439', 'A61K31/506', 'C07D209/08', 'C07D209/86', 'C07D401/06', 'C07D401/12', 'C07D403/06', 'C07D403/12', 'C07D405/12', 'C07D413/06', 'C07D471/04', 'C07D495/04', 'C07F5/022', 'A61K31/4155', 'A61K31/4188', 'A61K31/4192', 'A61K31/422']
7   13065610    23  2428    71350   ['G06Q50/24', 'G06F19/00']
8   13756098    17  2484    61743   ['F28D20/025', 'F28D20/02', 'F28D20/026', 'F28F2245/06', 'F28F2265/12', 'Y02E60/145', 'F28F2265/14']
9   12823912    6   2865    61269   []

What I would like is a new dataframe that looks like (NB, just an example format and not correct for the above data):

CPC Symbol    Count
H01L51/5036    84
H01L51/006    64
C08F290/062    55
C08F2220/1883    45
C08F220/36    44
C08F220/18    32
H01L2224/48091    26
H01L2924/0002    21

I having been trying to write something along the lines of:

x = 0
while x <= len(df['cpc']):
    y = 0
    while y <= len(df['cpc'][x]):
        # code to multiply the string df['cpc'][x] by the int df['rel'][0]
        y += 1
    x += 1
    # code to count the occurrence of the strings and write a new dataframe

Upvotes: 1

Views: 72

Answers (1)

Yuca
Yuca

Reputation: 6091

You have pretty much everything you need. Just adjust your cpc column and use the counter over it:

df['w_cpc'] =df.cpc*df.rel
flat_data = list(x for l in df.w_cpc for x in l)
d = Counter(flat_data)
df = pd.DataFrame.from_dict(d, orient='index').reset_index()

Upvotes: 2

Related Questions