基于计数的用户和ip地址

Question

我有过这样的档案

USER_ID,IP_ADDRESS
XXXXXX24,10.12.6.54
XXXXXX24,10.12.6.54
XXXXXX24,10.12.6.54
XXXXXX24,10.12.6.54
XXXXXX24,10.12.6.54
XXXXXX25,10.12.6.55
XXXXXX25,10.12.6.55
XXXXXX25,10.12.6.55
XXXXXX25,10.12.6.55
XXXXXX25,10.12.6.55
XXXXXX21,10.12.6.51
XXXXXX21,10.12.6.51
XXXXXX21,10.12.6.51
XXXXXX21,10.12.6.51

我需要一个基于IP地址的计数输出 像

10.12.6.51 10.12.6.55 10.12.6.54
XXXXXX21      4
XXXXXX25                 4
XXXXXX24                            4

这就是代码，很好，我得到的输出是这样的。我需要更多关于输出的细节

#!/bin/python3.6

import csv
import collections
datafile=open('conn.csv','r')
usefuldata=[]
for line in datafile:
   usefuldata.append(line)
from collections import Counter
outfile1=Counter(usefuldata)
print(outfile1)

最后在Barmer的帮助下，我得到了以下结果

Counter({'XXXXXX24,10.12.6.54\n': 5, 'XXXXXX25,10.12.6.55\n': 5, 'XXXXXX21,10.12.6.51\n': 4, 'XXXXXX24,10.12.6.56\n': 3, 'USER_ID,IP_ADDRESS\n': 1})

Answer 1

#!/bin/python3.6

import csv
import collections
datafile=open('conn.csv','r')
usefuldata=[]
for line in datafile:
   usefuldata.append(line)
from collections import Counter
outfile1=Counter(usefuldata)
#print(outfile1.most_common())
for value,count in outfile1.most_common():
  print(value,count)

我能够通过上面的代码实现我想要的

[root@lhqsb1db2db01 Scripts]# ./conn.py
XXXXXX24,10.12.6.54
 5
XXXXXX25,10.12.6.55
 5
XXXXXX21,10.12.6.51
 4
XXXXXX24,10.12.6.56
 3

Answer 2

您还可以使用pandas和collections.Counter

例如：

import collections

import pandas as pd
from tabulate import tabulate

with open("data_file.csv") as file:
    next(file, None)  # skip the header
    counter = collections.Counter([line.strip() for line in file])

output = collections.defaultdict(dict)
for user_and_ip, ip_to_user_count in counter.items():
    user, ip = user_and_ip.split(",")
    output[ip].update({user: ip_to_user_count})

df = pd.DataFrame(output).fillna("")
print(tabulate(df, headers="keys"))
df.to_csv("user_to_ip.csv")

输出：

          10.12.6.54    10.12.6.55    10.12.6.51
                            
XXXXXX24  5.0
XXXXXX25                5.0
XXXXXX21                              4.0

以及.csv文件：