openmolecules.org Forum: Functionality » Updating Merged Columns

Home » DataWarrior » Functionality » Updating Merged Columns

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

Updating Merged Columns [message #1125]

Thu, 19 November 2020 16:09

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

Member

I've created a number of merged columns in a DataWarrior file. As an example, the merged column might have data for the same assay from two different vendors. When I add new data to the source columns, I noticed the merged columns are not updated. Is there a way to update merged columns when data is added to the source columns? I know you can re-calculate columns that are calculated values, so this would be similar for updated merged columns.

Thanks,
Ed

Ed Fritzen

Report message to a moderator

Re: Updating Merged Columns [message #1126 is a reply to message #1125]

Thu, 19 November 2020 17:48

nbehrnd
Messages: 224
Registered: June 2019

Senior Member

I would suggest to continue on the path mentioned by you, i.e. starting with two columns of raw data and a third column based on calculated data offering the quick re-calculate option (yielding the update).

To illustrate, I set up a small set of molecules and randomly attributed data. To simulate data missing, I kept some of the cells empty, or used as entries the strings "0" .or. "NaN". The column to pick data from either one of the two previous columns then used the if-clause

if((vendor_1!=0), vendor_1,vendor_2)

which works reasonably well in terms of an update in the new column.

index.php?t=getfile&id=291&private=0

This however assumes the absence of a row, where both columns contain disagreeing entries ...

Attachment: example_update.png
(Size: 25.82KB, Downloaded 696 times)
Attachment: update_from_two_columns.dwar
(Size: 1.92KB, Downloaded 332 times)

Report message to a moderator

Re: Updating Merged Columns [message #1127 is a reply to message #1126]

Thu, 19 November 2020 20:53

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

Member

Thank you for the prompt reply, nbehrnd,

I understand what you're saying, however, how would the formula change if there were values for both vendor_1 and vendor_2, and you wanted to show both values in the "merged" column. In the attached file, how could I get the vendor_1_2 column (the calculated column) to look like the merged column? Can that be done with a calculated expression?

Thanks for the help.

Ed

Attachment: update_from_two_columns_mergedcolumn.dwar
(Size: 2.11KB, Downloaded 360 times)

Ed Fritzen

Report message to a moderator

Re: Updating Merged Columns [message #1128 is a reply to message #1127]

Thu, 19 November 2020 21:51

nbehrnd
Messages: 224
Registered: June 2019

Senior Member

Dear Ed,

based on your reply, I understand that my initial reading of your question does not fit your needs. I wrongly assumed you wish to display in column C at either the result of column A, or of column B. But you want to display both values simultaneously in one column of recalculated values.

This is possible using an expression in the following pattern

str(if(vendor_1!=0,vendor_1,"NaN")) + str("; ") + str(if(vendor_2!=0,vendor_2,"NaN"))

The expression asks both columns independently for a value -- if there is no value, these if clauses will explicitly state the absence by issuing an «NaN» string. These results, regardless if a numeric result, or some text, will be converted into a string. These are separated by a third string comprising a semi-colon and an explicit space (but it could be any other separator character, too [.cvs files]). Eventually, the plus signs are used to concatenate the parts into the string displayed in the table.

Et voilà, in the sixth column (an example .dwar file is attached, too):

index.php?t=getfile&id=295&private=0