0
$\begingroup$

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):

enter image description here

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:

enter image description here

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.

$\endgroup$
1
$\begingroup$

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)
| improve this answer | |
$\endgroup$
  • $\begingroup$ Thanks. What does aggfunc means in each line? $\endgroup$ – alim1990 Mar 15 at 9:46
  • $\begingroup$ 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 $\endgroup$ – Dhaval Thakkar Mar 15 at 10:03
  • $\begingroup$ when use pivot_table with two indexes, what does that means? $\endgroup$ – alim1990 Mar 15 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.