multiply each group by a specific value from a list in python groupby

user_8042888 0
Viewed 487

I have the following data frame and a list.

d = ({
    'Item' : ['a','a','a','b','b','b','b'],                 
    'Count' : [0,1,2,3,4,5,6],                                      
    })

df = pd.DataFrame(data = d)

list_factor=[10,100] # a list of factors with same length as the unique Items in dataframe.

Now I would like for each item, multiply the count by list_factor element correspond to that item. For example all counts in a will be multiplied by 10 and all counts in b will be multiplied by 100. So the final data frame will looks like this:


'Item' : ['a','a','a','b','b','b','b'],                 
'Count' : [0,10,20,300,400,500,600],

I tried to use groupby function and apply a multiplication function to it, but I can do it only for a constant value, not a value from the list. Here is what I did:

df.groupby('Item')['Count'].apply(lambda x : x*list_factor[?!])

This is just an example data frame and what I want to reach. In reality I have a data frame with 1000 different Items and as a result a list_factor with length of 1000. (The data frame has total rows of 40000)

pandas-groupby pandas python-3.x
2 Answers

You can use factorize to extract the order of Item, then numpy indexing to propagate the factor:

factors = np.array(list_factor)[df.Item.factorize()[0]]

df['Count'] *= factors

Output:

  Item  Count
0    a      0
1    a     10
2    a     20
3    b    300
4    b    400
5    b    500
6    b    600
user_42384080

Quang Hoang's answer is interesting- I wasn't familiar with factorize. Another approach would be to create a dictionary:

item_values = df['Item'].unique()
factor_dict = {item_values[i]: list_factor[i] for i in range(len(item_values))}


df['Count'] = df.apply(lambda x: x['Count'] * factor_dict.get(x['Item']), axis=1)

Result:

  Item  Count
0    a      0
1    a     10
2    a     20
3    b    300
4    b    400
5    b    500
6    b    600
user_134125670
Related