对于同一行，基于另一列计算字符串在列中出现的次数?

Question

数据框(对象)中的数据类型如下:

id                 :int64
id_contains        :object
categories         :object
category contents  :object
dtype: object

当前的数据是这样的，按id和类别内容组织:

id  id_contains  categories       category contents
---------------------------------------------------------
1   a,b,c         cat1            a,b,c
2   d,c,a         cat2            c
3   c,b,e         cat3            e,f,a
4   d,e,f         cat4            a,c

我需要计算在id_contains中出现cat1的次数，并为每个类别创建单独的列，并为每个id创建相应的计数。因此，结果输出数据框应该如下所示:

id  id_contains  categories  category_contents   cat1   cat2    cat3  cat4
---------------------------------------------------------------------------
1   a,b,c           cat1           a,b,c           3      1      1       2
2   d,c,a           cat2               c           2      1      1       2
3   c,b,e           cat3            e,f,a          2      1      0       1
4   d,e,f           cat4              a,c          0      0      2       0

也就是对于每个id，我可以看到它包含了多少类别1，类别2，类别3等等的元素。我是熊猫和数据框架的新手，很抱歉我不能分享实际数据。我已经尝试描述原始数据集，以及尽可能与虚拟数据基于这个社区以前的建议。期待大家的建议!

Answer 1

import pandas as pd
data = [ { "id": 1, "id_contains": "a,b,c", "categories": "cat1", "category_contents": "a,b,c" }, { "id": 2, "id_contains": "d,c,a", "categories": "cat2", "category_contents": "c" }, { "id": 3, "id_contains": "c,b,e", "categories": "cat3", "category_contents": "e,f,a" }, { "id": 4, "id_contains": "d,e,f", "categories": "cat4", "category_contents": "a,c" } ]
df = pd.DataFrame(data).set_index('id')
df['id_contains'] = df['id_contains'].str.split(',') #create list
df['category_contents'] = df['category_contents'].str.split(',') #create list
df['counts'] = df['id_contains'].apply(lambda x: [len(set(x) & set(i)) for i in df['category_contents'].tolist()]) #count occurrences for each category
df[['cat1','cat2','cat3','cat4']] = pd.DataFrame(df['counts'].tolist(), index= df.index) #turn list into columns
df.drop('counts', axis=1, inplace = True) #drop temporary counts column

结果:

|   id | id_contains     | categories   | category_contents   |   cat1 |   cat2 |   cat3 |   cat4 |
|-----:|:----------------|:-------------|:--------------------|-------:|-------:|-------:|-------:|
|    1 | ['a', 'b', 'c'] | cat1         | ['a', 'b', 'c']     |      3 |      1 |      1 |      2 |
|    2 | ['d', 'c', 'a'] | cat2         | ['c']               |      2 |      1 |      1 |      2 |
|    3 | ['c', 'b', 'e'] | cat3         | ['e', 'f', 'a']     |      2 |      1 |      1 |      1 |
|    4 | ['d', 'e', 'f'] | cat4         | ['a', 'c']          |      0 |      0 |      2 |      0 |