search

paviaishu16 / Multiplate-reader-data-analyser

0 stars 0 forks source link

Data Cleanup #3

Closed paviaishu16 closed 1 month ago

paviaishu16 commented 1 month ago

The formula's are written based on the data in below table, Raw Data:

1 | Time (Cell A) | Sample (Cell B) | 2 | 0,5 | 10 | 3 | 1,0 | 32 | 4 | 1,5 | 52 | 5 | 2,0 | 16 | 6 | 2,5 | 85 |

Normalized Value = (Current Value - Minimum value )/(Maximum value - Minimum value )

In excel: B2 of Normalized Data =('Raw Data'!B2-MIN('Raw Data'!B$2:B$6))/(MAX('Raw Data'!B$2:B$6)-MIN('Raw Data'!B$2:B$6))

LOESS = (0,2 value) + (0,8 Previous value)

While performing this step, data in the second row i.e., at 0,5 hours will remain same as the data obtained from normalized data. The formula will applied only from third row.

In excel: B2 of LOESS Data ='Normalized data'!B2 C2 of LOESS Data =0,2'Normalized data'!B3+0,8'LOESS -Normalized data'!B2

@gevhaz We might have to discuss this step as it requires changing the column titles with the sample name. I was thinking may be we could have two rows for column names. Say the first row would be A1 A2 A3 etc., which are unique and the second would have the actual sample names. (This could be implemented either part of first issue along with the time or at this step)

Once you do the above change then you will have to subtract the averaged blank value from individual columns i.e., AVERGAEIF the label in the second row is named as BLK. AVERAGEIF is a function is excel, there should be something similar in python as well.

Blanked Data = Value - Average(BLK) In the sample table, usually blank values are labelled as BLK

In excel: B2 of Blanked Data ='LOESS Data'!B2-AVERAGE('LOESS Data'!$AG2;'LOESS Data'!$AO2;'LOESS Data'!$AW2)

Here AG, AO and AW are the columns with the value obtained from blank wells.

The aim of this step is to convert all the value into positive values by adding the minimum value of the column to every cell of that column and if the value returned is zero then we change it to 0,00001.

Normalized Blanked Data = If (Value - Minimum value)=0, return 0,00001 or return (Value - Minimum value)

In Excel: B2 of Normalized Blanked Data = If(('Blanked Data'!B2-MIN('Blanked Data'!B$2:B$6))=0; 0,00001; ('Blanked Data'!B2-MIN('Blanked Data'!B$2:B$6))

gevhaz commented 1 month ago

All tasks completed. Resolving.