I'm in over my head with data. I got a .dat file from a simulation with individual vehicle trajectories. I want to find all vehicles that travel a certain path, and calculate average path travel time (based on departure from the first node) each minute or every 15 min. First off, it's a huge network with a whole bunch of uninteresting (to me) nodes and links.

Below are 2 sample vehicles. The first row is summary data of the vehicle, then the next row are the node IDs, then node departure times (relative to start time), link travel time (first entry is time to exit first node), and finally cumulative stop time. However the number of lines for each vehicle changes depending on the number of nodes it passes through (as shown in the second vehicle).

To get the time they depart the node of interest, you would add STime and then the time in the second row, and arrival at the last node can be calculated similarly. I have extremely basic understanding of SAS and access, and my first try to import to SAS just made a file with a single column and a long string in each record for each row.

Does anyone have ideas to get the data into a more manageable format? After that I think I can prune the unwanted entries and maybe get the travel times.

Veh	#	325	Tag=	2	OrigZ=	1964	DestZ=	1963	Class=	3	UstmN=	3893	DownN=	3894	DestN=	3880	STime=	0.1	Total	Travel	Time=	0.94	#	of	Nodes=	5	VehType	2	LOO	1
	3894	3879	3882	3883	3880																									
	0.1	0.27	0.45	0.63	0.94																									
	0.1	0.17	0.18	0.18	0.31																									
	0	0	0	0	0																									

Veh	#	51163	Tag=	2	OrigZ=	2170	DestZ=	1079	Class=	3	UstmN=	3082	DownN=	3083	DestN=	9551	STime=	31.5	Total	Travel	Time=	44.68	#	of	Nodes=	100	VehType	1	LOO	1																				
	3083	2988	2987	3037	3023	3022	2975	2976	2973	2978	2986	13018	14985	13020	2985	12803	12688	13022	13023	7656	7657	8104	8112	13026	14984	13028	8094	7717	13030	13032	7988	7987	13034	13036	7986	7985	7913	13038	13040	7912	10305	13041	13044	10304	9043	9084	13046	14988	13907	9083
	9190	13050	14990	13051	12876	12875	13054	13056	9189	9372	13058	9371	13060	9250	9247	9246	9324	13062	14991	13064	10027	10032	13066	13068	8895	46	8931	8885	8886	8888	12010	8889	8887	8909	8910	8915	8916	9961	9960	9485	9520	9529	9518	9523	9522	9514	9558	13948	9557	9551
	0.3	1.42	2.07	5.15	6.1	6.55	7.54	8.75	9.26	9.52	9.94	10.12	10.54	10.95	11.06	11.49	11.7	11.82	15.15	15.26	15.53	15.71	16.06	16.17	16.74	17.25	17.35	17.88	18.07	19.24	19.44	19.94	20.16	20.46	20.57	20.79	21.04	21.14	22.01	22.12	22.54	22.65	22.87	22.98	23.24	23.46	23.57	23.84	23.95	24.06
	24.52	24.62	24.78	24.89	25	25.5	25.61	26.11	26.23	26.75	26.92	27.39	28.31	28.46	28.63	28.75	29.03	29.14	29.24	29.79	30	30.17	30.28	30.54	30.64	30.81	30.92	31.03	31.16	31.26	31.36	33.75	34.16	34.26	34.57	35.45	36.22	36.51	36.85	37.03	38.25	38.65	39.14	40.65	40.8	42.15	42.52	43.5	44.45	44.68
	0.3	1.12	0.65	3.08	0.95	0.45	0.99	1.21	0.51	0.26	0.42	0.18	0.42	0.41	0.11	0.43	0.21	0.12	3.33	0.11	0.27	0.18	0.35	0.11	0.57	0.51	0.1	0.53	0.19	1.17	0.2	0.5	0.22	0.3	0.11	0.22	0.25	0.1	0.87	0.11	0.42	0.11	0.22	0.11	0.26	0.22	0.11	0.27	0.11	0.11
	0.46	0.1	0.16	0.11	0.11	0.5	0.11	0.5	0.12	0.52	0.17	0.47	0.92	0.15	0.17	0.12	0.28	0.11	0.1	0.55	0.21	0.17	0.11	0.26	0.1	0.17	0.11	0.11	0.13	0.1	0.1	2.39	0.41	0.1	0.31	0.88	0.77	0.29	0.34	0.18	1.22	0.4	0.49	1.51	0.15	1.35	0.37	0.98	0.95	0.23
	0	0	0	2.1	2.1	2.1	2.1	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07
	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	3.07	5.2	5.2	5.2	5.2	5.9	5.9	5.9	6	6	6.77	6.77	6.77	7.96	7.96	8.66	8.66	8.66	9.06	9.06

7/5/2011 1:16:15 PM

Looks like it's time you learned to program... unless you or your advisor wants to hire me at $60/hr.

7/5/2011 1:53:11 PM

That's what I was afraid of. I can write decent VBA in Excel, would you suggest developing proficiency in that (to possibly use in Access) or starting fresh with something new?

7/5/2011 2:06:29 PM

For the most future flexibility and capability, I suggest Python. For this type of task I would also suggest Perl or Matlab or SAS or R or ....

I would use Matlab because that's what I'm most proficient with. Failing that I would use Perl and failing that I would use Python.

Whatever you choose, I recommend unshackling yourself from MS Office. It's a poor science tool.

7/5/2011 2:15:47 PM

go-go gadget matlab

7/5/2011 2:37:19 PM

matlab or PERL would eat this for breakfast.

But if you know VBA and have access give that a shot first.

7/5/2011 3:48:24 PM

sounds like a job for Mathematica (I <3 Mathematica); PM me if interested

7/5/2011 10:51:03 PM

I, too, would recommend Matlab. It's widely used so there are lots of tutorials and help forums out there.

7/6/2011 11:01:00 AM

^^ for free (no $), just an acknowledgement in the paper it goes in.

7/6/2011 1:37:53 PM

sounds like you need to learn SAS.

i hear it's pretty good at this whole statistical analysis thing.

7/6/2011 4:58:34 PM

no, use R

see it's free

7/6/2011 7:15:23 PM

blasphemy.

7/6/2011 8:38:24 PM

conflict of interest

7/6/2011 9:36:44 PM

^ ?

7/9/2011 3:14:52 AM

^Evan works for SAS.

7/9/2011 6:04:05 AM

ohhh

7/9/2011 11:26:59 AM

I would never wish SAS on anyone, but it would be pretty simple to do this in SAS.

[Edited on July 9, 2011 at 1:16 PM. Reason : *]

7/9/2011 1:16:22 PM