How do I remap a dataframe with a dict containing nested list using map function?-CodePudding

I have a df like this:

I wanna add a new column with map function using dict d

d = {'123': [1, 2, 3, 1, 2, 1], '456': [1, 2, 1]}

Expected output:

    ID  Count
0   123   1
1   123   2
2   123   3
3   123   1
4   123   2
5   123   1
6   456   1
7   456   2
8   456   1

But df.ID.map(d) returns

0    [1, 2, 3, 1, 2, 1]
1    [1, 2, 3, 1, 2, 1]
2    [1, 2, 3, 1, 2, 1]
3    [1, 2, 3, 1, 2, 1]
4    [1, 2, 3, 1, 2, 1]
5    [1, 2, 3, 1, 2, 1]
6             [1, 2, 1]
7             [1, 2, 1]
8             [1, 2, 1]

Thanks in advance!

CodePudding user response：

You can use groupby apply:

df.groupby('ID').apply(lambda g: pd.Series(d[g.name]))

Example:

>>> df['Count'] = df.groupby('ID').apply(lambda g: pd.Series(d[g.name])).to_list()
>>> df
    ID  Count
0  123      1
1  123      2
2  123      3
3  123      1
4  123      2
5  123      1
6  456      1
7  456      2
8  456      1

CodePudding user response：

From you dict you can get what you need after explode

pd.Series(d).explode().reset_index()
Out[115]: 
  index  0
0   123  1
1   123  2
2   123  3
3   123  1
4   123  2
5   123  1
6   456  1
7   456  2
8   456  1