如何迭代两个dataframes来比较数据并进行处理？[副本]

Question

我有两个不同的DataFrame:A、B。column事件有相似的数据，我正在使用这些数据来比较这两个DataFrames。我想给Dataframe A一个新列dfa.newContext#。

为此，我需要使用Event列。我想遍历Dataframe A以找到事件的匹配项，并将dfb.context#分配给dfa.newcontext#

我认为循环是最好的方法，因为我有一些条件需要检查。

这可能要求得有点高，但我真的被困住了…我想做这样的事情：

offset = 0
Iterate through dfA:
    extract event
    extract context#
        Iterate through dfB:
            if dfB.event == dfA.event:
                dfA.newContext# = dfB.context#
                offset = dfA.new_context# - dfA.context#
                if dfB.event == "Special":
                    dfA.newContext# = dfA.context# - offset
          

数据帧A

+-------------+---------+------+
|dfA.context# |dfA.event| Name |
+-------------+---------+------+
| 0           | Special | Bob  |
| 2           | Special | Joan |
| 4           |    Bird | Susie|
| 5           | Special | Alice|
| 6           | Special | Tom  |
| 7           | Special | Luis |
| 8           |  Parrot | Jill |
| 9           | Special | Reed |
| 10          | Special | Lucas|
| 11          |   Snake | Kat  |
| 12          | Special | Bill |
| 13          | Special | Leo  |
| 14          | Special | Peter|
| 15          | Special | Mark |
| 16          | Special | Joe  |
| 17          | Special | Lora |
| 18          | Special | Care |
| 19          |Elephant | David|
| 20          | Special | Ann  |
| 21          | Special | Larry|
| 22          |   Skunk | Tony |
+-------------+---------+------+

数据帧B

+-------------+---------+
|dfB.context# |dfB.event|
+-------------+---------+
| 0           | Special |
| 0           | Special |
| 0           | Special |
| 1           | Special |
| 1           | Special |
| 1           | Special |
| 1           | Special |
| 2           |    Bird |
| 2           |    Bird |
| 3           | Special |
| 6           |  Parrot |
| 6           |  Parrot |
| 6           |  Parrot |
| 6           |  Parrot |
| 7           | Special |
| 7           | Special |
| 9           |   Snake |
| 9           |   Snake |
| 9           |   Snake |
| 10          | Special |
| 17          |Elephant |
| 17          |Elephant |
| 17          |Elephant |
| 18          | Special |
| 18          | Special |
| 20          |  Skunk  |
| 20          |  Skunk  |
| 21          | Special |
| 26          | Antelope|
+-------------+---------+

+-------------+---------+------+-------------+
|dfA.context# |dfA.event| Name |dfA.newContext#|
+-------------+---------+------+-------------+
| 0           | Special | Bob  |           0 |
| 2           | Special | Joan |           1 |
| 4           |    Bird | Susie|           2 |
| 5           | Special | Alice|           3 |
| 6           | Special | Tom  |             |
| 7           | Special | Luis |             |
| 8           |  Parrot | Jill |           6 |
| 9           | Special | Reed |           7 |
| 10          | Special | Lucas|             |
| 11          |   Snake | Kat  |           9 |
| 12          | Special | Bill |          10 | 
| 13          | Special | Leo  |             |
| 14          | Special | Peter|             |
| 15          | Special | Mark |             |
| 16          | Special | Joe  |             |
| 17          | Special | Lora |             |
| 18          | Special | Care |             |
| 19          |Elephant | David|          17 |
| 20          | Special | Ann  |          18 |
| 21          | Special | Larry|             |
| 22          |   Skunk | Tony |          20 |
+-------------+---------+------+-------------+

如何同时遍历两个数据流并访问信息？

Answer 1

95%的情况下，您可以使用pandas矢量化方法，并消除循环的需要。在这种情况下，您可以使用pd.merge作为一种简单、干净和高效的替代方法，来替代长循环。

编辑：（答案1）：实际上，您可以使用left_on=dfa.index、right_on='context'进行更高级的合并，并在合并后使用其他清理操作在一行中进行合并，但请参阅下面的更完整的答案，它采用了类似的方法：

df = (pd.merge(dfA, dfB['context'], how='left', left_on=dfA.index, right_on='context')
        .drop_duplicates()
        .dropna(subset=['Name'])
        .drop('context', axis=1)
        .rename({'context_x' : 'context', 'context_y' : 'newContext'}, axis=1).fillna(''))

答案2：在操作两个数据流准备合并之后，您可以将两个数据流合并在一起：

1. dfa-使dfa中的context列等于index列，但在更改之前，将其另存为系列s以备以后使用
2. DFB-删除重复项，重置索引，并将索引的名称更改为NewContext以准备合并。
3. 合并event和context并将newcontext值替换为null的context值。
4. 将context更改为原始数据，使用df['context']=s

s = dfA['context']
dfA['context'] = dfA.index.astype(str)
dfB = dfB.drop_duplicates().reset_index().rename({'index' :'newContext'}, axis=1).astype(str)
df = pd.merge(dfA, dfB, how='left', on=['event', 'context'])
df['newContext'] = df['newContext'].where(df['newContext'].isnull(), df['context']).fillna('')
df['context'] = s
df
Out[9]: 
    context     event   Name newContext
0         0   Special    Bob          0
1         2   Special   Joan          1
2         4      Bird  Susie          2
3         5   Special  Alice          3
4         6   Special    Tom           
5         7   Special   Luis           
6         8    Parrot   Jill          6
7         9   Special   Reed          7
8        10   Special  Lucas           
9        11     Snake    Kat          9
10       12   Special   Bill         10
11       13   Special    Leo           
12       14   Special  Peter           
13       15   Special   Mark           
14       16   Special    Joe           
15       17   Special   Lora           
16       18   Special   Care           
17       19  Elephant  David         17
18       20   Special    Ann         18
19       21   Special  Larry           
20       22     Skunk   Tony         20