Pandas DataFrame Rollup Error

Question

I am trying to compute Percentage Contributions on a pandas dataframe. I have a data frame which has three columns (Buyer, Product, CountofProductBought).

I create a new dataframe using groupby at Buyer level and using sum on the Count of Products Bought. I use the as_index=False.

Finally, I merge this data frame with the original data frame to compute the % [count of Product Bought/ SUM of count of Product Bought].

I get the following error when I call pd.merge:

The column label 'Buyer' is not unique. For a multi-index, the label must be a tuple with elements corresponding to each level.

Please help as there is no concrete help on this.

Answer 1

From my understanding you have dataframe containing a list of Buyer ID's, the product they bought and how many of it they bought. You want to find out what percentage of the total of each product bought each customer was responsible.

For instance if buyer 1 bought all of A then they would 100% and if buyer 2 bought half of all B then they would be 50%.

Each buyer only buys one type of product at a time.

I generated a dataset to replicate this as so:

options = 'ABCDEF'

product = []
count = []
buyer = []

for i in range(0,100):
    product.append(test[np.random.randint(0,5)])
    count.append(np.random.randint(0,10))
    buyer.append(i)

df = pd.DataFrame(data = [buyer, product, count]).transpose()

df.columns = ['buyer','product','count']
print(df.head())
>>> buyer product count
0     0       B     4
1     1       B     6
2     2       A     2
3     3       D     2
4     4       D     5

In order to calculate each customers percentage you will need the total count of each product bought

totals = {'A' : 0,
         'B' : 0,
         'C' : 0,
         'D' : 0,
         'E' : 0,
         'F' : 0}

for i in range(0, len(df)):
    currentProduct = df.iloc[i,1]
    totals[currentProduct] = totals[currentProduct] + 1

Now all you need to do is calculate count/total for each product and save to a new column in the dataframe.

for i in range(0, len(df)): # Iterate over every row in 
    # new column | count bought | total bought by all
    df.iloc[i,3] = df['count'][i]/totals[df['product'][i]]
print(df.head())
>>> buyer product count  contribution
0     0       B     4      0.210526
1     1       B     6      0.315789
2     2       A     2      0.080000
3     3       D     2      0.111111
4     4       D     5      0.277778
print(totals)
>>> {'A': 25, 'B': 19, 'C': 15, 'D': 18, 'E': 23, 'F': 0}

I know it's not the same method that you where using but it should still work, if you want please post a sample of your code so that we can look bugs, alternatively just tick this answer and change the title to represent the question that was answered.

If i misunderstood anything just say so