openmolecules.org Forum: Functionality » Averaging Values in three columns

Home » DataWarrior » Functionality » Averaging Values in three columns

Show: Today's Messages :: Polls :: Message Navigator

Averaging Values in three columns [message #1040]

Fri, 14 August 2020 23:19

ELFritzen
Messages: 47
Registered: November 2019
Location: North Carolina, US

Member

I'd like to average the values contained in three different columns into a new calculated column. The expression I use is (Column1+Column2+Column3)/3 to set up the calculated column. The calculated column only returns values when there is a value in each of the columns. Is there a way to write the expression that will return an average when one of the values was not determined?
Thanks,
Ed

Ed Fritzen

Report message to a moderator

Re: Averaging Values in three columns [message #1042 is a reply to message #1040]

Sat, 15 August 2020 13:40

thomas
Messages: 729
Registered: June 2014

Senior Member

it is a little longer, but this would work:

(if(isempty(A),0,A)+if(isempty(B),0,B)+if(isempty(C),0,C))/
(if(isempty(A),0,1)+if(isempty(B),0,1)+if(isempty(C),0,1))

[Updated on: Sat, 15 August 2020 13:41]

Report message to a moderator

Re: Averaging Values in three columns [message #1043 is a reply to message #1042]

Sun, 16 August 2020 19:19

ELFritzen
Messages: 47
Registered: November 2019
Location: North Carolina, US

Member

Thank you, Thomas,

That worked perfectly.

Ed

Ed Fritzen

Report message to a moderator

Re: Averaging Values in three columns [message #1044 is a reply to message #1043]

Sun, 16 August 2020 21:15

ELFritzen
Messages: 47
Registered: November 2019
Location: North Carolina, US

Member

I spoke too soon. The expression you provided did create the new calculated column with the averages I was interested in. However, when I I saved the new DW File, the the only thing that was saved was single column with the name of column being the numerator of the expression. The entire DW was overwritten with just this one column. I've attached the DW file to this post. I had replaced the variables in the expression you gave me with the actual column names I wanted to average in my DW File. After I calculated the new values into the new column, then saved the the DW file, the attached file is what I got.

Thank you for your help. I'm a Synthetic/Medicinal Chemist who doesn't know a lot about databases, and I'm responsible for maintaining our corporate compound database.

Ed Fritzen

Attachment: Copy of MasterFileAug13(wVivaCompdsandCalcSolAve).dwar
(Size: 112.80KB, Downloaded 403 times)

Ed Fritzen

Report message to a moderator

Re: Averaging Values in three columns [message #1045 is a reply to message #1044]

Mon, 17 August 2020 13:54

thomas
Messages: 729
Registered: June 2014

Senior Member

I forgot, official DataWarrior 5.2.1 still contains a bug when writing formulas to a file, if these formulas contain newline-characters. Thus, the correct answer from my side should be this (without a newline at the end):

(if(isempty(A),0,A)+if(isempty(B),0,B)+if(isempty(C),0,C))/( if(isempty(A),0,1)+if(isempty(B),0,1)+if(isempty(C),0,1))

I have attached the fixed file (removed two newlines). The downloadable DataWarrior development patch has fixed this issue.

Sorry for that, Thomas