1

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
John Stud
  • 1,506
  • 23
  • 46

1 Answers1

2

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.

sammywemmy
  • 27,093
  • 4
  • 17
  • 31
  • 2
    I think `how="left"` or `join` on `df = df.join(d, on='key')` might be more inline with `map` than `inner` join via merge depending on desired output. – Henry Ecker Oct 07 '21 at 02:58