I got an interesting problem:
file1.csv has a few hundred rows like:
Code,DTime
1,2010-12-26 17:01
2,2010-12-26 17:07
2,2010-12-26 17:15
file2.csv has about 11 million rows like:
id,D,Sym,DateTime,Bid,Ask
1375022797,D,USD,2010-12-26 17:00:15,1.311400,1.311700
1375022965,D,USD,2010-12-26 17:00:56,1.311200,1.311500
1375022984,D,USD,2010-12-26 17:00:56,1.311300,1.311600
1375023013,D,USD,2010-12-26 17:01:01,1.311200,1.311500
1375023039,D,USD,2010-12-26 17:01:02,1.311100,1.311400
1375023055,D,USD,2010-12-26 17:01:03,1.311200,1.311500
1375023063,D,USD,2010-12-26 17:01:03,1.311300,1.311600
What i'm trying to do is to write a script that takes each DTime value in file1.csv and finds the first instance of a partial match in the DateTime column of file2.csv, and outputs DateTime, Bid, Ask for that row. The partial match is on the first 16 characters.
Both files are sorted from oldest to newest, so if "2010-12-26 17:01" from file1.csv matched 4 entries in file2.csv, I only need to extract the first one: "2010-12-26 17:01:01"
Not sure how to proceed.. I tried a dictionary but the order of values is important so i'm not sure if that would work. Maybe bring file1's DTime column into a list and for each entry in that list search DateTime in file2?
Thanks guys
If you don't have duplicate DTime
values, this should work:
import csv
file1reader = csv.reader(open("file1.csv"), delimiter=",")
file2reader = csv.reader(open("file2.csv"), delimiter=",")
header1 = file1reader.next() #header
header2 = file2reader.next() #header
for Code, DTime in file1reader:
for id_, D, Sym, DateTime, Bid, Ask in file2reader:
if DateTime.startswith(DTime): # found it
print DateTime, Bid, Ask # output data
break # break and continue where we left next time
Edit
import csv
from datetime import datetime
file1reader = csv.reader(open("file1.csv"), delimiter=",")
file2reader = csv.reader(open("file2.csv"), delimiter=",")
header1 = file1reader.next() #header
header2 = file2reader.next() #header
for Code, DTime in file1reader:
DTime = datetime.strptime(DTime, "%Y-%m-%d %H:%M")
for id_, D, Sym, DateTime, Bid, Ask in file2reader:
DateTime = datetime.strptime(DateTime, "%Y-%m-%d %H:%M:%S")
if DateTime>=DTime: # found it
print DateTime, Bid, Ask # output data
break # break and continue where we left next time
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With