CODEWITHSUNDEEP
pythonpandas
DKM
DKM

Reputation: 1801

Pandas data frame replace max value of a column with different column value based on condition

I have a data frame with multiple columns and I wanted to replace only the maximum value of "Views" column with three different columns which are based on certain conditions.

 import pandas as PD
    data = [["1.Blend Of Vdx Display","DISPLAY","Features","CPE",1255,778732,13373,7142],["1.Blend Of Vdx Display","DISPLAY","TVC","CPE",10479,778732,13373,7142],
    ["2.Mobile VDX","Display","Features","CPE",168,1000,150,160],["2.Mobile VDX","Display","Features","CPE",2309,1000,150,160]]
df = pd.DataFrame(data,columns=['Placement#Name','PRODUCT','VIDEONAME','COST_TYPE',views','IMPRESSIONS','ENGAGEMENTS','DPEENGAMENTS'])



 print(df)
  Placement#Name  PRODUCT VIDEONAME COST_TYPE  views  IMPRESSIONS  \
0  1.Blend Of Vdx Display  DISPLAY  Features       CPE   1255       778732   
1  1.Blend Of Vdx Display  DISPLAY       TVC       CPE  10479       778732   
2            2.Mobile VDX  DISPLAY  Features       CPE    168         1000   
3            2.Mobile VDX  DISPLAY  Features       CPE   2309         1000   

   ENGAGEMENTS  DPEENGAMENTS  
0        13373          7142  
1        13373          7142  
2          150           160  
3          150           160

I'm able to filter out maximum value by doing this

 newdf = df.loc[df.reset_index().groupby(['Placement#Name'])['Views'].idxmax()]

print (newdf)
         Placement#Name  PRODUCT VIDEONAME COST_TYPE  Views  IMPRESSIONS  \
1  1.Blend Of Vdx Display  DISPLAY       TVC       CPE  10479       778732   
3            2.Mobile VDX  DISPLAY  Features       CPE   2309         1000   

   ENGAGEMENTS  DPEENGAMENTS  
1        13373          7142  
3          150           160

now i wanted to replace newdf views with conditions like 10479 and 2309 can be replaced with Engagements columns as on the condition Product is display and Cost_Type is CPE.

so the new df output is 

print (newdf)
             Placement#Name  PRODUCT VIDEONAME COST_TYPE  Views  IMPRESSIONS  \
    1  1.Blend Of Vdx Display  DISPLAY       TVC       CPE  13373       778732   
    3            2.Mobile VDX  DISPLAY  Features       CPE   150        1000   

       ENGAGEMENTS  DPEENGAMENTS  
    1        13373          7142  
    3          150           160

and then i wanted to convert this on original df. so original output is:

print (df)
     Placement#Name  PRODUCT VIDEONAME COST_TYPE  views  IMPRESSIONS  \
    0  1.Blend Of Vdx Display  DISPLAY  Features       CPE   1255       778732   
    1  1.Blend Of Vdx Display  DISPLAY       TVC       CPE  13373       778732   
    2            2.Mobile VDX  DISPLAY  Features       CPE    168         1000   
    3            2.Mobile VDX  DISPLAY  Features       CPE   150         1000   

       ENGAGEMENTS  DPEENGAMENTS  
    0        13373          7142  
    1        13373          7142  
    2          150           160  
    3          150           160

Upvotes: 2

Views: 2685

Answers (2)

Vivek Kalyanarangan
Vivek Kalyanarangan

Reputation: 9081

Another way to do -

This filters out and replaces the specific cases from views to ENGAGEMENTS

newdf['views'] = newdf.apply(lambda x: x['ENGAGEMENTS'] if ((x['PRODUCT'].upper()=='DISPLAY') & (x['COST_TYPE']=='CPE')) else x['views'], axis=1)

Append the 2 dataframes

df['views'].update(newdf['views'])

Output

           Placement#Name  PRODUCT VIDEONAME COST_TYPE  views  IMPRESSIONS  \
0  1.Blend Of Vdx Display  DISPLAY  Features       CPE   1255       778732   
1  1.Blend Of Vdx Display  DISPLAY       TVC       CPE  13373       778732   
2            2.Mobile VDX  Display  Features       CPE    168         1000   
3            2.Mobile VDX  Display  Features       CPE    150         1000   

   ENGAGEMENTS  DPEENGAMENTS  
0        13373          7142  
1        13373          7142  
2          150           160  
3          150           160

Upvotes: 1

jezrael
jezrael

Reputation: 862601

I believe need:

newdf = df.loc[df.reset_index().groupby(['Placement#Name'])['Views'].idxmax()]

#filter by conditions
mask = (newdf.PRODUCT.str.upper() == 'DISPLAY') & (newdf.COST_TYPE == 'CPE')

newdf.loc[mask, 'Views'] = newdf['ENGAGEMENTS']
print (newdf)
           Placement#Name  PRODUCT VIDEONAME COST_TYPE  Views  IMPRESSIONS  \
1  1.Blend Of Vdx Display  DISPLAY       TVC       CPE  13373       778732   
3            2.Mobile VDX  Display  Features       CPE    150         1000   

   ENGAGEMENTS  DPEENGAMENTS  
1        13373          7142  
3          150           160  

#remove old index rows and append new from newdf
df = df.drop(newdf.index).append(newdf).sort_index()
print(df)
           Placement#Name  PRODUCT VIDEONAME COST_TYPE  Views  IMPRESSIONS  \
0  1.Blend Of Vdx Display  DISPLAY  Features       CPE   1255       778732   
1  1.Blend Of Vdx Display  DISPLAY       TVC       CPE  13373       778732   
2            2.Mobile VDX  Display  Features       CPE    168         1000   
3            2.Mobile VDX  Display  Features       CPE    150         1000   

   ENGAGEMENTS  DPEENGAMENTS  
0        13373          7142  
1        13373          7142  
2          150           160  
3          150           160

Upvotes: 1

Related Questions