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

Admin 1
Viewed 120

I have a dataframe like this:

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

I want to add a new column with map function using dict d:

d = {'123': [1, 2, 3, 1, 2, 1, 5], '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
9   123   5

But df.ID.map(d) returns:

0    [1, 2, 3, 1, 2, 1, 5]
1    [1, 2, 3, 1, 2, 1, 5]
2    [1, 2, 3, 1, 2, 1, 5]
3    [1, 2, 3, 1, 2, 1, 5]
4    [1, 2, 3, 1, 2, 1, 5]
5    [1, 2, 3, 1, 2, 1, 5]
6             [1, 2, 1]
7             [1, 2, 1]
8             [1, 2, 1]
9    [1, 2, 3, 1, 2, 1, 5]
pandas series dataframe
3 Answers

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

edit. variant for unordered input:

(df.join(df.groupby('ID').apply(lambda g: pd.Series(d[g.name],
                                                    name='Count',
                                                    index=g.index))
           .droplevel(0))
)

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
9  123      5
user_163434640

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
user_79645270

Create a cumulative count on df -> the assumption here is that the count for each ID should be the same as the count of values for each ID in d :

df = df.assign(counter = df.groupby('ID').cumcount())

Build a dataframe from d, using pd.concat:

# converted the keys to integers
# so that it matches the dtype of ID in df
frame = pd.concat([pd.Series(val) for _, val in d.items()], 
                  keys = map(int, d))
frame.name = 'Count'

Run a pd.merge to align frame to df:

df.merge(frame, 
        left_on = ['ID', 'counter'], 
        right_index = True).drop(columns='counter')

    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
9  123      5
user_71757130
Related