I have a pandas df where each column have some numerical values, followed by some NaNs. The number of values and NaNs differ between columns. I want to append a single value at 'first non-NaN position' in a specific column.
My pandas df looks something like this:
A B C D
0 5 7 2 3
1 2 1 NaN 4
2 4 6 NaN 5
3 NaN 4 NaN 6
4 NaN 3 NaN NaN
5 NaN NaN NaN NaN
I want to add ("append") a value at the bottom of a specific column, for instance, I want to change the first 'NaN' in column 'A' to the value '3'. The desired outcome should be:
A B C D
0 5 7 2 3
1 2 1 NaN 4
2 4 6 NaN 5
3 3 4 NaN 6
4 NaN 3 NaN NaN
5 NaN NaN NaN NaN
I would like to use 'append', but it's deprecated. I have tried 'concat', but I don't want a whole new row, just append a single value at the bottom of a single column.
You can use Series.fillna with limit=1:
df['A'] = df['A'].fillna(3, limit=1)
Output:
A B C D
0 5.0 7.0 2.0 3.0
1 2.0 1.0 NaN 4.0
2 4.0 6.0 NaN 5.0
3 3.0 4.0 NaN 6.0
4 NaN 3.0 NaN NaN
5 NaN NaN NaN NaN
An alternative could be to use Series.isna + Series.idxmax and assign via df.loc. Here you first want to check if isna results in any True values. If not, you will overwrite the first value in the column:
if df['A'].isna().any():
df.loc[df['A'].isna().idxmax(), 'A'] = 3