From this DataFrame with name df
SAMPLETARGETCTTCT MEAN C41B2M20.64239920.680149C41B2M20.71790120.680149C42ULK129.110802C4229.110802C43TBP22.12641223TBP20.71790121,4221565
The Ct Mean column is the average of the two values of the Ct column of the same Sample and Target (this is given by default by the .xlsx that I imported into pandas).
My intention is to check if the difference between the two values of the Ct column for the same sample and target is not greater than +-1 two to two. For example, for C41 (B2M) the difference between 20.642399 and 20.717901 is less than 1, so it should return the value of Ct as is. Instead, for C43 (TBP) the difference between 22.126412 and 20.717901 is greater than 1 and replace the two Ct values for C43 with "Undetermined". The result that should give me would be this:
SampleTargetCtCt Mean C41B2M20.64239920.680149C41B2M20.71790120.680149C42ULK129.09788329.110802C42ULK129.12372229.110802C43TBPUndeterminedUndeterminedC43TBPUndetermined
I have tried in various ways to make a subtraction between two elements of the same column of a dataframe but I have not been able to. The first was to apply a loop for that column that would make the difference between the two values by making a jump so that it would then do the following two:
def loop(i): for i in range(0,96,2): if i-(i+1)>1 or i-(i+1)<(-1): i=="Undetermined" else: return i prueba = df["Ct"].apply(loop) pruebaPrint:
0 0 1 0 2 0 3 0 4 0 .. 91 0 92 0 93 0 94 0 95 0 Name: Ct, Length: 96, dtype: int64 NOTE* My dataframe has 96 rows. I have only put a head with the first 6 for the example. When printing, it gives me all 0. I have been searching and I saw that there is a .diff method that allows subtracting the value of an element minus the value of the previous element, but I don't know how to apply it. Another way I thought is to use:
df["Ct"].sub(df[0,len(df),2], axis=0)Obviously it gives an error and the syntax is not correct either.
def myfunc(g): if any(g.Ct.diff().abs()>1): g["Ct Mean"] = "Undetermined" return g df = df.groupby(["Sample", "Target"]).apply(myfunc) If df initially contains:
Sample Target Ct Ct Mean 0 C41 B2M 20.642399 20.680149 1 C41 B2M 20.717901 20.680149 2 C42 ULK1 29.097883 29.110802 3 C42 ULK1 29.123722 29.110802 4 C43 TBP 22.126412 21,4221565 5 C43 TBP 20.717901 21,4221565the result of the above code produces:
Sample Target Ct Ct Mean 0 C41 B2M 20.642399 20.680149 1 C41 B2M 20.717901 20.680149 2 C42 ULK1 29.097883 29.110802 3 C42 ULK1 29.123722 29.110802 4 C43 TBP 22.126412 Undetermined 5 C43 TBP 20.717901 UndeterminedAs you can see, everything is resolved in one line:
df.groupby(["Sample", "Target"]).apply(myfunc) What this does is to group the dataframe by "Sample" and "Target" so that it gathers in several "sub-dataframes" the rows that have the same value in "Sample" and "Target". myfunc is applied to each of the resulting "sub-dataframes" (hereafter called "groups").
This function therefore receives in its g parameter a group, which is actually a dataframe but "filtered" so that it has only a couple of elements with the same Sample and Target, at least in this case they are only a couple of elements. More generally, they receive a dataframe with an arbitrary number of rows, with the same columns as the df , and with the same value of "Sample" and "Target" in all rows.
What the function does is determine if in that group the value of the "Ct Mean" column should be changed to put "Undetermined" or if it should be left as it was. Then it returns the group in question, so that .apply() concatenates it again with the remaining groups to create the dataframe with the result.
The key to determining whether or not to put "Undetermined" is the following line:
if any(g.Ct.diff().abs()>1): g.Ct is the Ct column of the received group. When applying .diff() is subtracted from each element in that column. The first one does not have a previous one, so the result is NaN, but in the following ones the result will be the difference. Thus, we have a column of differences. .abs() is applied to that column to keep the absolute value so that the sign does not influence. Therefore, there is a column of numbers (the first of them NaN).
The column is compared with >1 which gives us a new column but this time with booleans. For each element of the difference that is greater than 1, there will be a True (and the rest will be False ). The first one that is NaN will always False In your case there will only be one more element (because there are only two rows in each group), but in general we could have a column with many booleans.
That column is passed to any() which returns True if there is at least one True among the elements. Only if they are all False return False .
The result is that if among the values returned by .diff() there is one greater than 1, the if be executed and then it will do:
g["Ct Mean"] = "Undetermined" which assigns that value to the entire column, that is, to all rows in that group. If the if not true, g not touched.
Finally the function returns g (whether it has been modified or not).