Stack Exchange
Pandas dependent columns lookup
I have a dataset that has 2 conditions, 2 replicates and samples with corresponding values (amounts). I read this into a pandas dataframe:
   condition  replicate sample  amount
0          1          1     a1       5
1          1          1     a2       2
2          1          2     a1       3
3          1          2     a2       1
4          2          1    b99       7
5          2          1     a2       4
6          2          2     a1       3
7          2          2     a2       2

I want to divide the amount from every sample in condition 1, by the amount from the corresponding sample in condition 2, if they belong to the same replicate (and have the same sample name).
In other words, I want to find the ratio between the amounts where the sample names and replicate numbers match between the conditions.
In this example, the output should be something like:
   replicate sample    amount
0          1     a1  0.714286
1          1     a2       NaN
2          2     a1  1.000000
3          2     a2  0.500000

I need advice if I should structure my data differently and if it is a good idea to go for pandas dataframes? Can anyone think of an elegant lookup solution?

You can use unstack for columns by conditions, then divide columns and last remove all NaNs rows by dropna:
df = df.set_index(['sample','replicate','condition'])['amount'].unstack()
df['new'] = df[1].div(df[2])

df = df['new'].unstack().dropna(how='all').stack(dropna=False).reset_index(name='amount')
print (df)
  sample  replicate  amount
0     a1          1     NaN
1     a1          2     1.0
2     a2          1     0.5
3     a2          2     0.5

