Pivot table/pivot based on condition in python

October 17, 2022

Having a data frame as below:

data_1 = {'Day': ['Mon','Mon', 'Mon','Tue','Tue'],
        'product': ['A','B','C','D','E']}
# Create DataFrame
df1 = pd.DataFrame(data_1)
df1

data_2 = {'Day': ['Mon','Mon', 'Mon','Mon','Mon', 'Mon','Mon','Mon', 'Mon', 'Mon','Tue','Tue','Wed','Wed'],
        'product_1': ['A','A','A','B','B','B','C','C','C','D','D','E','A','B'],
        'product_2': ['A','B','C','A','B','C','A','B','C','E','B','D','B','A'],
         'price':[10,20,30,40,55,50,60,70,80,90,12,13,14,15]}
# Create DataFrame
df_2 = pd.DataFrame(data_2)
df_2

I was trying to create pivot using these two data frame.I want to create pivot table as below.is it possible that we can create pivot based on condition?

like if Day is monday:

    A   B   C
A   10  20  30
B   40  55  50
C   60  70  80

I am not sure, How to do this? Can you help me?

>Solution :

As far as I understand your question, df1 is used to select the relevant products A, B and C.

You can get them like this:

day = "Mon"
products = df1.groupby("Day").product.apply(set)[day]

And then filter the relevant rows and use pivot to generate the output you want:

(df2[(df2.Day == day) & (df2.product_1.isin(products)) & (df2.product_2.isin(products))]
 .pivot(index='product_1', columns='product_2', values=['price']))


product_2     A   B   C
product_1              
A            10  20  30
B            40  55  50
C            60  70  80