Mercurial > hg > nsaunier > traffic-intelligence

annotate scripts/extract-user-counts.py @ 1301:7e42f83aab1f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
added utility to print recording durations from metadata
author Nicolas Saunier <nicolas.saunier@polymtl.ca>
date Thu, 10 Jul 2025 16:15:55 -0400
parents c4bef099d0a2
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1299
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
1 #! /usr/bin/env python
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
2
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
3 #import numpy as np
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
4 import argparse # os
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
5 import pandas as pd
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
6 import sqlite3
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
7
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
8 from trafficintelligence import utils, moving
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
9
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
10 parser = argparse.ArgumentParser(description='The program generates a csv of the user counts per SQLite database in the directory ')
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
11 parser.add_argument('-i', dest = 'dirname', help = 'name of the directory containing all the databases', default = '.')
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
12 args = parser.parse_args()
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
13
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
14 out = open('user-counts.csv', 'w')
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
15 out.write('filename')
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
16 for i in range(1,7):
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
17 out.write(','+moving.userTypeNames[i])
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
18 out.write('\n')
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
19 for fn in utils.listfiles(args.dirname, 'sqlite'):
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
20 with sqlite3.connect(fn) as connection:
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
21 data = pd.read_sql('SELECT road_user_type, count(*) AS n FROM objects GROUP BY road_user_type', connection)
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
22 counts = {i:'0' for i in range(1,7)}
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
23 for i,r in data.iterrows():
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
24 counts[r['road_user_type']]=str(r['n'])
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
25 fn2 = fn.split('/')[-1].rsplit('.',1)[0]
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
26 out.write(fn2+','+','.join(counts.values())+'\n')
c4bef099d0a2 adding script to extract users counts
Nicolas Saunier <nicolas.saunier@polymtl.ca>
parents:
diff changeset
27 out.close()