Replacing some dataframe values with NaN

Question

I've edited this question a bit to clarify things. I have a dataframe like this:

ID (index col)   1   1   1   1   1   2   2   2   2   2   3   3   3   3   3

where the ID column is strings but the rest of the df is floats. Like this question, my goal is to make a new dataframe that has 3 columns, where each column is an average of the 3 from the original dataframe, like this:

ID (index)    1avg   2avg   3avg

And that question handles that grouby call, but before I do that I want to check to make sure that in each row, there is a real value in at least 2/3 of each subgroup. So I need to check for things like this:

ID   1      1       1        1        1     2   2   2   2   2  3   3   3...
a   0.0005  0.0005  0.0005  0.0005  0.0005  7   5   2   19  5  18  9   20
b   0.0005  25      0.0005  0.0005    85    5   2   1   24  2  17  10  6
c   92      42      12      0.0005    15    1   2   5   52  2  3   5   7 
d   25      35      11      81        12    5   6   8   2   6  23  3   5

The program (commercial) generating the data replaces things that are really missing values with 0.0005, which is also tricky to replace with nans anyway.

What I really need is a way to handle

1) For rows with NaNs in all subgroup columns (row a above), nothing should happen but I can't drop the NaNs because I need to keep the same number of rows across groups. When I average all NaNs, the average will stay NaN which is ideal.

2) For rows with NaNs in at least 3 columns, (row b above), I need to convert 25 and 85 into NaN so that when I take the average it will be NaN but the row will stay and the other groups who have values will be unaffected.

3) for rows with 1 or 2 NaNs (row c above) or rows with values in all columns (row d above) they should stay as is to get the average value.

ShellieJ ShellieJ · Accepted Answer · 2020-04-29T01:41:11

Ok I ended up finding a solution with iterrows but I'm still interested to know if anyone can suggest a better/more correct way.

concentrations = ['1','2','3'...]
for k in concentrations:
tf = df[k]

for index,row in tf.iterrows():
    counter = 0
    for item in row:
        if math.isnan(item) == True:
            counter = counter+1

        if counter >2: #where 2 is the number of NaNs I'll let remain before I replace the row with NaNs
            tf.at[index] = np.nan

#also pretty unclear if all of this is necessary but it works
tfnew = tf.groupby(by=tf.columns, axis=1).apply(lambda g: g.mean(axis=1) if isinstance(g.iloc[0,0], numbers.Number) else g.iloc[:,0])           
ultra[k] = tfnew[k]

Replacing some dataframe values with NaN

1 Answers