Calculate mean of coloumns in array of varying size Topic is solved

[fuglen]

Hi

On a project, I copy the contents of a table, and then I would like to calculate the mean of different columns. The table I copy always has the same number of coloumns but the number of rows may wary (see picture for an example of two different tables):

image.png (19 KiB) Viewed 1145 times

My goal is to be able to calculate the mean of the last three coloumns for each "Kanal" (see the red circles in picture):

image.png (16.68 KiB) Viewed 1145 times

My first step has been to use StrSplit to separate the string into an array but after that, I´m unsure of what to do.

Code: Select all

lines := StrSplit(clipboard, "`n")

columns := []
for index, value in lines
    columns.Insert(StrSplit(value, "`t"))

The table example is attached as a txt file

Any help is greatly appreciated.

[mikeyww]

Code: Select all

Global val := [], col := []
str =
(
Kanal	Epoke	Turns	Mean Amp	T/A Ratio
		[#/s]	[µV]	
Kanal 1	1	286	256	1.12
	2	2069	671	3.1
	3	23.8	122	0.19
	4	161	205	0.79
	> 5 <	435	333	1.31
				
Kanal 2	1	138	181	0.76
	2	2064	750	2.8
	3	53.8	115	0.47
	4	149	205	0.73
	> 5 <	580	361	1.61
				
Kanal 3	1	158	219	0.72
	2	2089	756	2.8
	3	40.0	111	0.36
	4	136	175	0.78
	> 5 <	665	344	1.94
)
For rowNum, row in StrSplit(str, "`n")
 For colNum, cell in StrSplit(row, "`t")
  Switch rowNum {
   Case 1: col[colNum] := cell
   Case 2:
   Default:
    Switch colNum {
     Case 1:  kanal := cell > "" ? cell : kanal
     Case 2:  epoke := cell
     Default: val[kanal, epoke, col[colNum]] := cell
    }
  }
MsgBox, % mean("Kanal 3", "Mean Amp")
MsgBox, % mean("Kanal 1", "T/A Ratio")

mean(kanal, colName) {
 n := sum := 0
 For epoke, set in val[kanal]
  sum += set[colName], n++
 Return Format("{:.2f}", sum / n)
}

[Chunjee]

https://biga-ahk.github.io/biga.ahk/#/?id=mean accepts arrays as input

Code: Select all

A := new biga() ; requires https://www.npmjs.com/package/biga.ahk

A.mean([4, 2, 8, 6])
; => 5

[fuglen]

Code: Select all

Global val := [], col := []
str =
(
Kanal	Epoke	Turns	Mean Amp	T/A Ratio
		[#/s]	[µV]	
Kanal 1	1	286	256	1.12
	2	2069	671	3.1
	3	23.8	122	0.19
	4	161	205	0.79
	> 5 <	435	333	1.31
				
Kanal 2	1	138	181	0.76
	2	2064	750	2.8
	3	53.8	115	0.47
	4	149	205	0.73
	> 5 <	580	361	1.61
				
Kanal 3	1	158	219	0.72
	2	2089	756	2.8
	3	40.0	111	0.36
	4	136	175	0.78
	> 5 <	665	344	1.94
)
For rowNum, row in StrSplit(str, "`n")
 For colNum, cell in StrSplit(row, "`t")
  Switch rowNum {
   Case 1: col[colNum] := cell
   Case 2:
   Default:
    Switch colNum {
     Case 1:  kanal := cell > "" ? cell : kanal
     Case 2:  epoke := cell
     Default: val[kanal, epoke, col[colNum]] := cell
    }
  }
MsgBox, % mean("Kanal 3", "Mean Amp")
MsgBox, % mean("Kanal 1", "T/A Ratio")

mean(kanal, colName) {
 n := sum := 0
 For epoke, set in val[kanal]
  sum += set[colName], n++
 Return Format("{:.2f}", sum / n)
}

Thank you Mikey.
It works great but I would like str to be the content of the clipboard. If I do str := clipboard instead of typing the values in the script it doesnt work. I wonder what the difference is?

[mikeyww]

The difference is probably what is on your clipboard. I did use your clipboard file in testing.

You could post your clipboard contents, as well as your revised script. If your data come from Microsoft Excel, then realize that Excel adds extra CRLF sequences to copied text.

[fuglen]

The difference is probably what is on your clipboard. I did use your clipboard file in testing.

You could post your clipboard contents, as well as your revised script. If your data come from Microsoft Excel, then realize that Excel adds extra CRLF sequences to copied text.

I open the txt file and manually copy the entire table. I then run your script but it only seems the work on "Kanal 3", "Mean Amp". Everything else returns 0.00:

Code: Select all

Global val := [], col := []
MsgBox %Clipboard%
str := Clipboard
MsgBox %str%

For rowNum, row in StrSplit(str, "`n")
 For colNum, cell in StrSplit(row, "`t")
  Switch rowNum {
   Case 1: col[colNum] := cell
   Case 2:
   Default:
    Switch colNum {
     Case 1:  kanal := cell > "" ? cell : kanal
     Case 2:  epoke := cell
     Default: val[kanal, epoke, col[colNum]] := cell
    }
  }
MsgBox, % mean("Kanal 3", "Mean Amp")
MsgBox, % mean("Kanal 1", "T/A Ratio")

mean(kanal, colName) {
 n := sum := 0
 For epoke, set in val[kanal]
  sum += set[colName], n++
 Return Format("{:.2f}", sum / n)
}

[fuglen]

Hmm, so when I use the clipboard instead I´ve found that only "Turns" and "Mean Amp" work for only the last "Kanal". So if my table only has Kanal 1, "Turns" and "Mean Amp" is calculated correct (T/A Ratio always shows 0.00). If my table has Kanal 1 and Kanal 2, "Turns" and "Mean Amp" is calculated correct only for Kanal 2 and so on.

Seems to be the same problem if I use FileRead of the txt file instead of using the clipboard

Code: Select all

Global val := [], col := []
FileRead, str, C:\Users\"ME"\Desktop\clipboard.txt

MsgBox %str%

For rowNum, row in StrSplit(str, "`n")
 For colNum, cell in StrSplit(row, "`t")
  Switch rowNum {
   Case 1: col[colNum] := cell
   Case 2:
   Default:
    Switch colNum {
     Case 1:  kanal := cell > "" ? cell : kanal
     Case 2:  epoke := cell
     Default: val[kanal, epoke, col[colNum]] := cell
    }
  }
MsgBox, % mean("Kanal 3", "Mean Amp")
MsgBox, % mean("Kanal 1", "Turns")

mean(kanal, colName) {
 n := sum := 0
 For epoke, set in val[kanal]
  sum += set[colName], n++
 Return Format("{:.2f}", sum / n)
}

[mikeyww]

I found two bugs. Revision is below.

Code: Select all

Global val := [], col := []
FileRead, str, %A_ScriptDir%\clipboard.txt
For rowNum, row in StrSplit(str, "`n")
 For colNum, cell in StrSplit(StrReplace(row, "`r"), "`t")
  Switch rowNum {
   Case 1: col[colNum] := cell
   Case 2:
   Default:
    Switch colNum {
     Case 1:  kanal := cell > "" ? cell : kanal
     Case 2:  epoke := cell
     Default:
      If (epoke > "")
       val[kanal, epoke, col[colNum]] := cell
    }
  }
MsgBox, % mean("Kanal 3", "Mean Amp")
MsgBox, % mean("Kanal 2", "Mean Amp")
MsgBox, % mean("Kanal 1", "T/A Ratio")

mean(kanal, colName) {
 n := sum := 0
 For epoke, set in val[kanal]
  sum += set[colName], n++
 Return Format("{:.2f}", sum / n)
}

[fuglen]

Hi @mikeyww hope you are still active

THe script is working great, but can we change it so that I don´t have to type the "kanal name" when I what to extract the mean? There has been a change so the name of the "kanal" can be called many things now, so I was thinking maybe looping through the names instead?

So in "MsgBox, % mean("Kanal 2", "Mean Amp")"
Instead of "Kanal 2" it could be called Biceps, Triceps, or something else, so I would like to be able to just extract whatever the second "Kanal" is called

Code: Select all

Global val := [], col := []
str =
(
Kanal	Epoke	Mærke	Turns	Mean Amp	T/A Ratio
			[#/s]	[µV]
Kanal 1	1		504	304	1.66
	2		16.3	104	0.16
	> 3 <		658	320	2.1

Kanal 2	1		484	318	1.52
	2		27.5	108	0.26
	> 3 <		559	312	1.79

Kanal 3	1		502	300	1.67
	2		36.3	113	0.32
	> 3 <		530	308	1.72
)

For rowNum, row in StrSplit(str, "`n")
 For colNum, cell in StrSplit(row, "`t")
  Switch rowNum {
   Case 1: col[colNum] := cell
   Case 2:
   Default:
    Switch colNum {
     Case 1:  kanal := cell > "" ? cell : kanal
     Case 2:  epoke := cell
     Default: val[kanal, epoke, col[colNum]] := cell
    }
  }

MsgBox, % mean("Kanal 2", "Turns")

mean(kanal, colName) {
 n := sum := 0
 For epoke, set in val[kanal]
  sum += set[colName], n++
 Return Format("{:.2f}", sum / n)
}

[mikeyww]

One idea is below. There could be better ways to set up the array(s).

Code: Select all

Global val := [], col := [], kanal := []
FileRead, str, %A_ScriptDir%\clipboard.txt
For rowNum, row in StrSplit(str, "`n")
 For colNum, cell in StrSplit(StrReplace(row, "`r"), "`t")
  If (rowNum > 1)
   Switch colNum {
    Case 1 : (cell > "") && kanal.Push(cell)
    Case 2 : epoke := cell
    Default: (epoke > "") && val[kanal[kanal.Count()], epoke, col[colNum]] := cell
   }
  Else col[colNum] := cell
MsgBox, % mean(1, "Mean Amp")
MsgBox, % mean(2, "Mean Amp")
MsgBox, % mean(1, "T/A Ratio")

mean(kanalNum, colName) {
 For epoke, set in val[kanal[kanalNum]]
  sum += set[colName]
 Return Format("{:.2f}", sum / val[kanal[kanalNum]].Count())
}

[fuglen]

@mikeyww In the case of only one channel (Kanal) being used, how can I get the value of a single cell?

So for example if I in the below str want to get "Epoke 3" from the column "Turns"? (should be 658)

Code: Select all

str =
(
Kanal	Epoke	Mærke	Turns	Mean Amp	T/A Ratio
			[#/s]	[µV]
Kanal 1	1		504	304	1.66
	2		16.3	104	0.16
	> 3 <		658	320	2.1
)

[mikeyww]

Code: Select all

MsgBox, % val["Kanal 1", "> 3 <", "Turns"]

[fuglen]

Code: Select all

MsgBox, % val["Kanal 1", "> 3 <", "Turns"]

@mikeyww Can´t seem to make it work if I choose another "Epoke". Seems to only be working when I choose to get information out of the epoke with the > and < signs in them. How can that be?

[mikeyww]

Explained: Keys

Quoted literal strings are considered purely non-numeric in v1.x, so x[1] and x["1"] are not equivalent. Additionally, if a quoted literal string is concatenated with another value (as in "0x" x), the result is treated as purely non-numeric. However, this does not apply to variables, so x[1] and x[y:="1"] are equivalent. This issue will be resolved in AutoHotkey v2, so scripts should avoid using quoted numeric literals as keys.

Code: Select all

MsgBox, % val["Kanal 1", 2, "Turns"]

[fuglen]

Explained: Keys

Quoted literal strings are considered purely non-numeric in v1.x, so x[1] and x["1"] are not equivalent. Additionally, if a quoted literal string is concatenated with another value (as in "0x" x), the result is treated as purely non-numeric. However, this does not apply to variables, so x[1] and x[y:="1"] are equivalent. This issue will be resolved in AutoHotkey v2, so scripts should avoid using quoted numeric literals as keys.

Code: Select all

MsgBox, % val["Kanal 1", 2, "Turns"]

Great! Can you lead me to somewhere I can better udnerstand the use of Switch and Case 1 and Case 2 in the code?

[mikeyww]

Certainly. Switch