Need to count pairs of rows

[epikuron]

Hi all! Please, help me. I do not know how to do it.

I have a log.

Date Time Direction Place Auto
01.01.2024 10:12 IN Place1 ABC001
01.01.2024 19:18 OUT Place1 ABC001
01.01.2024 21:10 OUT Place1 ABC001
02.01.2024 12:00 IN Place1 ABC123
05.01.2024 10:12 IN Place2 ABC001
04.01.2024 17:12 OUT Place2 ABC123
07.01.2024 19:28 OUT Place1 ABC001
07.01.2024 14:00 IN Place3 ABC123
07.01.2024 17:12 OUT Place3 ABC123
09.01.2024 11:12 IN Place1 BBB022
11.01.2024 21:10 OUT Place1 BBB022
09.01.2024 11:12 IN Place1 ABC001
11.01.2024 21:10 OUT Place1 ABC001

1. I need to count how many visits (pairs of IN and OUT) of each of the Auto.
   ABC001 3
   ABC123 2
   BBB022 1
2. Remove possible errors

this
01.01.2024 10:12 IN Place1 ABC001
01.01.2024 19:18 OUT Place1 ABC001
01.01.2024 21:10 OUT Place1 ABC001

correct to this
01.01.2024 10:12 IN Place1 ABC001
01.01.2024 21:10 OUT Place1 ABC001

Thanks!!!

[evert.homan_scilifelab.se]

Hi,

#1: save your log file as a text file, read it with the File Reader node, using space as the column delimiter instead of the default comma. This should read your file with the headers correctly. I then use the Rule-based Row Splitter node to split the table in two based on Direction (IN/OUT). Then I group the ‘IN’ output on Auto and count the Dates. This gives you the number of visits (assuming this is the same as number of times a car drives IN).

#2: the two outputs from the Rule-based Row Splitter are grouped separately on Date and Auto, and then aggregated on the minimum time for ‘IN’ and maximum time for ‘OUT’ respectively. These 2 tables are then concatenated. You have now for each date and auto 1 ‘IN’ and 1 'OUT row.

I note in your data that some cars have different ‘IN’ and ‘OUT’ places, but maybe this is irrelevant?

Hope this is what you wanted to achieve.

BW/Evert

Need to count pairs of rows.knwf (16.5 KB)

[epikuron]

Thanks.I will try it

[epikuron]

I try it, but it’s not working on other sample
I don’t understand why

|Date|Time|Direction|Place|Auto|
|01.01.2024|10:12|IN|Place1|ABC001|
|01.01.2024|19:18|OUT|Place1|ABC001|
|02.01.2024|21:10|OUT|Place1|ABC001|
|02.01.2024|12:00|IN|Place1|ABC123|
|05.01.2024|10:12|IN|Place2|ABC001|
|04.01.2024|17:12|OUT|Place2|ABC123|
|07.01.2024|19:28|OUT|Place1|ABC001|
|07.01.2024|14:00|IN|Place3|ABC123|
|07.01.2024|17:12|OUT|Place3|ABC123|
|09.01.2024|11:12|IN|Place1|BBB022|
|11.01.2024|21:10|OUT|Place1|BBB022|
|09.01.2024|11:12|IN|Place1|ABC001|
|11.01.2024|21:10|OUT|Place1|ABC001|

[HansS]

Hi @epikuron

I was thinking of a wf like this count_pair_of_rows.knwf (69.2 KB)

If there are multiple OUT’s before a new IN then only the last OUT is selected . (Duplicate Row Filter) . The Loop has 2 outputs. The first are the IN-&-OUT’s per car. The second is a count of the number of movements.

gr. Hans

[epikuron]

Thanks! I’ll try it!