How to alter values in multiple columns based on values in other columns within a loop

Will your samples continue to repeat in new columns or will they be appended to subsequent rows? For example, if you have two more samples should we expect they will occupy new columns such as 03_s_IDX_type and 03_s_IDX, 04_s_IDX_type and 04_s_IDX, and so on? Or would these new samples appear as a new row underneath your existing row?

Assuming the former (that is, that each new sample is associated with two new columns), here's a solution I came up with. I know you said you wanted a loop, but this actually avoids looping and therefore should be faster and more efficient.

First, let's build the dataframe using the values you supplied.

df = {'01_s_IDX_type':  ['HET'],
    '01_s_IDX': ['0/1:10,9:19:99:202,0,244'],
    '02_s_IDX_type': ['REF'],
    '02_s_IDX': ['0/1:18,1:19:99:202,0,244']
    }

ratio_bounds = (0.7, 1.3)

df = pd.DataFrame(df)

Next, if we can assume that each new sample abides by the same naming conventions, we can extract each column that contains similar data. This means that we can create a single dataframe that contains a column for the chromosomal data and use this to evaluate the genotype later on. We can also split the names of these to extract the sample ID number.

chro_vals = df[df.columns[1::2]]
idxs = [i.split('_')[0] for i in chro_vals]

Now, we're going to build a new dataframe from this simplified and restructured data. Since we don't need the old dataframe anymore, I'm just going to overwrite it by making a new dataframe with the same variable name. Also, using some of the tricks that you used in your first iteration, we can extract the experimental values that you are evaluating. I'll put them in two columns called Val1 and Val2.

df = pd.DataFrame(columns=['Raw_values'], index=idxs, data=chro_vals.values.T)
df['Ratio_vals'] = df.Raw_values.str.split(pat=':', expand=True)[1]
df[['Val1', 'Val2']] = df.Ratio_vals.str.split(pat=',', expand=True).astype(int)

Now we can compute the ratio that you're interested in by dividing these columns. Once that's done, we can define a simple function that will be passed to df.apply() to label the genotype of each of your samples.

def get_genotype(row):
    if row['Observed_Ratio'] == 0:
        return 'HOM'
    elif row['Observed_Ratio'] > ratio_bounds[0] and row['Observed_Ratio'] < ratio_bounds[1]:
        return 'REF'
    else:
        return 'HET'


df['Genotype'] = df.apply(lambda row: get_genotype(row), axis=1)
print(df)

If you want, you could subset the dataframe here to make it look a bit cleaner, but other than that you should have everything you need:

                  Raw_values Ratio_vals  Val1  Val2  Observed_Ratio Genotype
01  0/1:10,9:19:99:202,0,244       10,9    10     9        1.111111      REF
02  0/1:18,1:19:99:202,0,244       18,1    18     1       18.000000      HET

Here is an updated answer to generalize to multiple rows. This problem may be trickier but I think I found a solution. It involves first concatenating all of your raw IDX values to a single column in a dataframe, then after your genotypes are evaluated, the dataframe is transformed into more or less its original shape.

First, similar definitions as above, this time incorporating the updated dataframe values.

import pandas as pd

df = {'01_s_IDX_type':  ['HET', 'HOM'],
    '01_s_IDX': ['0/1:10,9:19:99:202,0,244', '0/1:20,0:20:99:202,0,244'],
    '02_s_IDX_type': ['REF', 'HOM'],
    '02_s_IDX': ['0/1:18,1:19:99:202,0,244', '0/1:0,50:50:99:202,0,244']
    }

ratio_bounds = (0.7, 1.3)

df = pd.DataFrame(df)

Here I think it's useful to label each row. I chose to call them "runs" but you might have some other more informative name. Then I separated the two columns associated with sample 2 to a separate dataframe and renamed those columns to match sample 1 - this will allow us to concatenate the dataframes with no issues.

df.index = [f'Run{i+1}' for i in range(df.shape[0])]
df_renamed = df[['02_s_IDX', '02_s_IDX_type']].rename(columns={'02_s_IDX': '01_s_IDX',
                                                               '02_s_IDX_type': '01_s_IDX_type'})

df = pd.concat([df[['01_s_IDX', '01_s_IDX_type']], df_renamed], axis=0).loc[:, ['01_s_IDX']]

By now we've created a dataframe with a single column representing the IDX values, which we will use to evaluate the genotypes in the same way as before.

df.columns = ['IDX']

df['Ratio_vals'] = df.IDX.str.split(pat=':', expand=True)[1]
df[['Val1', 'Val2']] = df.Ratio_vals.str.split(pat=',', expand=True).astype(int)

df['Observed_Ratio'] = df['Val1'] / df['Val2']


def get_genotype(row):
    if row['Observed_Ratio'] == 0:
        return 'HOM'
    elif row['Observed_Ratio'] > ratio_bounds[0] and row['Observed_Ratio'] < ratio_bounds[1]:
        return 'REF'
    else:
        return 'HET'


df['Genotype'] = df.apply(lambda row: get_genotype(row), axis=1)

Finally, we can just merge these dataframes together using the index values (i.e. the "runs" that we defined above) as a basis to merge on. Since the columns are identical, we can pass suffixes for the left and right dataframes.

df = df.iloc[:int(df.shape[0] / 2), :].merge(df.iloc[int(df.shape[0] / 2):, :], left_index=True,
                                             right_index=True, suffixes=('_01', '_02'))

print(df)

If you inspect this output you will see you end up with a dataframe of shape (2, 12). From here you can rename the columns as you wish or subset the dataframe to clean it up further. Hope this helps!