Divide every row that shares an identifier in pandas-CodePudding

  id item val 
o a  tire  5
1 a  brick 5
2 b  wheel 9
3 b  brick 6
4 c  ice   6
5 c  brick 3
6 d  brick 3
7 d  grass 6

Let's say I have this dataframe. I want to divide every value of an item that shares an id with "brick" by the value for "brick". The end result should look like this:

  id item val per/brick
o a  tire  5     1
1 a  brick 5     1
2 b  wheel 9     1.5
3 b  brick 6     1
4 c  ice   6     2
5 c  brick 3     1
6 d  brick 3     1
7 d  grass 6     2

I tried declaring the column using a for loop:

perbrick=[]
for x in df['id']:
    if df[df['id']==x & df['item']!='brick']:
        perbrick.append(df[df['id']==x]['val']/df[(df['id']==x)&(df['item']=='brick')]['val'])
    else:
        perbrick.append(1)

However this just yields TypeError: Cannot perform 'rand_' with a dtyped [object] array and scalar of type [bool]

Thanks in advance

CodePudding user response：

You can use a groupby.transform:

bricks = (df['val']
          .where(df['item'].eq('brick'), 0)
          .groupby(df['id']).transform('sum')
          )

df['per/brick'] = df['val'].div(bricks)

output:

  id   item  val  per/brick
0  a   tire    5        1.0
1  a  brick    5        1.0
2  b  wheel    9        1.5
3  b  brick    6        1.0
4  c    ice    6        2.0
5  c  brick    3        1.0
6  d  brick    3        1.0
7  d  grass    6        2.0

CodePudding user response：

here is one way to do it

#create a dictionary of id, val for brick items
d=dict(df.loc[df['item'].eq('brick')][['id','val']].values)

resulting dictionary

{'a': 5, 'b': 6, 'c': 3, 'd': 3}

# divide by the brick mapped value
df['perbrick']=df['val'].div( df['id'].map(d)  )
df

    id  item    val     perbrick
o   a   tire    5   1.0
1   a   brick   5   1.0
2   b   wheel   9   1.5
3   b   brick   6   1.0
4   c   ice     6   2.0
5   c   brick   3   1.0
6   d   brick   3   1.0
7   d   grass   6   2.0