openmolecules.org

 
Home » DataWarrior » Functionality » Averaging Values in three columns
Averaging Values in three columns [message #1040] Fri, 14 August 2020 23:19 Go to next message
ELFritzen is currently offline  ELFritzen
Messages: 39
Registered: November 2019
Location: Connecticut, 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
Re: Averaging Values in three columns [message #1042 is a reply to message #1040] Sat, 15 August 2020 13:40 Go to previous messageGo to next message
thomas is currently offline  thomas
Messages: 523
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 Go to previous messageGo to next message
ELFritzen is currently offline  ELFritzen
Messages: 39
Registered: November 2019
Location: Connecticut, US
Member
Thank you, Thomas,

That worked perfectly.

Ed


Ed Fritzen
Re: Averaging Values in three columns [message #1044 is a reply to message #1043] Sun, 16 August 2020 21:15 Go to previous messageGo to next message
ELFritzen is currently offline  ELFritzen
Messages: 39
Registered: November 2019
Location: Connecticut, 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


Ed Fritzen
Re: Averaging Values in three columns [message #1045 is a reply to message #1044] Mon, 17 August 2020 13:54 Go to previous messageGo to next message
thomas is currently offline  thomas
Messages: 523
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

[Updated on: Mon, 17 August 2020 13:54]

Report message to a moderator

Re: Averaging Values in three columns [message #1047 is a reply to message #1045] Mon, 17 August 2020 15:57 Go to previous message
ELFritzen is currently offline  ELFritzen
Messages: 39
Registered: November 2019
Location: Connecticut, US
Member
Thank you, Thomas! That works great now.

Ed


Ed Fritzen
Previous Topic: Tagging Data to a Particular Assay
Next Topic: Smiles to Structure
Goto Forum:
  


Current Time: Sun Aug 07 18:56:51 CEST 2022

Total time taken to generate the page: 0.01771 seconds