#1
|
|||
|
|||
Looking for an Excel boffin . . . .not related to MechMate . . . completely OFF-TOPIC
Hi All
I am looking for an Excel boffin who knows his macros . . . We have a logger on a garbage truck that logs the gps position and time of every cart/bin pickup. Here is a report from this morning (a small number of those data lines are corrupt - those lines can be ignored) liftlogger That daily CSV file needs to be processed to get: Total bins L (left) Total bins R (right) Total bins (L+R) before an N in the Position column (The N's represent extended driving/parking time when No bins are lifted for 15 minutes.) Total bins in each "session" (beat) between the N's. Time beat starts Time beat ends Duration of beat Tempo of bins/hour during a beat. Average bins per kilometer during a beat Number of stopping points in the beat (a stopping point is when the gps location changes by less than 7 meters) Average number of bins per stopping point for a beat. Typical is 3 beats per day, of about 400 bins each. Etc. Etc. I know Excel well enough to crunch the numbers without using macros, but the process needs serious automation. Soon there will be 14 of these loggers running every day . . . . . Any volunteers? Thanks |
#2
|
|||
|
|||
Gerald,
I work on it for you. If I have some questions, I will send you an email. Nils |
#3
|
|||
|
|||
Quote:
it's not excel but it may be a better approach in the attachment you'll find a few things a batch file to run - eg. "summarize.bat 2008 03 14" a "data" directory where the raw logs go - organized in folders and subfolders by year, month and day hopefully the the awk script and sql is fairly self explanatory the awk script filters out any bad rows, reformats the date and time, adds a beat identifier and the number of seconds since the start of the beat it's imported into an sqlite db a number of sql views are created in the db (see summarize.sql) and should give a reasonable example of how to query for various stats it's not highly polished but is should be good for a start the query results can be piped out to a file if you like sqlite has an odbc driver that will let excel populate its tables from an sqlite database either the comma delimited output or odbc connection can be used in excel where the presentation can be prettied up having the data cleaned up with some useful fields added to each row should make any excel type work easier cheers, robin |
#4
|
|||
|
|||
Hi Robin
You have obviously done a lot of work and I appreciate you taking the time out to look at this. It is going to take me a while to figure out what you said though. . . . . Realise that I am not the person trying to digest the csv file. The end-user will probably only have Excel at their disposal. Nor am I in control of the database that is writing the .csv file. |
#5
|
|||
|
|||
no problem - i've learn a lot from this site and continue to be amazed by the quality of the content here
i have more practice doing this stuff than say welding :-) based on the numbers in your first note, i'm guessing that managing the raw information in excel will quickly become problematic for any one trying to manage it 1,200 rows per log - 16,800 rows per day - 336,000 rows per month - 4,032,000 rows per year keeping the data in raw form can be helpful later when you may want to reanalyze the idea with the "data" dir is to keep the data in raw form but make it easy to zip say a month at a time i was going to play with trying to generate some KML to show a color coded path (color according to say beat or tempo) but perhaps later if you've got more sample data i should be able automate the aggregation of day over day data (ie something better than just dump out a csv table to the console) it's all pretty much self contained everything used is either open source (awk) or public domain (sqlite) and included in the zip: http://gnuwin32.sourceforge.net/packages/gawk.htm http://www.sqlite.org/ The distance calculation was taken from: http://www.movable-type.co.uk/scripts/latlong.html for a bit more background, there is: http://en.wikipedia.org/wiki/Awk http://en.wikipedia.org/wiki/Sqlite |
|
|