Pandas: Fill in missing values with an empty numpy array

Question

I have a Pandas Dataframe that I derive from a process like this:

df1 = pd.DataFrame({'c1':['A','B','C','D','E'],'c2':[1,2,3,4,5]}) df2 = pd.DataFrame({'c1':['A','B','C'],'c2':[1,2,3],'c3': [np.array((1,2,3,4,5,6)),np.array((6,7,8,9,10,11)),np.full((6,),np.nan)]}) df3 = df1.merge(df2,how='left',on=['c1','c2']) 

This looks like this:

c1	c2	c3
A	1	[1,2,3,4,5,6]
B	2	[6,7,8,9,10,11]
C	3	[nan,nan,nan,nan,nan,nan]
D	4	NaN
E	5	NaN

In order to run the next step of my code, I need all of the arrays in c3 to have a consistent length. For the inputs coming in that were present in the join (i.e. row 1 through 3) this was already taken care of. However, for the rows that were missing from df2 where I now have only a single NaN value (rows 4 and 5) I need to replace those NaN's with an array of NaN values like in row 3. The problem is that I can't figure out how to do that.

I've tried a number of things, starting with the obvious:

df3.loc[pd.isnull(df3.c3),'c3'] = np.full((6,),np.nan) 

Which gave me a

ValueError: Must have equal len keys and value when setting with an iterable 

Fair enough; I understand this error and why python is confused about what I'm trying to do. How about this?

for i in df3.index: df3.at[i,'c3'] = np.full((6,),np.nan) if all(pd.isnull(df3.c3)) else df3.c3 

That code runs without error but then when I go to print out df3 (or use it) I get this error:

RecursionError: maximum recursion depth exceeded 

That one I don't understand, but moving on, what if I preassign a column full of my NaN arrays and then I can do some logic after the join:

for i in df1.index: df1.at[i,'c4'] = np.full((6,),np.nan) 

This gives me the understandable error:

ValueError: setting an array element with a sequence 

How about another variation of the same idea:

df1['c4'] = np.full((6,),np.nan) 

This one gives a different, also understandable error:

ValueError: Length of values (6) does not match length of index (5) 

Hence, the question: How do I replace values in my dataframe (in this case null values) with an empty numpy array of a given length?

For clarity, the desired final result is this:

c1	c2	c3
A	1	[1,2,3,4,5,6]
B	2	[6,7,8,9,10,11]
C	3	[nan,nan,nan,nan,nan,nan]
D	4	[nan,nan,nan,nan,nan,nan]
E	5	[nan,nan,nan,nan,nan,nan]

Answer 1

A possible solution:

# the array with the 6 nan values arr_nan = np.full( df3['c3'].map( lambda x: np.size(x) if isinstance(x, np.ndarray) else 0).max(), np.nan) df3.assign(c3 = df3['c3'].map( lambda y: arr_nan if not isinstance(y, np.ndarray) else y)) 

This solution first determines the length of the arrays in c3, and then replaces all non-array entries in c3 by the array of 6 np.nan.

Output:

 c1 c2 c3 0 A 1 [1, 2, 3, 4, 5, 6] 1 B 2 [6, 7, 8, 9, 10, 11] 2 C 3 [nan, nan, nan, nan, nan, nan] 3 D 4 [nan, nan, nan, nan, nan, nan] 4 E 5 [nan, nan, nan, nan, nan, nan] 

Answer 2

Get the index of the rows where you have na values, and create a Series with an equal amount of rows, and with the same index.

idx = df3[df3['c3'].isna()].index df3.loc[idx, 'c3'] = pd.Series([np.full((6,), np.nan)] * len(idx), index=idx) 

End result:

c1 c2 c3 A 1 [1, 2, 3, 4, 5, 6] B 2 [6, 7, 8, 9, 10, 11] C 3 [nan, nan, nan, nan, nan, nan] D 4 [nan, nan, nan, nan, nan, nan] E 5 [nan, nan, nan, nan, nan, nan]