Extract specific column values into columns

[yukiii1223]

I would like to extract the Temperature and Light sensor readings into two columns.
But I cannot find the correct nodes to do this process.

[takbb]

Hi @yukiii1223, welcome to the forum.

If you can give some context then it will be easier for people to help… We can probably help if we know what the aim is, but remember that we aren’t necessarily all up to speed on your Temperature and Light sensor readings…

What form does your data take and what is it you are trying to do?

[yukiii1223]

jjjjjj.knwf (4.8 KB)
The column Value is mixed by Temperature sensor readings, Light sensor readings, and Motion sensor & Door sensor states (ON/OFF, OPEN/CLOSE).

Column Sensor ID:
DXXX Door Sensor
LSXXX Light Sensor
MXXX Motion Detector
MAXXX Motion Area Detector
TXXX Temperature

I need to extract the Temperature and light values for future numerical analysis. light sensor Integer values ranging from 0 to 100, and Temperature has a decimal value in Celsius.

However, i cannot create two new columns to extract the Temp & Light values, could you please help me? Thank You!

[takbb]

Hi @yukiii1223 , can you upload your csv file please as it isn’t in your workflow’s data folder (you may need to rename it to .txt) . thanks

[yukiii1223]

SetA.txt (3.3 MB)
Hello, sorry for disturbing you so late.

[takbb]

OK, no problem and thanks for uploading the data. So do you just want the temperature and light sensor readings pulled out into two separate files or do you want these values each put into their own columns (one for light and one for temperature) but with the other records remaining in place?

[yukiii1223]

I would like these values each put into their own columns (one for light and one for temperature) but with the other records (ON/OFF,OPEN/CLOSE) remaining in place!
Thank you so much for help!

[takbb]

Hi @yukiii1223 ,

In the attached workflow I’ve used a CSV Reader instead of the File Reader. My main reason for changing was that it is easier to configure it use a file from the workflow’s data folder for uploading the example, and also it is a dedicated node for reading delimited files, but your File Reader node can work just the same provided that it is able to detect the file format so that’s up to you which you use.

As your “value” column can contain both numeric and string data, it is imported as a String data type. I have used the Rule Engine twice to extract each of the values into a new column, but only where the sensor ID starts with T (temperature) and LS (Light Sensor).

In both cases I did not include a “default outcome” so there was no final
TRUE => "xxxxx" line or TRUE => "0" which means that where this is not a reading for Light Sensor or Temperature, the respective value will be set to ‘missing’ and appear as a ? in the column.
If you prefer, you could set it to “0” or “-1” or something to indicate that it is not a value for the sensor in question.

Finally, as you are requiring these as numeric values, I added two String to Number nodes. If they had both been the same data type, this could have been done with a single node, but in this case it turns to Temperature column to Double, and the Light Sensor column to Integer.

(Incidentally, I did notice that in your data both Temperature and Light appear to actually contain integer data, but I’ve left it as per your requirement)

extract to new columns.knwf (375.8 KB)

I hope that helps.

[yukiii1223]

Thank You so much! It is very helpful!