从一个具有不同值和类型的列创建新的dataframe列

Question

我试图通过鱼种名称创建新的列，并将整数作为值，保留索引以在之后进行数据帧连接

import pandas as pd
df = pd.read_csv("fishCounts.csv",index_col=0)
countsdf = df[["Fish Count"]].copy()
countsdf.head()
    
Fish Count
0   38 Sand Bass, 16 Sculpin, 10 Blacksmith
1   138 Sculpin, 28 Sand Bass
2   150 Sculpin Released, 102 Sculpin, 40 Sanddab
3   156 Sculpin, 29 Sand Bass, 5 Black Croaker, 3 ...
4   161 Sculpin

countsdf.columns = ["fish"]
countsdf.fish = countsdf.fish.str.split(", ", expand=False)
countsdf.head()

fish
0   [38 Sand Bass, 16 Sculpin, 10 Blacksmith]
1   [138 Sculpin, 28 Sand Bass]
2   [150 Sculpin Released, 102 Sculpin, 40 Sanddab]
3   [156 Sculpin, 29 Sand Bass, 5 Black Croaker, 3...
4   [161 Sculpin]

这就是我不知道该去哪里的地方。遍历数据帧行？列一张字典的清单？ 我是否可以以不同的方式导入数据以简化此操作

编辑：这就是我想说的

  Sand Bass   Sculpin   Blacksmith   Sculpin Released  Sanddab  Black Croaker
0        38        16           10
1        28        138
2                  102                            150       40
3        29        156                                                      5
4                  161

Answer 1

类似于@Manakin的东西

将Fish Count转换为整数列表

df['Fish Count']=df['Fish Count'].str.split(',')

爆炸以使用其id分隔每条鱼

df2=df.explode('Fish Count')

创建字典。在这里，我使用列表理解将Fish Count中的值拆分为数字后的空格，然后派生键和值

{i:j for i,j in df2['Fish Count'].str.split(r'(?<=\d)\s')}

结果

{'38': 'Sand Bass',
 ' 16': 'Sculpin',
 ' 10': 'Blacksmith',
 '138': 'Sculpin',
 ' 28': 'Sand Bass',
 '150': 'Sculpin Released',
 ' 102': 'Sculpin',
 ' 40': 'Sanddab',
 '156': 'Sculpin',
 ' 29': 'Sand Bass',
 ' 5': 'Black Croaker',
 '161': 'Sculpin'}

如果需要，可以打印

print(pd.DataFrame.from_dict({i:j for i,j in df2['Fish Count'].str.split(r'(?<=\d)\s')}, orient='index'))

                     0
38           Sand Bass
 16            Sculpin
 10         Blacksmith
138            Sculpin
 28          Sand Bass
150   Sculpin Released
 102           Sculpin
 40            Sanddab
156            Sculpin
 29          Sand Bass
 5       Black Croaker
161            Sculpin