我希望通过 IP 地址按升序对特定列"D"的每一行/单元格中的条目进行排序。这些条目存储在新行上,并在 IP 末尾列出了关联的协议和端口,我不关心仅对 IP 地址的 4 个八位字节进行排序。我觉得这需要某种具有某种 lambda 函数的 reg ex。有时可能存在主机名而不是 IP 地址。
示例数据帧为:
ID A B C D
1 x x x 10.0.0.50/TCP/50
192.168.1.90/TCP/51
server1/TCP/80
10.0.0.9/TCP/78
2 y y y 192.168.3.90/UDP/53
10.0.4.10/TCP/65
10.0.3.4/TCP/34
host1/UDP/80
3 z z z 10.0.0.40/TCP/80
10.0.0.41/TCP/443
192.168.2.70/UDP/98
10.0.0.9/TCP/12
所需的输出将是:
ID A B C D
1 x x x 10.0.0.9/TCP/78
10.0.0.50/TCP/50
192.168.1.90/TCP/51
server1/TCP/80
2 y y y 10.0.3.4/TCP/34
10.0.4.10/TCP/65
192.168.3.90/UDP/53
host1/UDP/80
3 z z z 10.0.0.9/TCP/12
10.0.0.40/TCP/34
10.0.0.41/TCP/443
192.168.2.70/UDP/98
为了实现上述数据帧,我最初使用一个 groupby 组合行 D,该行有效,但 IP 地址不按顺序排列:
df = df.groupby(['ID','A','B','C'], sort=False, as_index=False)['D'].apply('n'.join)
如果可能的话,同时组合和排序可能比 2 个单独的命令更有效??
任何想法非常感谢我看了几个例子,但似乎都不适合。希望我的解释足够清楚,提前感谢您的任何帮助。
假设你有原始的DF,在分组之前:
In [70]: df
Out[70]:
ID A B C D
0 1.0 x x x 10.0.0.50/TCP/50
1 1.0 x x x 192.168.1.90/TCP/51
2 1.0 x x x server1/TCP/80
3 1.0 x x x 10.0.0.9/TCP/78
4 2.0 y y y 192.168.3.90/UDP/53
5 2.0 y y y 10.0.4.10/TCP/65
6 2.0 y y y 10.0.3.4/TCP/34
7 2.0 y y y host1/UDP/80
8 3.0 z z z 10.0.0.40/TCP/80
9 3.0 z z z 10.0.0.41/TCP/443
10 3.0 z z z 192.168.2.70/UDP/98
11 3.0 z z z 10.0.0.9/TCP/12
选项 1:多索引 DF:
In [69]: (df.assign(x=df.D.replace(['/.*',r'b(d{1})b',r'b(d{2})b'],
...: ['',r'001',r'01'],
...: regex=True))
...: .sort_values('x')
...: .groupby(['ID','A','B','C'], sort=False, as_index=False)['D']
...: .apply('n'.join)
...: .to_frame('D'))
...:
...:
Out[69]:
D
ID A B C
1.0 x x x 10.0.0.9/TCP/78n10.0.0.50/TCP/50n192.168.1.9...
3.0 z z z 10.0.0.9/TCP/12n10.0.0.40/TCP/80n10.0.0.41/T...
2.0 y y y 10.0.3.4/TCP/34n10.0.4.10/TCP/65n192.168.3.9...
选项 2:常规 DF:
In [75]: (df.assign(x=df.D.replace(['/.*',r'b(d{1})b',r'b(d{2})b'],
...: ['',r'001',r'01'],
...: regex=True))
...: .sort_values('x')
...: .groupby(['ID','A','B','C'], sort=False, as_index=False)['D']
...: .apply('n'.join)
...: .reset_index(name='D'))
...:
...:
Out[75]:
ID A B C D
0 1.0 x x x 10.0.0.9/TCP/78n10.0.0.50/TCP/50n192.168.1.9...
1 3.0 z z z 10.0.0.9/TCP/12n10.0.0.40/TCP/80n10.0.0.41/T...
2 2.0 y y y 10.0.3.4/TCP/34n10.0.4.10/TCP/65n192.168.3.9...
以下内容可能有助于了解其工作原理:
添加具有零填充 IP 八位字节的虚拟列x
:
In [71]: df.assign(x=df.D.replace(['/.*',r'b(d{1})b',r'b(d{2})b'],
...: ['',r'001',r'01'],
...: regex=True))
...:
...:
Out[71]:
ID A B C D x
0 1.0 x x x 10.0.0.50/TCP/50 010.000.000.050
1 1.0 x x x 192.168.1.90/TCP/51 192.168.001.090
2 1.0 x x x server1/TCP/80 server1
3 1.0 x x x 10.0.0.9/TCP/78 010.000.000.009
4 2.0 y y y 192.168.3.90/UDP/53 192.168.003.090
5 2.0 y y y 10.0.4.10/TCP/65 010.000.004.010
6 2.0 y y y 10.0.3.4/TCP/34 010.000.003.004
7 2.0 y y y host1/UDP/80 host1
8 3.0 z z z 10.0.0.40/TCP/80 010.000.000.040
9 3.0 z z z 10.0.0.41/TCP/443 010.000.000.041
10 3.0 z z z 192.168.2.70/UDP/98 192.168.002.070
11 3.0 z z z 10.0.0.9/TCP/12 010.000.000.009
按虚拟列对 DF 进行排序x
:
In [72]: (df.assign(x=df.D.replace(['/.*',r'b(d{1})b',r'b(d{2})b'],
...: ['',r'001',r'01'],
...: regex=True))
...: .sort_values('x'))
...:
...:
Out[72]:
ID A B C D x
3 1.0 x x x 10.0.0.9/TCP/78 010.000.000.009
11 3.0 z z z 10.0.0.9/TCP/12 010.000.000.009
8 3.0 z z z 10.0.0.40/TCP/80 010.000.000.040
9 3.0 z z z 10.0.0.41/TCP/443 010.000.000.041
0 1.0 x x x 10.0.0.50/TCP/50 010.000.000.050
6 2.0 y y y 10.0.3.4/TCP/34 010.000.003.004
5 2.0 y y y 10.0.4.10/TCP/65 010.000.004.010
1 1.0 x x x 192.168.1.90/TCP/51 192.168.001.090
10 3.0 z z z 192.168.2.70/UDP/98 192.168.002.070
4 2.0 y y y 192.168.3.90/UDP/53 192.168.003.090
7 2.0 y y y host1/UDP/80 host1
2 1.0 x x x server1/TCP/80 server1