Home > Back-end >  How to unpack and map multi-valued dict into many Pandas columns?
How to unpack and map multi-valued dict into many Pandas columns?

Time:10-07

Following a similar thread I made previously, with many great answers, How to map multi-valued dict into multiple Pandas columns?, I am stuck as to how to deal with this problem if the multiple values are objects other than strings. How can I unpack the dictionary into multiple columns?

import numpy as np
import pandas as pd

# multi-valued dict
d1 = {67045: np.array([20200911, 20201113, 20201230, np.nan, np.nan]),
    67046: np.array([20190909, 2020102, 20201230, 1, np.nan])}

# df with key
df = pd.DataFrame({'key': [67045, 67046]})

# map to multiple cols -- fails
df[['c1', 'c2', 'c3', 'c4', 'c5']] = df['key'].map(d1)

# expected output
df = pd.DataFrame({'key': [67045, 67046],
                   'c1': [20200911, 20190909],
                   'c2': [20201113, 2020102],
                   'c3': [20201230, 20201230],
                   'c4': [np.nan, 1],
                   'c5': [np.nan, np.nan]})

    key c1  c2  c3  c4  c5
0   67045   20200911    20201113    20201230    NaN NaN
1   67046   20190909    2020102 20201230    1.0 NaN

CodePudding user response:

I suspect you need something like this:

d = pd.DataFrame.from_dict(d1, orient='index', columns = ['c1', 'c2', 'c3', 'c4', 'c5'])
df.merge(d, left_on='key', right_index = True)

     key          c1          c2          c3   c4  c5
0  67045  20200911.0  20201113.0  20201230.0  NaN NaN
1  67046  20190909.0   2020102.0  20201230.0  1.0 NaN

map works on series; for multiple columns; merge/join works as a good fit.

with @HenryEcker's comment, you can set how='left' to make it as close to a map syntax as possible.

Of course, I could be wrong and you could have some other shape in mind.

  • Related