Appropriate data structure for deeply nested data?

Question

I can't figure out what would be an appropriate data structure for my problem. I need to write a script that will count some events. Input is a list of events (thousands of them). Each event is an object with following attributes:

class Event
  company
  city
  office
  department

You can look at it like this: „Each event is related to one company in a particular town in one office and in one department of that office“.

My script needs to output the total number of events for company, town office and department. Basically it needs to output sentences like this:

Total events in company X in town Y in office Z in department T : 15
Total events in company X in town Y in office Z in department R: 36
...
Total events in company N in town Y in office G in department T: 89
...

How should I store these counts and how should I count them. I was thinking of using nested dictionaries (in python) or in-memory SQL database. Is there any other way?

Answer 1

If the example sentence is ONLY required output, then it would be good to create a single key from all the sub-keys (company, town, office, department) and then use it as lookup for dictionary, whose value would be count of occurrence of this key.

If there are different aggregate outputs (counts, averages, over different keys) then you are probably looking for some kind of OLAP tool.

Answer 2

If your data is hierarchical (company can exist in multiple towns, each town can have multiple offices, each office can have multiple departments) then I'd use a bunch of nested dictionaries/hash maps. You'll have one at the top level containing all companies, the value will be the dictionary of towns that company has offices in, the values in that will be a dictionary of offices in that town, and the value of that will be a dictionary of departments in that office. The departments dictionary can contain either a simple event count or you can store other information in there (event timestamp, event type/severity/response, whatever).

Answer 3

First approach is to determine the smallest atomic information in your structure would be event that should have foreign keys on Company, Town, Office, Department.

Thus, you may use template to construct sentences from count of events for required combinations.

Second approach might be using XML structure to store and retrieve your data.

Answer 4

Make a table with all companies, a table with all towns and a table with all department types.

Then make a Table with all offices, where town and company are properties. Then make a Table with all departments where office and department type are properties.

Finally Make a Table with all events linked to the according department.