Dataset manipulation

[Boboxx]

Hello,

I'm just a newb trying to write a small timing system for our local cycling club. I have a record set that looks like this:

Plate,Time
2,00:09:30.430
12,00:09:44.782
19,00:10:05.764
48,00:10:40.490
50,00:10:42.970
2,00:18:56.867
12,00:19:17.303
19,00:20:17.519
48,00:20:56.598
17,00:21:13.960
50,00:21:26.284
2,00:28:47.375
12,00:29:13.193
19,00:30:41.599
48,00:31:25.871
50,00:31:53.515
2,00:38:32.719
12,00:38:59.130
57,00:40:03.855
34,00:40:57.862
19,00:41:09.796
9,00:42:00.044
48,00:42:06.050
50,00:42:08.593
2,00:48:26.238
12,00:48:34.475

Data is entered with plate number and the time it took them to do a lap... so far so good

Here is what I'm having a hard time with. I need to be able to sort out each riders lap time and position like this:

2,00:09:30.430, 00:18:56.867, 00:28:47.375, 00:38:32.719, 00:48:26.238
12,00:09:44.782, 00:19:17.303, 00:29:13.193 00:38:59.130, 00:48:34.475
19,00:10:05.764, 00:20:17.519, 00:30:41.599, 00:41:09.796
15,00:10:40.490, 00:20:56.598, 00:31:25.871, 00:42:06.050
50,00:10:42.970, 00:21:26.284, 00:31:53.515

Since all riders don't have the same amount of laps I can't sort on the last time record and I need to sort on larger amount of lap first and then sort on time.

It's not in a DB and just in a csv text file and a BufDataset now... Should I move it to ZMSql? or any other  libs? (I was trying to build something nativ with Lazarus)

I'm a bit lost and I'm not sure how to store or sort my results... any pointer would be apreciated.

Thank you

[molly]

At first sight (and with current information available), i would suggest using a stringlist.

Assuming the list is sorted first lap to last lap (but, if not we can make that work as well), you can simply load the data into a stringlist with loadfromfile(), then you can 'travel' the items in the stringlist one by one, extract the plate part, if it matches the plate that you wish to 'collect' then print it or add to another stringlist for easy saving (or add it to a listbox etc.)

Repeat the above process for each individual plate number. If no match on platenumber left, then you're done. In case platenumbers are not in consecutive order, then extract those first (again travel the list, extract platenumberpart) and add them to yet another stringlist (or a dynamic array) and use that to match inside the original stringlist with all the data that you originally loaded.

Sounds perhaps complicated, but really isn't.

[Boboxx]

That was kind of my first aproch, the problem I ran in was that I could not sort the string list with the numbers...

I would get:
1
10
13
2
21
22

and I had the same problem with my times

[molly]

That is because a stringlist assumes you are sorting strings, and as we all know, number sorts a bit awkward when being a string.

You can use customsort for that if you really want to.

Note that in my approach there would would be no need to sort the stringlist at all. There is perhaps a moment in my described process where you wish to present the data in a sorted way but, for processing the data it is not really necessary (remember, you are in control of traveling the list from top to bottom, or vice verse).

edit: typos

[Boboxx]

Ok so if I go with a string list.. Would I do somethig like this:

//String list#1 to hold the list of unique plate number so I can loop trough
StringList1 = [2, 19, 7, 18, 16, 32]

//String list#2 a 2d array to hold the list of each riders time
StringList1 = [[00:09:30.430,00:18:56.867],[00:09:44.782],[00:10:05.764],[00:10:40.490],[00:10:42.970],[00:10:44.016]]

Or would there be a easier way to manage the 2 list?

[Fungus]

It might seem more cumbersome, but I would create a record for each rider and store it in a TList. This would give much more flexibility to access and sort the data. Consider the following data structure

Code: Pascal  [Select][+][-]

1. Type
2.  
3.   PLapTime = ^TDateTime; //Or use a Cardinal and store the amount of milliseconds per lap
4.  
5.   PRiderData = ^TRiderData;
6.   TRiderData = Record
7.     RiderID: Cardinal; //The plate number of the driver
8.     LapTimes: TList; //A list of PLapTime, one for each lap
9.   End;

[molly]

For your 'original' list:
- just load it with .loadfromfile()
- remove the header (if there is any), with .delete()
- use an index running from 0 to .count-1
- for each individual line, extract the platenumber (extractword() comes to mind)
- add the freshly extracted platenumber to another stringlist to form a list of unique platenumbers (set the sorted property to true, and autoremove duplicates. If wanting to sort this list on number, use that lists' customsort methos).
- then from the second stringlsit you just filled, use an index from 0 to .count-1 to get the platenumber and travel your way down that list
- with that platenumber, search your original list from top to bottom, extracting each line item, extract the platenumber and compare it
- if they match, add the time to a list of times for the current platenumber (you/we haven't discussed yet, where they should go ?, you can use yet another stringlist for that if wanted)
- keep matching until your done traveling the platenumber list.
- done.

So you original list would look like:

Code: [Select]

2,00:09:30.430
12,00:09:44.782
19,00:10:05.764
48,00:10:40.490
50,00:10:42.970
2,00:18:56.867
...etc

Your platenumber list would look like:

Code: [Select]

2
7
16
18
19
32
etc.

And whatever we choose to 'hold' the times for a platenumber will be in whatever was chosen to hold that data.

Note that sorting the original table can perhaps be easier. Use the custom sort and sort on plate + time. If wanted you can still create a unique list of platenumbers in order to speed up extracting times for each unique platenumber (but it isn't necessary as you can travel donw your sorted list and see when the platenumber changed).

Much information, and sounds complicated. Just start with the simplest things first and report in case you get stuck.

[Boboxx]

If I want to create a recordset for each rider, how would I store them? Can I store them in a string list?

I like that aproche beter as I can add all the extra valeues that I was also hoping to add:

type
  PLapTime = ^TDateTime; //Or use a Cardinal and store the amount of milliseconds per lap

  PRiderData = ^TRiderData;
  TRiderData = Record
    RiderID: Cardinal; //The plate number of the driver
    RiderFirstName: String;
    RiderlastName: String;
    RiderCategory:  Intger;
    RiderClub: String;
    LapTimes: TList; //A list of PLapTime, one for each lap
  End;

[Fungus]

You must store the records in a TList, like this:

Code: Pascal  [Select][+][-]

1. Var PRD: PRiderData;
2.     PLT: PLapTime;
3.     Riders: TList;
4.     I, II: Integer;
5. Begin
6.  
7.   //Create i TList for the records
8.   Riders:= TList.Create;
9.  
10.   //Create a record
11.   New(PRD);
12.  
13.   //Set the values of the record
14.   PRD^.RiderID:= 2;
15.   PRD^.RiderName:= 'Some Name';
16.   //Set the rest of the values...
17.  
18.   //Create a list of lap times for the rider
19.   PRD^.LapTimes:= TList.Create;
20.   //Add two lap times
21.   New(PLT);
22.   PLT^:= EncodeTime(1, 2, 3, 456);
23.   PRD^.LapTimes.Add(PLT);
24.   New(PLT);
25.   PLT^:= EncodeTime(1, 3,23, 465);
26.   PRD^.LapTimes.Add(PLT);
27.  
28.   //Add the rider data to list of riders
29.   Riders.Add(PRD);
30.  
31.   //Release it all again
32.   For I:= 0 To Riders.Count - 1 Do Begin
33.     PRD:= Riders[I];
34.     For II:= 0 To PRD^.LapTimes.Count - 1 Do Dispose(PLapTime(PRD^.LapTimes[II]));
35.     PRD^.LapTimes.Free;
36.     Dispose(PRD);
37.   End;
38.   Riders.Free;
39. End;

That is how it is done, now you can create the routines for loading your data into the list

[molly]

@Boboxx,
Ok, first of all, you neglected to mention you got other fields as well.  That is why i said, at first sight (as i already had a suspicion).

Secondly, if your only goal is to get a list of platenumbers with their times then i believe (for such relative small number of 'database-fields') using a stringlist is still a better/quicker/easier to understand solution (even though you might perhaps have a few questions about the code below).

Thirdly, the code as shown was made to compile with Free Pascal, e.g. in case you would like to integrate this inside a lazarus form for a quick spin on Windows (only Windows), then you would have to disable the gui application checkbox in your projects settings, otherwise you will get an error on the writeln's. Alternatively replace those writeln's with adding lines to a memo component. Easiest is just to compile as a normal free pascal program.

@Fungus:
Yes, seeing that TS now claims to have additional fields it is much easier to use something like records. However, you conveniently left out the sorting parts ;-) I would rather suggest using a proper database. Perhaps a in memory database ? (mind you i am terrible with databases, let alone teach someone else).

The code bits:

Code: Pascal  [Select][+][-]

1. program AnaData;
2.  
3. {$MODE OBJFPC}{$H+}
4.  
5. uses
6.   Classes, StrUtils, DateUtils, SysUtils;
7.  
8. var
9.   DataList   : TStringList;
10.   PlateTimes : TStringList;
11.  
12.  
13. // Sort List on index (automatically comes platenumber and then time provided
14. // tha the list stored on disk is listed in order of time.
15. function  SortListOnIndex(List: TStringList; ListIndex1: Integer; ListIndex2: Integer):Integer;
16. var
17.   Index1, Index2: Integer;
18. begin
19.   Index1 := StrToInt(ExtractWord(1, List[ListIndex1], [',']));
20.   Index2 := StrToInt(ExtractWord(1, List[ListIndex2], [',']));
21.   result := Index1 - Index2;
22. end;
23.  
24.  
25. // sort list on plate, then time (and if those fail !? , then index)
26. function  SortListOnPlate(List: TStringList; ListIndex1: Integer; ListIndex2: Integer):Integer;
27. var
28.   Index1, Index2: Integer;
29.   Plate1, Plate2 : Integer;
30.   Time1 , Time2  : TDateTime;
31.   DTSettings     : TFormatSettings;
32. begin
33.   Plate1 := StrToInt(ExtractWord(2, List[ListIndex1], [',']));
34.   Plate2 := StrToInt(ExtractWord(2, List[ListIndex2], [',']));
35.   result := Plate1 - Plate2;
36.   // platenumbers are the same, differentiate with time
37.   if (result = 0) then
38.   begin
39.     DTSettings.TimeSeparator    := ':';
40.     DTSettings.DecimalSeparator :='.';
41.     DTSettings.ShortTimeFormat  := 'hh:nn:ss.zzz';
42.  
43.     Time1 := StrToTime(ExtractWord(3, List[ListIndex1], [',']), DTSettings);
44.     Time2 := StrToTime(ExtractWord(3, List[ListIndex2], [',']), DTSettings);
45.     Result := CompareDateTime(Time1, Time2);
46.     // should not be happening but just in case differentiate on index
47.     if (Result = 0) then
48.     begin
49.       Index1 := StrToInt(ExtractWord(1, List[ListIndex1], [',']));
50.       Index2 := StrToInt(ExtractWord(1, List[ListIndex2], [',']));
51.       result := Index1 - Index2;
52.     end;
53.   end;
54. end;
55.  
56.  
57. function  SortListOnTime(List: TStringList; ListIndex1: Integer; ListIndex2: Integer):Integer;
58. var
59.   Index1, Index2 : Integer;
60.   Time1 , Time2  : TDateTime;
61.   DTSettings     : TFormatSettings;
62. begin
63.   DTSettings.TimeSeparator    := ':';
64.   DTSettings.DecimalSeparator :='.';
65.   DTSettings.ShortTimeFormat  := 'hh:nn:ss.zzz';
66.  
67.   Time1 := StrToTime(ExtractWord(3, List[ListIndex1], [',']), DTSettings);
68.   Time2 := StrToTime(ExtractWord(3, List[ListIndex2], [',']), DTSettings);
69.   Result := CompareDateTime(Time1, Time2);
70.   // in case times are same, sort on first entry measured/listed.
71.   if (Result = 0) then
72.   begin
73.     Index1 := StrToInt(ExtractWord(1, List[ListIndex1], [',']));
74.     Index2 := StrToInt(ExtractWord(1, List[ListIndex2], [',']));
75.     result := Index1 - Index2;
76.   end;
77. end;
78.  
79.  
80. procedure DumpList(List: TStringList);
81. var
82.   i     : Integer;
83.   Index : String;
84.   Plate : String;
85.   Time  : String;
86.   LineS : String;
87. begin
88.   WriteLn('==================');
89.   for i := 0 to Pred(List.Count) do
90.   begin
91.     LineS := List[i];
92.     Index := ExtractWord(1, LineS, [',']);
93.     Plate := ExtractWord(2, LineS, [',']);
94.     Time  := ExtractWord(3, LineS, [',']);
95.     WriteLn('item[',i:2,']', ' index = ', Index:3,'  Plate = ', Plate:3, '  Time = ', Time);
96.   end;
97.   WriteLn('==================');  
98. end;
99.  
100.  
101. procedure DumpPlateTimes(List: TStringList);
102. var
103.   i : Integer;
104. begin
105.   WriteLn('==================');
106.   for i := 0 to Pred(List.Count)
107.     do WriteLn('item[', i:2,'] = ', List[i]);
108.   WriteLn('==================');
109. end;
110.  
111.  
112. procedure ExtractPlateTimes(SortedList: TStringList; PlateTimeList: TStringList);
113. var
114.   SortedIndex  : Integer;
115.   PlateNr      : Integer;
116.   CurrentPlate : Integer;
117.   TimeAsStr    : String;
118.   PlateTimes   : String;
119. begin
120.   CurrentPlate := -1;
121.   PlateTimes   := '';
122.  
123.   for SortedIndex := 0 to Pred(SortedList.Count) do
124.   begin
125.     PlateNr := StrToInt(ExtractWord(2, SortedList[SortedIndex], [',']));
126.  
127.     // account for the very first entry;
128.     if CurrentPlate = -1 then CurrentPlate := PlateNr;
129.  
130.     // Platenumbers differs from previous so add data to platelist
131.     // and re-init some variables
132.     if (CurrentPlate <> PlateNr) then
133.     begin
134.       PlateTimeList.Add(IntToStr(CurrentPlate) + ',' + PlateTimes);
135.       PlateTimes := '';
136.       CurrentPlate := PlateNr;
137.     end;
138.  
139.     TimeAsStr  := ExtractWord(3, SortedList[SortedIndex], [',']);
140.     // account for (not) first entry
141.     if (PlateTimes <> '') then PlateTimes := PlateTimes + ',';
142.     // Add next time for current active plate
143.     PlateTimes := PlateTimes + TimeAsStr;
144.   end;
145.   // i initially forgot to these two lines below
146.   // we stil could have a last entry which was not written
147.   if (PlateTimes <> '') then PlateTimeList.Add(IntToStr(CurrentPlate) + ',' + PlateTimes);
148. end;
149.  
150.  
151. procedure AnalyzeData(List: TStringList);
152. var
153.   i : Integer;
154. begin
155.   // 'insert' index number, just because we can
156.   for i := 0 to Pred(List.Count)
157.     do List[i] := IntToStr(i) + ',' + List[i];
158.  
159.   // Dump the list as is
160.   DumpList(List);
161.  
162.   // sort the list on Platenumber
163.   List.CustomSort(@SortListOnPlate);
164.   DumpList(List);  
165.  
166.   // sort the list on times
167.   List.CustomSort(@SortListOnTime);
168.   DumpList(List);  
169.  
170.   // dump plate times into another stringlist
171.   List.CustomSort(@SortListOnPlate);    // Make sure to sort on plate + time
172.   ExtractPlateTimes(List, PlateTimes);  // Store individual plates + times into another stringlist
173.   DumpPlateTimes(PlateTimes);           // print the list
174. end;
175.  
176.  
177. begin
178.   PlateTimes := TStringList.Create;
179.  
180.   DataList := TStringList.Create;
181.   DataList.LoadFromFile('Data.txt');
182.  
183.   // remove the "Plate,Time" header
184.   if (DataList.Count > 0) then DataList.Delete(0);
185.  
186.   AnalyzeData(DataList);
187.  
188.   DataList.Free;
189.  
190.   PlateTimes.Free;
191. end.
192.  

And yes, there is some duplication in there and there are some things that coould be optimzed. Not wanted to make it too complicated.

Which produces the following output for me:

Code: [Select]

==================
item[ 0] index =   0  Plate =   2  Time = 00:09:30.430
item[ 1] index =   1  Plate =  12  Time = 00:09:44.782
item[ 2] index =   2  Plate =  19  Time = 00:10:05.764
item[ 3] index =   3  Plate =  48  Time = 00:10:40.490
item[ 4] index =   4  Plate =  50  Time = 00:10:42.970
item[ 5] index =   5  Plate =   2  Time = 00:18:56.867
item[ 6] index =   6  Plate =  12  Time = 00:19:17.303
item[ 7] index =   7  Plate =  19  Time = 00:20:17.519
item[ 8] index =   8  Plate =  48  Time = 00:20:56.598
item[ 9] index =   9  Plate =  17  Time = 00:21:13.960
item[10] index =  10  Plate =  50  Time = 00:21:26.284
item[11] index =  11  Plate =   2  Time = 00:28:47.375
item[12] index =  12  Plate =  12  Time = 00:29:13.193
item[13] index =  13  Plate =  19  Time = 00:30:41.599
item[14] index =  14  Plate =  48  Time = 00:31:25.871
item[15] index =  15  Plate =  50  Time = 00:31:53.515
item[16] index =  16  Plate =   2  Time = 00:38:32.719
item[17] index =  17  Plate =  12  Time = 00:38:59.130
item[18] index =  18  Plate =  57  Time = 00:40:03.855
item[19] index =  19  Plate =  34  Time = 00:40:57.862
item[20] index =  20  Plate =  19  Time = 00:41:09.796
item[21] index =  21  Plate =   9  Time = 00:42:00.044
item[22] index =  22  Plate =  48  Time = 00:42:06.050
item[23] index =  23  Plate =  50  Time = 00:42:08.593
item[24] index =  24  Plate =   2  Time = 00:48:26.238
item[25] index =  25  Plate =  12  Time = 00:48:34.475
==================
==================
item[ 0] index =   0  Plate =   2  Time = 00:09:30.430
item[ 1] index =   5  Plate =   2  Time = 00:18:56.867
item[ 2] index =  11  Plate =   2  Time = 00:28:47.375
item[ 3] index =  16  Plate =   2  Time = 00:38:32.719
item[ 4] index =  24  Plate =   2  Time = 00:48:26.238
item[ 5] index =  21  Plate =   9  Time = 00:42:00.044
item[ 6] index =   1  Plate =  12  Time = 00:09:44.782
item[ 7] index =   6  Plate =  12  Time = 00:19:17.303
item[ 8] index =  12  Plate =  12  Time = 00:29:13.193
item[ 9] index =  17  Plate =  12  Time = 00:38:59.130
item[10] index =  25  Plate =  12  Time = 00:48:34.475
item[11] index =   9  Plate =  17  Time = 00:21:13.960
item[12] index =   2  Plate =  19  Time = 00:10:05.764
item[13] index =   7  Plate =  19  Time = 00:20:17.519
item[14] index =  13  Plate =  19  Time = 00:30:41.599
item[15] index =  20  Plate =  19  Time = 00:41:09.796
item[16] index =  19  Plate =  34  Time = 00:40:57.862
item[17] index =   3  Plate =  48  Time = 00:10:40.490
item[18] index =   8  Plate =  48  Time = 00:20:56.598
item[19] index =  14  Plate =  48  Time = 00:31:25.871
item[20] index =  22  Plate =  48  Time = 00:42:06.050
item[21] index =   4  Plate =  50  Time = 00:10:42.970
item[22] index =  10  Plate =  50  Time = 00:21:26.284
item[23] index =  15  Plate =  50  Time = 00:31:53.515
item[24] index =  23  Plate =  50  Time = 00:42:08.593
item[25] index =  18  Plate =  57  Time = 00:40:03.855
==================
==================
item[ 0] index =   0  Plate =   2  Time = 00:09:30.430
item[ 1] index =   1  Plate =  12  Time = 00:09:44.782
item[ 2] index =   2  Plate =  19  Time = 00:10:05.764
item[ 3] index =   3  Plate =  48  Time = 00:10:40.490
item[ 4] index =   4  Plate =  50  Time = 00:10:42.970
item[ 5] index =   5  Plate =   2  Time = 00:18:56.867
item[ 6] index =   6  Plate =  12  Time = 00:19:17.303
item[ 7] index =   7  Plate =  19  Time = 00:20:17.519
item[ 8] index =   8  Plate =  48  Time = 00:20:56.598
item[ 9] index =   9  Plate =  17  Time = 00:21:13.960
item[10] index =  10  Plate =  50  Time = 00:21:26.284
item[11] index =  11  Plate =   2  Time = 00:28:47.375
item[12] index =  12  Plate =  12  Time = 00:29:13.193
item[13] index =  13  Plate =  19  Time = 00:30:41.599
item[14] index =  14  Plate =  48  Time = 00:31:25.871
item[15] index =  15  Plate =  50  Time = 00:31:53.515
item[16] index =  16  Plate =   2  Time = 00:38:32.719
item[17] index =  17  Plate =  12  Time = 00:38:59.130
item[18] index =  18  Plate =  57  Time = 00:40:03.855
item[19] index =  19  Plate =  34  Time = 00:40:57.862
item[20] index =  20  Plate =  19  Time = 00:41:09.796
item[21] index =  21  Plate =   9  Time = 00:42:00.044
item[22] index =  22  Plate =  48  Time = 00:42:06.050
item[23] index =  23  Plate =  50  Time = 00:42:08.593
item[24] index =  24  Plate =   2  Time = 00:48:26.238
item[25] index =  25  Plate =  12  Time = 00:48:34.475
==================
==================
item[ 0] = 2,00:09:30.430,00:18:56.867,00:28:47.375,00:38:32.719,00:48:26.238
item[ 1] = 9,00:42:00.044
item[ 2] = 12,00:09:44.782,00:19:17.303,00:29:13.193,00:38:59.130,00:48:34.475
item[ 3] = 17,00:21:13.960
item[ 4] = 19,00:10:05.764,00:20:17.519,00:30:41.599,00:41:09.796
item[ 5] = 34,00:40:57.862
item[ 6] = 48,00:10:40.490,00:20:56.598,00:31:25.871,00:42:06.050
item[ 7] = 50,00:10:42.970,00:21:26.284,00:31:53.515,00:42:08.593
item[ 8] = 57,00:40:03.855
==================
So, if you like you can use this, or use it to get yourself more familiar with stringlists and extracting data out of them.

I would suggest to make a clear decision on what data you have, what you would like to do with it before even continuing. Based on your wishes you would have to make a decision on what would be the best solution for you to use.

Nobody here is able to make that decision for you if you keep withdrawing vital information, f.e. where do these other fields like rider name etc. come from ? are they stored inside the same file, or do you have a separate file for that. Do you want to sort/select on riders name, or perhaps even want to be informed on a per lap base ?

In such case it becomes more interesting to use a database instead of toying with stringlists and records. all imho of course.

edit: whoopsy, i forgot to account for the last entry. corrected now.