This is my first question so please forgive any mistakes.
I have a large file(csv) with several(~10000000+) lines of information like the following example:
date;box_id;box_length;box_width;box_height;weight;type
--snip--
1999-01-01 00:00:20;nx1124;10;4;5.5;2.1;oversea
1999-01-01 00:00:20;np11r4;8;3.25;2;4.666;local
--snip--
My objective is to read through each line and calculate the box's volume and within 1 hour window(for example, 00:00:00 - 00:00:59) I have to record if 2 or more boxes are of similar volume (+-10% difference) and then record their timestamp as well as type.
At the moment, I am using a brute-force approach:
- run through each line
- calculate volume
- go to next line and compute volume
- compare
- repeat till 1 hr time-difference is detected
- remove the first box from list
- add another box to the list
- repeat the process with second box
For example, if my 1 hour window has 1,2,3,4, I'm doing this
1
2 == 1
3 == 1 then == 2
4 == 1 then == 2 then == 3
5 == 2 then == 3 then == 4 # removed 1 from list(1hr window moved down)
6 == 2 then == 3 then == 4 then == 5
7 == 2 then == 3 then == 4 then == 5 then == 6
.... so on ....
This is the best I can think of since I have to compare each and every box with others within a given time-window. But this is very very slow at the moment.
I am looking for a better algorithm but I am unsure as to which direction I must go. I am trying to learn some excellent tools(so far Pandas is my favorite) but I am under the assumption that I need to implement some algorithm first to allow these tools to deal with the data in the way I need to.
If it helps I will post my python code(source).
Update Following are my code. I have omitted several lines(such as try/catch block for invalid file path/format, type conversion error handling etc). I have customized the code a bit to work for 5second window.
Following is the Box class
from datetime import datetime
from time import mktime
class Box(object):
""" Box model """
def __init__(self,data_set):
self.date = data_set[0]
self.timestamp = self.__get_time()
self.id = data_set[1]
self.length = float(data_set[2])
self.width = float(data_set[3])
self.height = float(data_set[4])
self.weight = int(data_set[5])
self.volume = self.__get_volume()
def __get_time(self):
""" convert from date string to unix-timestamp """
str_format = '%Y-%m-%d %H:%M:%S'
t_tuple = datetime.strptime(self.date, str_format).timetuple()
return mktime(t_tuple)
def __get_volume(self):
""" calculate volume of the box """
return (self.length * self.width * self.height)
Following is the actual program performing the comparison. I combined by utility file and main.py file together for convenience.
from csv import reader
from io import open as open_file
from os import path
from sys import argv, exit
from time import time
# custom lib
from Box import Box
def main():
file_name = str.strip(argv[1])
boxes_5s = []
diff = 0
similar_boxes = []
for row in get_file(file_name):
if row:
box = Box(row)
if len(boxes_5s) > 0:
diff = box.timestamp - boxes_5s[0].timestamp
if diff < 6:
boxes_5s.append(box)
else:
similar_boxes += get_similar(boxes_5s)
del boxes_5s[0] # remove the oldest box
boxes_5s.append(box)
else:
boxes_5s.append(box)
print(similar_boxes)
def get_file(file_name):
""" open and return csv file pointer line by line """
with open_file(file_name,'rb') as f:
header = f.readline()
print(header)
rows = reader(f, delimiter=';')
for r in rows:
yield r
else:
yield ''
def get_similar(box_list):
""" compare boxes for similar volume """
num_boxes = len(box_list)
similar_boxes = []
record_str = "Box#{} Volm:{} and #{} Volm:{}"
for i in xrange(num_boxes):
box_1 = box_list[i]
for j in xrange(i+1, num_boxes):
box_2 = box_list[j]
vol_diff = abs((box_1.volume - box_2.volume)/box_1.volume) <= 0.1
if vol_diff: similar_boxes.append(record_str.format(box_1.id,box_1.volume,box_2.id, box_2.volume))
return similar_boxes
if __name__ == "__main__":
main()
Thank you.
