python - 根据另一个 df 和 pandas 的条件在 df 中添加新行

Question

为了根据 2 个数据框信息创建新数据框，我遇到了一些问题。这是一个 dataframe1:

species seq_names   value
dog     seq_C       0.67
cat     seq_F       1.4
cat     seq_E       0.4
dolphin seq_F       0.7
dolphin seq_A       1.9
frog    seq_A       0.8
frog    seq_B       0.40

这是另一个dataframe2:

group_number    col1
1               cat
1               dog
2               dolphin
2               frog
2               seq_X
2               seq_Y

如您所见，有 2 个组。想法是根据它们在 df1 中匹配的物种及其值向这些组添加 seq_names。

在这里，我应该得到一个 new_df 添加了 seq_names:

group_number    sp_seq_names
1               cat
1               dog
1               seq_C
1               seq_F
1               seq_E   
2               dolphin
2               frog
2               seq_A
2               seq_B
2               seq_X
2               seq_Y

针对 IMC 进行编辑:如您所见，seq_X 和 seq_Y 仍然存在于末尾。

group_number 1 获得了 seq_names C、F 和 E，因为所有这些都与组中的至少一个物种匹配。但微妙之处在于:您还可以看到 group1 获得了 seq_name F 而不是 group_number 2尽管 dolphin 也匹配这个序列，但是 cat 比 dolphin 有更好的值(value) seq_name(1.4 对 0.7。)所以 group_number 2 只获得了 2 个 seq_names A 和 B.

有人知道使用 pandas 吗？

我尝试了合并:

pd.merge(df2, df1, left_on=['col1'],right_on=['species'],how='outer')

   group_number     col1  species seq_names  value
0             1      cat      cat     seq_F   1.40
1             1      cat      cat     seq_E   0.40
2             1      dog      dog     seq_C   0.67
3             2  dolphin  dolphin     seq_F   0.70
4             2  dolphin  dolphin     seq_A   1.80
5             2     frog     frog     seq_B   0.40
6             2     frog     frog     seq_A   0.80

然后我创建了所需的 df :

df=[]
for species, group  in  zip (df_new['seq_names'],df_new['group_number']):
 df.append({'groups':group,'sp_seq_names':species})
for species, group  in  zip (df_new['species'],df_new['group_number']):
 df.append({'groups':group,'sp_seq_names':species})

我得到:

>>> pd.DataFrame(df)
       col1  groups
0       cat       1
1       cat       1
2       dog       1
3   dolphin       2
4   dolphin       2
5      frog       2
6      frog       2
7     seq_F       1
8     seq_E       1
9     seq_C       1
10    seq_F       2
11    seq_A       2
12    seq_B       2
13    seq_A       2

但是如您所见，我无法设法在组之间共享 seq_name 并根据值决定哪个组获得此 seq_name。

Answer 1

import pandas as pd

df = pd.read_csv('test')
df2 = pd.read_csv('test.csv')
df2 = df2.rename(columns={'col1' : 'species'})
print(df)
#    species seq_names  value
# 0      dog     seq_C   0.67
# 1      cat     seq_F   1.40
# 2      cat     seq_E   0.40
# 3  dolphin     seq_F   0.70
# 4  dolphin     seq_A   1.90
# 5     frog     seq_A   0.80
# 6     frog     seq_B   0.40

print(df2)
#    group_number  species
# 0             1      cat
# 1             1      dog
# 2             2  dolphin
# 3             2     frog

#   We now don't immediatly drop the duplicates, we want to save the merge before.
#   Doing this, we're able to keep the seq_names associated with their group_number.
ndf = df.merge(df2, on='species')\
        .sort_values(by='value', ascending=False)

#   I make a copy so that I get a whole new DataFrame.
#   If I didn't. Changes made to seq_groups would have affected the original.
seq_groups_df = ndf[['seq_names', 'group_number']].copy()
seq_groups_df = seq_groups_df.rename(columns={'seq_names' : 'sp_seq_names'})
print(seq_groups_df)
#   seq_names  group_number
# 4     seq_A             2
# 1     seq_F             1
# 5     seq_A             2
# 3     seq_F             2
# 0     seq_C             1
# 2     seq_E             1
# 6     seq_B             2

ndf = ndf.drop_duplicates(subset='seq_names', keep='first')

#   Either select the interesting columns.
ndf = ndf[['group_number', 'species']]
ndf = ndf.rename(columns={'species' : 'sp_seq_names'})

print(ndf)
#    group_number sp_seq_names
# 4             2      dolphin
# 1             1          cat
# 0             1          dog
# 2             1          cat
# 6             2         frog


result_df = ndf.append(seq_groups_df).reset_index(drop=True)
print(result_df)
#     group_number sp_seq_names
# 0              2      dolphin
# 1              1          cat
# 2              1          dog
# 3              1          cat
# 4              2         frog
# 5              2        seq_A
# 6              1        seq_F
# 7              2        seq_A
# 8              2        seq_F
# 9              1        seq_C
# 10             1        seq_E
# 11             2        seq_B