Fast way to replace value in sparse dataframe with value from dictionary

Question

I have a very sparse dataframe df such as this:

       Apples Bananas Pineapple Mango
Mary   Apples     NaN       NaN   NaN
Jane      NaN Bananas       NaN   NaN
Diego     NaN     NaN       NaN Mango
Guido     NaN     NaN Pineapple   NaN

and I would like to build a dictionary d such as

d = {'Apples':3, 'Bananas':1, 'Pineapple':2, 'Mango': 15}

to obtain

       Apples Bananas Pineapple Mango
Mary        3     NaN       NaN   NaN
Jane      NaN       1       NaN   NaN
Diego     NaN     NaN       NaN    15
Guido     NaN     NaN         2   NaN

I can do

df.to_sparse().replace(d)

but it's been over 30' and no output yet. My dataframe has 10000 rows times 1500 columns, the dataframe is originally 135MB, that becomes 850kB after to_sparse(). Is there any faster way?

Hi all, clearly replace(d) is not working for me. I am reopening the question with focus on an alternative, more performing solution than replace(d) — famargar, Jan 03 '18 at 12:37
@famargar - If it is very sparse, use `df = df.stack().map(d).unstack()` — jezrael, Jan 03 '18 at 12:37
@cᴏʟᴅsᴘᴇᴇᴅ - can you reopen this? Or can I reopen? — jezrael, Jan 03 '18 at 12:47
@jezrael It is a dupe, so answer [here](https://stackoverflow.com/q/20250771/2901002), also with timings if you like. — cs95, Jan 03 '18 at 12:48
Thank you all! I guess we should clean up the comments after the edits. Removing mines — famargar, Jan 03 '18 at 13:09

score 1 · Accepted Answer · answered Jan 03 '18 at 13:03

EDIT by changed question - you can use stack for Series with MultiIndex - (NaNs values are removed) - with map and then for reshape back unstack:

np.random.seed(1235)
N = 1000
d = {'Apples':3, 'Bananas':1, 'Pineapple':2, 'Mango': 15}
df = pd.DataFrame(np.random.choice(list(d.keys()) + [np.nan], 
                  size=(N, N), 
                  p=(0.01,0.02,0.03,0.02,0.92)))
#print (df)

In [227]: %timeit df.replace(d)
1 loop, best of 3: 661 ms per loop

In [228]: %timeit df.stack().map(d).unstack()
1 loop, best of 3: 381 ms per loop

Fast way to replace value in sparse dataframe with value from dictionary

1 Answers1