How to replace Nan of the dataframe with the values of the series?

Question

I have a series and dataframe with 14 columns and I need to replace the dataframe value with the series value if dataframe has Nan value. (row by row)

average= ref_series.iloc[:,len_of_ref_series[0]-wks:len_of_ref_series[0]].mean(axis=1)

ref_series = ref_series.fillna(average, axis='index')

print(ref_series)

Average is a series:

0    360.000000
1    400.000000
2    386.666667

ref_series is a dataframe:

W01  W02  W03  W04  W05  W06  W07  W08  W09  W10  W11  W12  W13    QTR
0  120  240  360  480  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN
1  120  240  360  600  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN
2  110  220  500  440  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN

my expected result :

W01  W02  W03  W04  W05  W06  W07  W08  W09  W10  W11  W12  W13  QTR
0  120  240  360  480  360  360  360  360  360  360  360  360  360  360
1  120  240  360  600  400  400  400  400  400  400  400  400  400  400
2  110  220  500  440  386  386  386  386  386  386  386  386  386  386

cs95 · Accepted Answer · 2019-04-18 19:25:42Z

1

Perform an isna check to determine which columns have NaNs in every row.

df.columns[df.isna().all(axis=0)]
# Index(['W05', 'W06', 'W07', 'W08', 'W09', 
#        'W10', 'W11', 'W12', 'W13', 'QTR'], dtype='object')

You can use this result to assign the means to those columns using df.assign:

df = df.assign(**dict.fromkeys(df.columns[df.isna().all(axis=0)], s))

Or, as integer columns,

df.assign(**dict.fromkeys(df.columns[df.isna().all(axis=0)], s.astype(int)))

   W01  W02  W03  W04  W05  W06  W07  W08  W09  W10  W11  W12  W13  QTR
0  120  240  360  480  360  360  360  360  360  360  360  360  360  360
1  120  240  360  600  400  400  400  400  400  400  400  400  400  400
2  110  220  500  440  386  386  386  386  386  386  386  386  386  386

answered Apr 18, 2019 at 19:25

cs95

406k106 gold badges744 silver badges797 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ganesh Shinde Over a year ago

hey, Thanks for your quick reply. You have explained everything above but as a beginner, it's confusing for me. how to assign that result and what is s?

cs95 Over a year ago

@GaneshShinde df.isna will check which cells are NaN, returning True/False. df.isna().all(axis=0) will then return a 1D boolean array of True/False for each column, True if every value in the column is NaN, and false otherwise. I then get the list of columns which satisfy this condition, then call df.assign to assign the series s to each column previously selected. Hope that helps. I suggest running this code and playing with it so you're super clear on what each step is doing.

Ganesh Shinde Over a year ago

Thanks. It works. ref_series=ref_series.assign(**dict.fromkeys(ref_series.columns[ref_series.isna().all(axis=0)], average.astype(int))). what is the use of performing isna check? I haven't use it.

Collectives™ on Stack Overflow

How to replace Nan of the dataframe with the values of the series?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related