Pandas update row value based on group

user_10636727 0
Viewed 47

I have such df 

product  type  price 
  A       1      10
  A       2      15
  A       3      NAN
  A       4      20
  B       1      40
  B       2      30
  B       3      NAN
  B       4      5
  C       1      80
  C       2      70 
  C       3      90
  C       4      NAN 
  D       6      75
  D       8      40

I want to update the value of price (Not necessarily NAN) based on another value

If (type == 3):
   price == df['type' == 1]
elif ( type == 4): 
   price == df['type' == 2]
elif ( type == 8):
   price == df['type' == 6]

Something like this the condition. I tried for each using the following: df.loc[df['type'] == 3, 'price'] = df[df['type']==1]['price'].iloc[0]

The following way works just that the value 10 is replaced at both product A, B and C. Is there a way that this value can be updated according to the product group. The expected outcome as following :

product  type  price 
  A       1      10
  A       2      15
  A       3      10
  A       4      15
  B       1      40
  B       2      30
  B       3      40
  B       4      30
  C       1      80
  C       2      70 
  C       3      80
  C       4      70
  D       6      75
  D       8      75

Note: using transform('first') may not be appropriate in this use case. Not necessarily each product will have all the type (1-10) Thanks

pandas numpy python
2 Answers

IIUC,you need to fillna with the first value of each group:

df['price']=df['price'].fillna(df.groupby('product')['price'].transform('first'))
print(df)

   product  type  price
0       A     1   10.0
1       A     2   15.0
2       A     3   10.0
3       A     4   20.0
4       B     1   40.0
5       B     2   30.0
6       B     3   40.0
7       B     4    5.0

EDIT , bases on edited question , you can try pivotting and filling na with the respective columns:

piv = df.set_index(['product','type'])['price'].unstack()
piv = piv.fillna({3:piv[1],4:piv[2]})
out = piv.stack().reset_index(name='price')

print(out)

   product  type  price
0        A     1   10.0
1        A     2   15.0
2        A     3   10.0
3        A     4   20.0
4        B     1   40.0
5        B     2   30.0
6        B     3   40.0
7        B     4    5.0
8        C     1   80.0
9        C     2   70.0
10       C     3   90.0
11       C     4   70.0
user_98406370

You can try:

for x,y in zip([3,4,8],[1,2,6]):
    df.loc[df['type'].eq(x), 'price'] = df.loc[df['type'].eq(y),'price'].values

Output:

   product  type price
0        A     1    10
1        A     2    15
2        A     3    10
3        A     4    15
4        B     1    40
5        B     2    30
6        B     3    40
7        B     4    30
8        C     1    80
9        C     2    70
10       C     3    80
11       C     4    70
12       D     6    75
13       D     8    75
user_42384080
Related