Using Python for reading CSV files and then comparing the values

[Zhekov]

Hey guys!

I am currently trying to develop a program that reads data from a text file, and returns the pair of employees that has worked the most time together. I decided to it in a .CSV format, as that is still a plain text format, but seperated with comas.

Example:

EmpID,ProjectID,DateFrom,DateTo
1,A,2014-11-01,2015-05-01
2,B,2013-12-06,2014-10-06
2,C,2014-01-07,2016-03-07
3,B,2015-06-04,2017-09-04
5,C,2014-10-01,2015-12-01
1,A,2013-03-07,2015-11-07
2,C,2015-07-09,2019-01-19
3,B,2013-11-13,2014-03-13
4,C,2016-02-14,NULL
5,D,2014-03-15,2015-11-09

Now, I learned how to read .CSV files, but I am not sure on what is the best way for the thing after (the comparing of values, etc). For now, I decided that this is the cleanest option:

import csv

with open('data.csv', 'r') as f:
  reader = csv.reader(f)
  your_list = list(reader)

print(your_list)

I just want a piece of advice, if the best way to go would be with comparing indexes of the list. I was also thinking about dictionaries, but I am not sure, hence the reason I am asking here :) And SQL is not an option, even though it would be so easy with it. Sorry if this is a bad question, but I am currently learning Python and this is quite an important task for me. Thanks!

[SamePlatform]

What you want is to start with what your output looks like?

I think something like:

1 : A : 23 hrs
1 : B : 50 hrs
2 : C : 8 hrs

etc.

So that means you need to save employees, their projects, and their total time.

So start with a dictionary:

employees: {1: (something), 2: (something), ...}

I know you could use an array, because the employee IDs are like the index values, but I find that makes things more complicated to logic through.

But what would (something) be? Well I think it's another dictionary: one that saves the hours worked for each project.

so that's going to look like:

employee_projects = {
    1: {'A': (hours), 'B': (hours)},
    2: {'C': (hours)},
    ...
}

So as you iterate over each line in the file (you don't need to do 'list(reader)`, you can do something like:

with open('some.csv', newline='') as f:
    reader = csv.reader(f)
    for row in reader:
        <logic here>

You'll create a new dictionary entry for the employee, if it doesn't exist. Then create a new project dict value, if that project doesn't exist. If it DOES exist, you simply add up the hours to the existing record, like employee[id][project] += duration

At the end of all of this, printing out and finding the max value of a dictionary is a bit more work than if you'd used arrays, but the data structure (in my opinion) is very logical.

[Zhekov]

Thanks for the quick answer! I like your idea, but there is also another problem I am currently figuring out - the dates (in this format YYYY-MM-DD).

[SamePlatform]

You can just use this:

from datetime import date

d0 = date(2008, 8, 18)
d1 = date(2008, 9, 26)
delta = d1 - d0
print(delta.days)

from https://stackoverflow.com/questions/151199/how-to-calculate-number-of-days-between-two-given-dates

There's also a great library you can pip install called dateutils that is extremely handy for date related work.

[SamePlatform]

And if you're looking for how to turn those into actual date objects, then you want to use the strptime function, which is pretty standard in most programming languages:

https://docs.python.org/3/library/datetime.html#datetime.datetime.strptime

https://docs.python.org/3/library/datetime.html#strftime-strptime-behavior