我有一个存储为 17GB csv 文件 (fileData) 的大型数据集,其中包含每个 customer_id 的可变数量的记录(最多约 30,000 条)。我正在尝试搜索特定客户(在 fileSelection 中列出 - 总共 90000 个中大约有 1500 个)并将每个客户的记录复制到单独的 csv 文件中(fileOutput).
我是 Python 的新手,但使用它是因为 vba 和 matlab(我更熟悉)无法处理文件大小。 (我正在使用 Aptana studio 编写代码,但直接从 cmd 行运行 python 以提高速度。运行 64 位 Windows 7。)
我写的代码提取了一些客户,但是有两个问题:1)无法在大型数据集中找到大多数客户。 (我相信它们都在数据集中,但不能完全确定。)2)它非常慢。任何加速代码的方法都将受到赞赏,包括可以更好地利用 16 核 PC 的代码。
代码如下:
`def main():
# Initialisation :
# - identify columns in slection file
#
fS = open (fileSelection,"r")
if fS.mode == "r":
header = fS.readline()
selheaderlist = header.split(",")
custkey = selheaderlist.index('CUSTOMER_KEY')
#
# Identify columns in dataset file
fileData = path2+file_data
fD = open (fileData,"r")
if fD.mode == "r":
header = fD.readline()
dataheaderlist = header.split(",")
custID = dataheaderlist.index('CUSTOMER_ID')
fD.close()
# For each customer in the selection file
customercount=1
for sr in fS:
# Find customer key and locate it in customer ID field in dataset
selrecord = sr.split(",")
requiredcustomer = selrecord[custkey]
#Look for required customer in dataset
found = 0
fD = open (fileData,"r")
if fD.mode == "r":
while found == 0:
dr = fD.readline()
if not dr: break
datrecord = dr.split(",")
if datrecord[custID] == requiredcustomer:
found = 1
# Open outputfile
fileOutput= path3+file_out_root + str(requiredcustomer)+ ".csv"
fO=open(fileOutput,"w+")
fO.write(str(header))
#copy all records for required customer number
while datrecord[custID] == requiredcustomer:
fO.write(str(dr))
dr = fD.readline()
datrecord = dr.split(",")
#Close Output file
fO.close()
if found == 1:
print ("Customer Count "+str(customercount)+ " Customer ID"+str(requiredcustomer)+" copied. ")
customercount = customercount+1
else:
print("Customer ID"+str(requiredcustomer)+" not found in dataset")
fL.write (str(requiredcustomer)+","+"NOT FOUND")
fD.close()
fS.close()
`
提取了几百个客户花了几天时间,但未能找到更多客户。
Sample Output
感谢@Paul Cornelius。这样效率更高。我采用了您的方法,还使用了@Bernardo 建议的 csv 处理:
# Import Modules
import csv
def main():
# Initialisation :
fileSelection = path1+file_selection
fileData = path2+file_data
# Step through selection file and create dictionary with required ID's as keys, and empty objects
with open(fileSelection,'rb') as csvfile:
selected_IDs = csv.reader(csvfile)
ID_dict = {}
for row in selected_IDs:
ID_dict.update({row[1]:[]})
# step through data file: for selected customer ID's, append records to dictionary objects
with open(fileData,'rb') as csvfile:
dataset = csv.reader(csvfile)
for row in dataset:
if row[0] in ID_dict:
ID_dict[row[0]].extend([row[1]+','+row[4]])
# write all dictionary objects to csv files
for row in ID_dict.keys():
fileOutput = path3+file_out_root+row+'.csv'
with open(fileOutput,'wb') as csvfile:
output = csv.writer(csvfile, delimiter='\n')
output.writerows([ID_dict[row]])
改用 csv 阅读器。 Python 有一个很好的库来处理 CSV 文件,因此您不需要进行拆分。
查看文档:https://docs.python.org/2/library/csv.html
>>> import csv
>>> with open('eggs.csv', 'rb') as csvfile:
... spamreader = csv.reader(csvfile, delimiter=' ', quotechar='|')
... for row in spamreader:
... print ', '.join(row)
Spam, Spam, Spam, Spam, Spam, Baked Beans
Spam, Lovely Spam, Wonderful Spam
它应该表现得更好。
我是一名优秀的程序员,十分优秀!