Replacing each Null value in dataframe with a list

Question

I am trying to replace each instance of null value by a different value each time. The values are in a list CB.

import pandas as pd
import numpy as np
df = pd.DataFrame({'a': 0, 'b': np.NaN, 'c':np.NaN, 'd': np.NaN}, index = [0])
CB = [1, 2, 3]
print(df)

Result is:

   a   b   c   d
0  0 NaN NaN NaN

Expected output:

   a   b   c   d
0  0   1   2   3

Will the size of the list always match the amount of `nan`s? — Tomerikoo, Dec 03 '20 at 17:05
Does this answer your question? [How to replace NaN values by Zeroes in a column of a Pandas Dataframe?](https://stackoverflow.com/questions/13295735/how-to-replace-nan-values-by-zeroes-in-a-column-of-a-pandas-dataframe) — Tomerikoo, Dec 03 '20 at 17:08
What if `a` has null. Is that to be ignored? What if only `b` has null. Can you add those as inputs in your question pls — Joe Ferndz, Dec 03 '20 at 20:47

Mayank Porwal · Answer 1 · 2020-12-04T07:09:09.630

1

Are you looking for this:

In [4422]: df[df.columns[df.isna().any()]] = CB

In [4423]: df
Out[4423]: 
   a    b    c    d
0  0  1.0  2.0  3.0

EDIT:

Consider below df:

In [4621]: df
Out[4621]: 
   a    b    c    d
0  0  4.0  5.0  6.0
1  0  NaN  NaN  NaN
2  0  NaN  NaN  NaN

If you want to change NaN values in only last row, do this:

Get the column names which have NaN in last row:

In [4623]: cols = df.columns[df.iloc[-1].isna()]
In [4631]: cols
Out[4631]: Index(['b', 'c', 'd'], dtype='object')

Now, set the values for these cols from list CB:

In [4631]: CB = [1, 2, 3]

In [4628]: df.loc[df.index[-1], cols] = CB

In [4629]: df
Out[4629]: 
   a    b    c    d
0  0  4.0  5.0  6.0
1  0  NaN  NaN  NaN
2  0  1.0  2.0  3.0

edited Dec 04 '20 at 07:09

answered Dec 03 '20 at 17:14

Mayank Porwal

33,470
8
37
58

What if I had many rows in my DataFrame and I only wanted to change the final row which contains NaNs? – Youssef El Sayed Dec 03 '20 at 19:14
@YoussefElSayed then update your question to reflect that with a proper [mre] – Tomerikoo Dec 03 '20 at 19:32
@YoussefElSayed I've updated my answer. Please have a look. – Mayank Porwal Dec 04 '20 at 07:09

score 1 · Answer 2 · answered Dec 03 '20 at 19:43

Let's say we have a dataframe (making it a bit bigger that the original question)

df = pd.read_csv(StringIO(
"""
   a   b   c   d
0  0   4   5   6
1  0 NaN NaN NaN
"""), delim_whitespace=True)

then we can do the following

dfs = df.stack(dropna = False)
dfs[dfs.isna()] = CB
df = dfs.unstack()
df

produces

    a   b   c   d
0   0.0 4.0 5.0 6.0
1   0.0 1.0 2.0 3.0

Here we unwrap the df into a timeseries using stack(), filter to NaNs, replace with CB, and fold back into the original shape

Replacing each Null value in dataframe with a list

2 Answers2

EDIT: