How to count variable in one dataframe base on another dataframe?

Question

I have two df one called 'order' and another called 'asian_food'. Two table have a common column 'product_id'. I want to know how many time each of the product in the 'asian_food' table was ordered in the 'order' table.

'order' table:

'asian_food' table:

I've tried the following code:

asian['frequency'] = asian['product_id'].map(order_copy['product_id'].value_counts()).fillna(0).astype(int)

but it returns a error saying:

SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame.Try using .loc[row_indexer,col_indexer] = value instead

How can I use .loc to get what I want? Thank you in advance.

Can you pls share a sample data set for order and asian_food — Redox
– Redox, Commented Apr 24, 2022 at 4:09
could you include your sample as text? it's difficult to work with images — user7864386
– user7864386, Commented Apr 24, 2022 at 4:42

PCDSandwichMan · Accepted Answer · 2022-04-24 04:16:20Z

2

Could you do something like this?

def get_order_count_totals(order_df, asian_food_df):
    """
    Function returns a dataframe with the following columns:
        | product_id  |  product_name  |  total_orders  |
        |------------:|:--------------:|:--------------:|
        |  14         |  Asian Food    |  1             |
    """
    df = order_df.merge(asian_food_df, on="product_id")
    df = df.groupby(["product_id", "product_name"])["order_id"].count().reset_index()
    df.rename(columns={"order_id": "total_orders"}, inplace=True)
    return df

edited Apr 24, 2022 at 4:16

answered Apr 24, 2022 at 4:11

PCDSandwichMan

2,1581 gold badge15 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Guacaka283 Over a year ago

Thanks for your help! I have a question. After you merge two df, what happens to those product_id in the 'order' df that cannot find a match in the 'asian_food' df?

Zero Over a year ago

They are excluded from the data. You can probably change the parameter of the merge function to get them.

Zero Over a year ago

@PCDSandwichMan Thanks for your answer! I learned something new cause of it!

PCDSandwichMan Over a year ago

@Guacaka283 Ishan is right you could pretty easily add a default value for that though. But it just depends on what you want. 👍

Zero · Accepted Answer · 2022-04-24 04:39:43Z

0

This would do the job,

asian_indices = [orders_df[orders_df["product_id"] == product_id].index[0] for product_id in asian_orders_df["product_id"]]
new_df = orders_df.loc[asian_indices, :]

answered Apr 24, 2022 at 4:39

Zero

1,9091 gold badge10 silver badges24 bronze badges

Collectives™ on Stack Overflow

How to count variable in one dataframe base on another dataframe?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related