Renaming information in columns (not column headers)

[nagemneuman]

Hello!

I have a list of defining variables within a column (FileName) which describes the conditions of the experiment. Each A01, A02, B01, B02… etc refers to a separate experimental setup.

I know that I can use the Column Rename (regex) node to search for each variable, and replace with the condition type - but that only works if the search string is in the column header (which it’s not). Is there another method to do this? I have two separate columns within a file that I need to do this for.

I have provided a screenshot of the column I am referring to for clarity!

[izaychik63]

If you familiar with Regex, you can use String Manipulation node corresponding functions

[bruno29a]

Hi @nagemneuman any Column Rename nodes are for renaming the headers indeed.

If you want to “rename” the data, then you can use the String Manipulation node as the simplest way - there are other nodes that you can use such as Column Expressions node, or any of the Rule Engine nodes, or via programming/script nodes such as Python, Java or R.

You also do not necessarily need to use Regex if what you are looking for is simple enough. Using the String Manipulation node, you can use functions such as Replace or regexReplace. You can check the available functions in the Function list, and you can click on any of the function and you will see a brief description of the function.

[nagemneuman]

Okay, thank you so much!

I don’t think I’m doing it right, because it’s not changing anything.

I have:

regexReplace(“B02”,"[a-zA-Z]{3}",“Q21_0uM-1”)

The “B02” is what I want to replace; honestly, don’t know what the middle statement is, but that’s how it was in all three KNIME examples; and “Q21_0uM-1” is what I want to replace it with.

Please let me know if you or @bruno29a have any suggestions.

[bruno29a]

Hi @nagemneuman , then just use a replace() statement instead.

replace($whateverColumn$, "B02", "Q21_0uM-1")

For example, if your column has “123B02whatever”, it will be replaced by “123Q21_0uM-1whatever”. Similarly if it has “B02”, it will be replaced by “Q21_0uM-1”.

If it does not work, maybe the best way is to give us a few sample, like 3 examples, and what you want as result for these 3 records.

[nagemneuman]

Still not working

This time I tried:

replace($FileName_MOOF1D$,“scan_Plate_R_p00_0_B02f00d1.TIF” ,“scan_Plate_R_p00_0_Q21_0uM-1f00d1.TIF” )

Here are three originals:
scan_Plate_R_p00_0_B01f05d1.TIF
scan_Plate_R_p00_0_B02f05d1.TIF
scan_Plate_R_p00_0_B03f00d1.TIF

I want them to change to:
scan_Plate_R_p00_0_Q21_NT-1f05d1.TIF
scan_Plate_R_p00_0_Q21_0uM-1f05d1.TIF
scan_Plate_R_p00_0_Q21_0.005uM-1f00d1.TIF

[bruno29a]

Hi @nagemneuman , try this in the String Manipulation node:

replace(replace(replace($FileName_MOOF1D$
  , "B01", "Q21_NT-1")
  , "B02", "Q21_0uM-1")
  , "B03", "Q21_0.005uM-1")

[nagemneuman]

Okay, that worked!

Well, it didn’t work with the original .csv document, but when I copied and pasted over the two columns I was interested in into a new .csv file, it did work.

[nagemneuman]

Okay, never mind! There were so many columns in the original document that I didn’t realize it was creating an entirely new column, not just replacing the current values. Rookie mistake!

Thanks so much for your help!

[bruno29a]

Hi @nagemneuman , yes you have to check if you are choosing Append Column, which will append a new column, or Replace Column which will replace a column with the new values.

I’m glad that it’s resolved.

[nagemneuman]

Oh that makes things a lot easier!! Thanks!

[nagemneuman]

Okay, here’s another one for you…

Now that I have them renamed, how can I properly sort my table? I’ll give an example below.

For each condition (column 1; see below) there are six replicates (f00-f05).
scan_Plate_R_p00_0_Q21_NT-1f00d1.TIF
scan_Plate_R_p00_0_Q21_NT-1f01d1.TIF
scan_Plate_R_p00_0_Q21_NT-1f02d1.TIF
scan_Plate_R_p00_0_Q21_NT-1f03d1.TIF
scan_Plate_R_p00_0_Q21_NT-1f04d1.TIF
scan_Plate_R_p00_0_Q21_NT-1f05d1.TIF
scan_Plate_R_p00_0_Q21_0uM-1f00d1.TIF
scan_Plate_R_p00_0_Q21_0uM-1f01d1.TIF
scan_Plate_R_p00_0_Q21_0uM-1f02d1.TIF
scan_Plate_R_p00_0_Q21_0uM-1f03d1.TIF
scan_Plate_R_p00_0_Q21_0uM-1f04d1.TIF
scan_Plate_R_p00_0_Q21_0uM-1f05d1.TIF
scan_Plate_R_p00_0_Q21_0.005uM-1f00d1.TIF
scan_Plate_R_p00_0_Q21_0.005uM-1f01d1.TIF
scan_Plate_R_p00_0_Q21_0.005uM-1f02d1.TIF
scan_Plate_R_p00_0_Q21_0.005uM-1f03d1.TIF
scan_Plate_R_p00_0_Q21_0.005uM-1f04d1.TIF
scan_Plate_R_p00_0_Q21_0.005uM-1f05d1.TIF

In column 2 are the associated intensity readings from 0-1.

I want to somehow calculate the mean intensity from all six trials (see below).

Condition | Intensity
scan_Plate_R_p00_0_Q21_NT-1meand1.TIF | 0.009
scan_Plate_R_p00_0_Q21_0uM-1meand1.TIF | 0.028
scan_Plate_R_p00_0_Q21_0.005uM-1meand1.TIF | 0.05

I know the basic methods of how to do this, but I can’t figure out how to group by specific condition and not by column.

[bruno29a]

Hi @nagemneuman , I think first of all, you should create new questions in a new thread. You’re asking different questions here which will obviously give different solutions for different questions, but you can’t mark which answer is which solution for which question, which is not helpful for other users who might have the same questions.

So, for your next question, please ask in a new thread, unless it’s related to what you are asking already. The sort or groupby question is independent of the replace() question. You’re just using the same data as example, but the operations are different.

So, please keep that in mind

To answer your new question, just create a new column without the f00-f05. And since they all end with d1.TIF, you can just remove everything from f00-f05 to the end. So, just do:
substr($FileName_MOOF1D$, 0, length($FileName_MOOF1D$) - length("f00d1.TIF"))

and append the result to a new column. Then just groupby on that new column.

For example, let’s say I have this input data:

I apply this:

Results:

Do a Groupby:

Results:

You can then rename the column as you want, etc

Alternatively, you can use this in your String Manipulation:
join(substr($FileName_MOOF1D$, 0, length($FileName_MOOF1D$) - length("f00d1.TIF")), "meand1.TIF")

That way, you will end up with the Condition names that you want: