I have a dataframe called All_samp where I want to change entries in a column to the lowest value if other entries in that same row match entries in a different row. For example, I have this dataframe
| index | chromosome | start | sample | no_calls |
|---|---|---|---|---|
| 22 | chr1 | 190098060 | 8.1 | 600 |
| 23 | chr1 | 190098060 | 9.1 | 858 |
| 24 | chr1 | 190098078 | 8.1 | 201 |
| 25 | chr1 | 190098093 | 8.1 | 250 |
| 26 | chr1 | 190098093 | 8.1 | 32 |
| 27 | chr1 | 190098093 | 8.1 | 271 |
| 28 | chr1 | 190098119 | 8.1 | 288 |
| 29 | chr1 | 190098123 | 10.1 | 146 |
| 30 | chr1 | 190098123 | 10.1 | 307 |
| 31 | chr10 | 190098123 | 8.1 | 366 |
| 32 | chr1 | 190098160 | 8.1 | 298 |
If chromosome, start, and sample match are the same, then I want no_calls to be the minimum value of the matching rows. Therefore, this is the result I am looking for:
| index | chromosome | start | sample | no_calls |
|---|---|---|---|---|
| 22 | chr1 | 190098060 | 8.1 | 600 |
| 23 | chr1 | 190098060 | 9.1 | 858 |
| 24 | chr1 | 190098078 | 8.1 | 201 |
| 25 | chr1 | 190098093 | 8.1 | 32 |
| 26 | chr1 | 190098093 | 8.1 | 32 |
| 27 | chr1 | 190098093 | 8.1 | 32 |
| 28 | chr1 | 190098119 | 8.1 | 288 |
| 29 | chr1 | 190098123 | 10.1 | 146 |
| 30 | chr1 | 190098123 | 10.1 | 146 |
| 31 | chr10 | 190098123 | 10.1 | 366 |
| 32 | chr1 | 190098160 | 8.1 | 298 |
I tried to do this was by making a series of the no_calls column using itertuples and a nested loop to make the changes to the entries. With this, I should be able to replace the no_calls column with the amended series. This is what my code looked like
no_calls = []
for row1 in All_samp.itertuples():
for row2 in All_samp.itertuples():
if row1[0] != row2[0] and (row1[1] == row2[1] and row1[2] == row2[2] and row1[3] == row2[3]):
print(row1[0], row2[0], row1[4], row2[4], min(row1[4], row2[4]))
no_calls.append(min(row1[4], row2[4]))
break
else:
no_calls.append(row1[4])
break
The result is simply giving me a list of the original no_calls entries, and I imagine it's because my 'if' statement is only going through the first iteration instead of cycling through all rows. Once I get the loop to work, I will replace the column with the list with
All_samp['no_calls'] = no_calls
If there are any ideas how to help me fix my loop or even a completely different way to get the minimum no_calls values in my dataframe (I am certain there is something better than the loops, I would greatly appreciate it.
Also, if there is a way that I can present the dataframes other than as a table on StackOverflow such that it's easier to work with them directly, please let me know.