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

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.

CodePudding user response：

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

CodePudding user response：

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, :]