Count ranges

[jerem488]

Hi all,

 

I don't know what title to put for this problem !

I have data like this, in a txt file :

61 - 65;66 - 70;-140.4
61 - 65;66 - 70;75
26 - 30;76 - 80;-206.2
36 - 40;11 - 15;-72.6
116 - 120;6 - 10;-130.8
61 - 65;81 - 85;-191.6
86 - 90;66 - 70;-215.8
41 - 45;81 - 85;24.8
91 - 95;31 - 35;-96
6 - 10;41 - 45;-235.7

The txt file is attached to this topic.

I want read this file line per line and want make the sum of each slices (61 - 65;66 - 70) and substract the reverse slices.

For example :

61 - 65;66 - 70;-140.4
61 - 65;66 - 70;75

66 - 70;61-65;45.20

I want the result : -140.4 + 75 -45.20 = -110.6 for the 61 - 65;66 - 70 slice.

I don't know if that's understandable!

Data_Logs.txt

[Andreik]

Don't have a clue where all these numbers comes from.

You have this line

Quote

61 - 65;66 - 70;-140.4

and this formula

Quote

I want the result : -140.4 + 75 -45.20 = -110.6 for the 61 - 65;66 - 70 slice.

-140.4 is the last set of data for the first line, what about 75 and -45.20? Where do these numbers come from?

 

Looking again at the sample data above I understand that these ranges can be found on multiple lines, right? But also that the order might be reversed like here:

Quote

61 - 65;66 - 70;-140.4
61 - 65;66 - 70;75

66 - 70;61-65;45.20

So your goal is to sum the third set of data where the first two sets of data are the same (not necessarily in the same order)?

Edited November 30, 2023 by Andreik

[Somerset]

is this 3d coordinates?

[jerem488]

Each line corresponds to factory parts

We have 5 centimeter slices cut out of tubes.
We have production lines and we want to measure the scrap rate on the melting of tubes to create bottles.

 

If we have negative values it means that the tube did not arrive quickly enough or the camera analyzing the tubes scanned incorrectly.

[jerem488]

expandcollapse popup

#include <Array.au3>
#include <File.au3>

$aOutputTable = __ReadFile(@DesktopDir & "\TxScraps.txt", ";")

__Scrap($aOutputTable)
Func __Scrap($aAnalysys)
    Local $iSizeArray = UBound($aAnalysys)
    If @error Then Exit

    Local $aTable[0][3]
    For $i = 0 To UBound($aAnalysys) -1
        If $aAnalysys[$i][4] == $aAnalysys[$i][5] Then ContinueLoop
        $Isum = Number(StringReplace($aAnalysys[$i][2], ",", "."))
        If UBound($aTable) = 0 Then
            _ArrayAdd($aTable, $aAnalysys[$i][4] & "|" & $aAnalysys[$i][5] & "|" & $Isum)
        EndIf
        For $x = 0 To UBound($aTable) -1
            If $aTable[$x][0] == $aAnalysys[$i][4] And $aTable[$x][1] == $aAnalysys[$i][5] Then
                $aTable[$x][2] += $Isum
                ContinueLoop 2
            EndIf
        Next
        _ArrayAdd($aTable, $aAnalysys[$i][4] & "|" & $aAnalysys[$i][5] & "|" & $Isum)
    Next

    For $i = 0 To UBound($aAnalysys) -1
        For $x = 0 To UBound($aTable) -1
            If $aTable[$x][0] == $aAnalysys[$i][5] And $aTable[$x][1] == $aAnalysys[$i][4] Then
                $aTable[$x][2] -= $Isum
                ContinueLoop
            EndIf
        Next
    Next


    $sFile = @ScriptDir & "\Results_Scrap.txt"
    Local $hBackupFileOpen = FileOpen($sFile, BitOR($FO_OVERWRITE, $FO_CREATEPATH))
    If $hBackupFileOpen = -1 Then
        ConsoleWrite("Error opening file" & $sFile & @LF)
    EndIf
    For $y = 0 To UBound($aTable) -1
        FileWriteLine($hBackupFileOpen, $aTable[$y][0] & ";" & $aTable[$y][1] & ";" & StringReplace($aTable[$y][2], ".", ","))
    Next

    _ArrayDisplay($aTable)
EndFunc

Func __ReadFile($sFilePath, $sDelimiter)
    Local $aFileArray

    _FileReadToArray($sFilePath, $aFileArray, 0, $sDelimiter)
    If @error Then ConsoleWrite(@error & @LF)
    Return $aFileArray
EndFunc

It doesn't work properly

[Andreik]

It's still not clear how your data it's organized and what do you want to achieve. What I understand it's that each line have 3 sets of data delimited by ; and first two sets consists of some ranges. If these ranges are identically but not necessarily in the same order (ex. 61-65;66-70 is considered identical with 66-70;61-55) then you want an aggregated sum of third data sets across all the lines. Is that right?

[jerem488]

@Andreik Exactly !

[Andreik]

But why for first two lines the last data set is added and for the third line is subtracted? It's because the first two ranges are not in the same order?

Quote

 

61 - 65;66 - 70;-140.4
61 - 65;66 - 70;75
66 - 70;61 - 65;45.20

-140.4 + 75 -45.20 = -110.6

 

Based on what exactly do you know if the last data set should be added or subtracted? What if the order of lines is this one?

Quote

66 - 70;61 - 65;45.20
61 - 65;66 - 70;-140.4
61 - 65;66 - 70;75

What should be the result now? I suppose: 45.20 - (-140.4) - 75 = 114.2   Is that right? Or because first range (66-70) is greater than second range (61-65) that means it's reversed and the formula should be -45.20 + (-140.4) + 75 = -110.6

[jerem488]

The correct calculation is -45.20 + (-140.4) + 75 = -110.6

[Andreik]

This should work:

expandcollapse popup

#include <Array.au3>

Global $mResult[]
Global $sData = FileRead('Data_Logs.txt')
Global $aMatches = StringRegExp($sData, '(\d+\h*-\h*\d+);(\d+\h*-\h*\d+);([-]{0,1}\d+(?:\.\d)*)', 4)
Global $CurrentMatch, $FirstRange, $SecondRange, $sKey, $aKey1, $aKey2

If IsArray($aMatches) Then
    For $Index = 0 To UBound($aMatches) - 1
        $CurrentMatch = $aMatches[$Index]
        $FirstRange = MapExists($mResult, $CurrentMatch[1] & ';' & $CurrentMatch[2])
        $SecondRange = MapExists($mResult, $CurrentMatch[2] & ';' & $CurrentMatch[1])
        If $FirstRange Or $SecondRange Then
            $sKey = $CurrentMatch[($FirstRange ? 1 : 2)] & ';' & $CurrentMatch[($FirstRange ? 2 : 1)]
            $mResult[$sKey] += ($FirstRange ? $CurrentMatch[3] : -$CurrentMatch[3])
        Else
            $aKey1 = StringRegExp($CurrentMatch[1], '(\d+)\h*-\h*(\d+)', 3)
            $aKey2 = StringRegExp($CurrentMatch[2], '(\d+)\h*-\h*(\d+)', 3)
            $sKey = $CurrentMatch[($aKey1[0] < $aKey2[0] ? 1 : 2)] & ';' & $CurrentMatch[($aKey1[0] < $aKey2[0] ? 2 : 1)]
            $mResult[$sKey] = ($aKey1[0] < $aKey2[0] ? $CurrentMatch[3] : -$CurrentMatch[3])
        EndIf
    Next
EndIf

MapDisplay($mResult)

Func MapDisplay(Const ByRef $mMap, $sTitle = 'MapDisplay')
    If Not IsMap($mMap) Then Return SetError(1, 0, Null)
    Local $aMapKeys = MapKeys($mMap)
    Local $aDisplay[UBound($aMapKeys) + 1][2] = [['Key', 'Value']]
    Local $Index = 1
    For $vKey In $aMapKeys
        $aDisplay[$Index][0] = $vKey
        $aDisplay[$Index][1] = $mMap[$vKey]
        $Index += 1
    Next
    _ArrayDisplay($aDisplay, $sTitle)
EndFunc

The script it's tested with these lines in Data_Logs.txt

Quote

66 - 70;61 - 65;45.20
61 - 65;66 - 70;-140.4
61 - 65;66 - 70;75

The order of lines it's not important and when first range is greater than second range the last data set will be subtracted. The result it's a map but as you can see in MapDisplay() function it's quite simple to convert a map to an array.

Edited November 30, 2023 by Andreik
Minor changes to code

[Nine]

Another way :

#include <File.au3>

Local $aList, $mList[], $iTmp1, $iTmp2, $iMult, $sKey
_FileReadToArray("Data_Logs.txt", $aList, $FRTA_COUNT, ";")
_ArrayDisplay($aList)
For $i = 1 To $aList[0][0]
  $iTmp1 = Int(StringSplit($aList[$i][0], " - ", $STR_ENTIRESPLIT + $STR_NOCOUNT)[0])
  $iTmp2 = Int(StringSplit($aList[$i][1], " - ", $STR_ENTIRESPLIT + $STR_NOCOUNT)[0])
  If $iTmp2 < $iTmp1 Then
    $sKey = $aList[$i][1] & "|" & $aList[$i][0]
    $iMult = -1
  Else
    $sKey = $aList[$i][0] & "|" & $aList[$i][1]
    $iMult = 1
  EndIf
  If MapExists($mList, $sKey) Then
    $mList[$sKey] += $aList[$i][2] * $iMult
  Else
    $mList[$sKey] = $aList[$i][2] * $iMult
  EndIf
Next
$aList = MapKeys($mList)
For $i = 0 To UBound($aList) - 1
  ConsoleWrite($aList[$i] & " = " & $mList[$aList[$i]] & @CRLF)
Next

 

Edited December 1, 2023 by Nine
cleaner code