# Python and data analytics how to get the unique number of orders per seller using groupby?

I am totally new to data analytics and python.

I took a good course on Machine learning. But I didn't really worked with real life data.

I managed to get data from a store who wants to create a recommendation system. I convinced them, that I will learn and get experience, and you will get benefited with a recommendation system, plus some insights on your previous orders.

I've got a json file having the following structure (I transformed it into excel):

Each order_id is connected through one seller_id. And it may contain multiple product_id.

With that I mean, that a user could have orderd from the seller_id=1, 2 products and the data would be like this:

{
order_id: 12,
seller_id: 1,
product_id: 3,
...
},
{
order_id: 12,
seller_id: 1,
product_id: 89,
...
}


The main things I need to export from these data is the following:

1. Number of unique orders per seller;
2. Products that are being ordered the most per seller;
3. Products that are being ordered the most in general;
4. Sum of the quantity field for each product to check how many times has been order;
5. Number of orders for each customer;

And other things that I may come up with later.

I started with the number 1, wherre I need to get the unique number of orders per seller. I tried the following:

data.groupby('order_id')['seller_name'].value_counts().sort_values(ascending=False)


But I didn't get the right result. A seller having 110 unique orders, but using the line above, it shows he is having more than 500 orders.

Here is a snippet of the data available:

Even though, seller_id=1 having 4 rows, but the actual number of orders is uniquely 2, despite the fact that the quantities of items within these 2 orders are 7.

The same for seller_id=5, he is having 4 rows, but they are only for one single order.

So how can I get the unique number of orders of each seller as a start? I think I will figure the other points out once I get to do the first one.

Based on the information available above I think the solution below should work

# Number of unique orders per seller
a = pd.pivot_table(df, index = ['seller_id'], values = ['order_id'],
aggfunc = {'order_id' : pd.Series.nunique})

# Products that are being ordered the most per seller
# Products that are being ordered the most in general
b = pd.pivot_table(df, index = ['seller_id', 'name'], values = ['product_id'],
aggfunc = 'count')

# Sum of the quantity field for each product to check how many times has been order
c = pd.pivot_table(df, index = ['name'], values = ['product_id', 'quantity'],
aggfunc = {'product_id': 'count', 'quantity': 'sum'})

# Number of orders for each customer
d = pd.crosstab(df.customer_id, df.order_id)

• Thanks. What does aggfunc means in each line? – alim1990 Mar 15 '20 at 9:46
• aggfunc basically stands for how do you want to aggregate that particular value. In the above cases, you might want to count, sum up values, find the mean or find the standard deviation – Dhaval Thakkar Mar 15 '20 at 10:03
• when use pivot_table with two indexes, what does that means? – alim1990 Mar 15 '20 at 18:42