我有一个表,'State'
并且关联的IP CIDR范围与该状态关联。
表A
--------------------------------------------------
| ID | State | IP_subnet |
--------------------------------------------------
| 1 | CA | 192.168.1.0/24 |
--------------------------------------------------
| 2 | TX | 172.68.7.0/24 |
--------------------------------------------------
| 3 | NY | 61.141.47.0/24 |
--------------------------------------------------
我想遍历下表并将IP
字段与IP_subnet
字段进行匹配。
表B
| ID | IP |
--------------------------------------
| 1 | 61.141.47.1 |
--------------------------------------
| 2 | 192.168.1.48 |
--------------------------------------
| 3 | 172.68.7.124 |
--------------------------------------
| 4 | 40.32.123.212 |
--------------------------------------
下面是我要的结果:(匹配相关State
的IP
)
| ID | IP | State |
--------------------------------------------------
| 1 | 61.141.47.1 | null |
--------------------------------------------------
| 2 | 192.168.1.48 | CA |
--------------------------------------------------
| 3 | 172.68.7.124 | TX |
--------------------------------------------------
| 4 | 40.32.123.212 | NY |
--------------------------------------------------
我知道下面的代码将适用于1值。我如何遍历一列IPs
针对另一列?
from ipaddress import IPv4Address, IPv4Network
IPv4Address('172.68.7.124') in IPv4Network('172.68.7.0/24')
Y
数据= [[1,'CA','192.168.1.0/24'],[2,'TX','172.68.7.0/24'],['juli',14],[3,NY,61.141。 47.0 / 24]]
df = pd.DataFrame(数据,列= ['ID','状态','IP_subnet'])
首先使用2个数据帧为每个IP查找状态,然后根据此字典数据创建新列并加载到原始df中。
我认为可以以更紧凑的方式完成此操作,但仍然可以完成。
import pandas as pd
data = [[1, 'CA', '192.168.1.0/24'], [2, 'TX', '172.68.7.0/24'], [3, 'NY', '61.141.47.0/24']]
df = pd.DataFrame(data, columns=['ID', 'State', 'IP_subnet'])
# replace end of IP
df['IP_subnet'] = df['IP_subnet'].str.replace(r'.0/24', '')
data2 = [[1, '61.141.47.1'], [2, '192.168.1.48'], [3, '172.68.7.124'], [4, '40.32.123.212']]
df2 = pd.DataFrame(data2, columns=['ID', 'IP'])
# match IP with state
data = {}
for index, row in df.iterrows():
ww = df2[df2['IP'].str.contains(row['IP_subnet'])]
data[ww['IP'].values[0]] = row['State']
# create State column
state_data = []
for index, row in df2.iterrows():
if row['IP'] in data:
state_data.append(data.get(row['IP']))
else:
state_data.append('NaN')
df2['State'] = state_data
输出:
ID IP State
0 1 61.141.47.1 NY
1 2 192.168.1.48 CA
2 3 172.68.7.124 TX
3 4 40.32.123.212 NaN
谢谢您的帮助!!