How do I calculate the frequency of the ItemSold grouped by each ClientID and create the new column for that frequency?
My dataframe looks like this:
data = {'ClientId': ['1','2','3','4','2','2','1','4'],
'QuantitySold': ['5','10','6','7','5','10','8','7']
}
Expected Output:
Client Id QuantitySold FrequencySold
1 5 0.385
2 10 0.4
3 6 1
4 7 0.5
2 5 0.2
2 10 0.4
1 8 0.615
4 7 0.5
I have found a solution for the calculation but can't manage to create the new column FrequencySold.
Working code:
import collections
totals = collections.defaultdict(int)
for c, q in zip(data["ClientId"], data["QuantitySold"]):
totals[c] += int(q)
# defaultdict(int, {'1': 13, '2': 25, '3': 6, '4': 14})
for c, q in zip(data["ClientId"], data["QuantitySold"]):
print(c, q, int(q)/totals[c])
How do I create the new column based on the loop?
Thank you
You can try:
>>> data = {'ClientId': ['1','2','3','4','2','2','1','4'],
... 'QuantitySold': ['5','10','6','7','5','10','8','7']
... }
>>> df = pd.DataFrame(data)
>>>
>>> df['FrequencySold'] = df.groupby('ClientId')['QuantitySold'].apply(lambda x: x.astype(int) / x.astype(int).sum())
>>> df
ClientId QuantitySold FrequencySold
0 1 5 0.384615
1 2 10 0.400000
2 3 6 1.000000
3 4 7 0.500000
4 2 5 0.200000
5 2 10 0.400000
6 1 8 0.615385
7 4 7 0.500000