python - 使用 ipaddress 库使用 while 循环查找 IP 问题

Question

我有 2 个 pandas 数据框。

• “DF1”是来自日志的流量，包含小时、时间和目标地址，包含大约200 万行。
• “DF2”是 ASN 信息，我在其中获取了起始地址和结束地址，并将其转换为 CIDR 表示法，大约为: 800,000 行。

我试图在“DF2”的 CIDR 字段中查找“DF1”的地址，然后将与 CIDR block 关联的 ASN 编号添加到“DF1”。由于我有如此大的数据集，我很好奇最好的方法是完成此任务？

DF1
    address 
0   8.47.124.1  
1   63.215.97.2 
2   8.47.124.2  
3   63.215.97.1 
4   8.47.124.1
5   8.47.124.2  
6   8.47.124.1  
7   8.47.124.1  

DF2
   ASN   CIDR
0   1    [IPNetwork('8.47.124.0/29')]
1   1    [IPNetwork('8.45.244.0/29')]
2   2    [IPNetwork('63.215.97.8/29')]
3   1    [IPNetwork('8.13.232.64/27')]
4   2    [IPNetwork('63.215.97.16/29')]
5   2    [IPNetwork('63.215.97.24/29')]
6   1    [IPNetwork('8.13.228.128/27')]
7   1    [IPNetwork('8.13.228.96/27')]

期望的输出:

DF1
   address         asn
0   8.47.124.1     1
1   63.215.97.2    2
2   8.47.124.2     1
3   63.215.97.1    2
4   8.47.124.1     1
5   8.47.124.2     1
6   8.47.124.1     1
7   8.47.124.1     1

我越来越近了:

import ipaddress

#Create new column "ASN" to DF1
DF1["ASN"] = ""

#While loop uses the library ipaddress that checks if address from "DF1" is in CIDR block of "DF2"
index = 0  
while(index < lenth):
  DF1["sourceaddress"].iloc[index] in DF2["CIDR"].iloc[index]

  DF1["ASN"].iloc[index] = DF2["ASN"].iloc[index]
  index = index + 1

但这只给了我 1 的 ASN.1 值。我认为当 DF1 的 IP 与 DF2 中的 CIDR 匹配时，它只是给我 DF2 中位置的 ASN，而不是 ASN。

Answer 1

考虑从第一个数字到最后一个句点构建 IP 地址的子字符串，然后合并在一起:

DF1['IP_Sub'] = DF1['address'].apply(lambda x: x[0:x.rindex('.')])

DF2['IP_Sub'] = DF2['CIDR'].apply(lambda x: x[12:x.rindex('.')])
DF2 = DF2[['IP_Sub', 'ASN']].drop_duplicates()

# MERGE DFs
DF3 = pd.merge(DF1, DF2, on='IP_Sub')[['address', 'ASN']]

print(DF3)
#        address  ASN
# 0   8.47.124.1    1
# 1   8.47.124.2    1
# 2   8.47.124.1    1
# 3   8.47.124.2    1
# 4   8.47.124.1    1
# 5   8.47.124.1    1
# 6  63.215.97.2    2
# 7  63.215.97.1    2

# MERGE DFs (MAINTAIN ORIGINAL INDEX)
DF3 = (DF1.reset_index()
          .merge(DF2, on='IP_Sub', sort=False)
          .filter(['index', 'address', 'ASN'])
          .set_index('index').sort_index()
          .rename_axis(None))
print(DF3)
#        address  ASN
# 0   8.47.124.1    1
# 1  63.215.97.2    2
# 2   8.47.124.2    1
# 3  63.215.97.1    2
# 4   8.47.124.1    1
# 5   8.47.124.2    1
# 6   8.47.124.1    1
# 7   8.47.124.1    1