openmolecules.org

 
Home » DataWarrior » Functionality » Row Wise Sum
Row Wise Sum [message #1926] Tue, 13 June 2023 10:46 Go to next message
amorrison
Messages: 36
Registered: March 2016
Member
Hi,

I'm trying to do a row wise sum of several columns within a Row to get a total value. In some cases there will be a empty value within a particular row. I've tried both sum(x,y,z) and also (x+y+z), this works fine for rows that are fully populated but for rows that have a empty value I get 'NAN' returned. Can I force it to ignore this and give me the sum even when there is a empty value?

In addition, is it possible to get a count of the populated values within the row? So for example, within the row 4 out of 5 of the columns have data, the number I would like to return is "4".

Just in case this is the issue, I am using an if statement to generate the numbers with an initial if(isempty(var),"","Do something else").

Many thanks,

Angus


Re: Row Wise Sum [message #1927 is a reply to message #1926] Tue, 13 June 2023 20:49 Go to previous messageGo to next message
nbehrnd is currently offline  nbehrnd
Messages: 211
Registered: June 2019
Senior Member
Dear Angus,

to answer the first question, you need a placeholder which either returns `0` (if there is nothing in the cell), or the value of the cell (if the cell contains an entry acceptable for an addition); `replaceempty` is suitable here. For the second question, you already tap against a good door; you now need to know how to negate a Boolean in DataWarrior's syntax.

For the purpose of illustration, I attach a minimal working example below. Departing from a small library of random molecules, DataWarrior computes the number of H-Donors and H-Acceptors. Manually added -- leaving some gaps is intentional -- two additional columns with manual entries, `counter_a` and `counter_b`. The `special_sum` then

HAcceptors + HDonors + replaceempty(counter_a, 0) + replaceempty(counter_b, 0)
either adds either the results of DataWarrior's intrinsic functions plus the zeroes, or the values of the additional columns. To count the the non-empty columns `!` offers to define

!isempty(HAcceptors) + !isempty(HDonors) + !isempty(counter_a) + !isempty(counter_b)
You can access, adjust and update the equations in question in the header cell of the corresponding column 7 and 8 (clique with the right mouse button).

With regards,

Norwid
Re: Row Wise Sum [message #1929 is a reply to message #1927] Wed, 14 June 2023 08:44 Go to previous message
amorrison
Messages: 36
Registered: March 2016
Member
Hi Norwid,

Many thanks that worked a treat.

Best regards,

Angus
Previous Topic: Unable to Create New Form
Next Topic: is it possible to find text
Goto Forum:
  


Current Time: Thu Jun 13 10:10:31 CEST 2024

Total time taken to generate the page: 0.04276 seconds