Pivot table/pivot based on condition in python-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

I'm not sure why you need df1 but you can use pivot after filtering the day of your choice:

df2[df2['Day']=='Mon'].pivot(values=['price'], index='product_1', columns='product_2')

Output:

          price
product_2     A     B     C     E
product_1
A          10.0  20.0  30.0   NaN
B          40.0  55.0  50.0   NaN
C          60.0  70.0  80.0   NaN
D           NaN   NaN   NaN  90.0