Pandas update values using loc with repeated indices

Question

All,

I have a dataframe with repeated indices. I'm trying to update the values using the index for all rows with that index. Here is an example of what I have

"A" is present at every time. I want to replace "x" with the current value of "x", minus the value of "x" for "A" at that time. The tricky part is to get with an array or dataframe that is, in this case

array([5, 5, 7, 5, 5, 5])

which is the value for "A", but repeated for each timestamp. I can then subtract this from df['x']. My working solution is below.

temp = df[df['name'] == 'A']
d = dict(zip(temp.index, temp['x']))
df['x'] = df['x'] - df.index.to_frame()['t'].replace(d)


  name  x
t        
0    A  0
0    B -3
1    A  0
2    A  0
2    B  4
2    C -2

This works, but feels a bit hacky, and I can't help but think there is a better (and must faster) solution...

BENY · Accepted Answer · 2020-07-22 13:38:16Z

1

I will do reindex

df.x-=df.loc[df.name=='A','x'].reindex(df.index).values
df
Out[362]: 
  name  x
t        
0    A  0
0    B -3
1    A  0
2    A  0
2    B  4
2    C -2

answered Jul 22, 2020 at 13:38

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tyler Klein Over a year ago

This is EXACTLY what I needed. Thank you! I had only ever used reindex to reset the index to 0..N, I never knew it could be used to essentially duplicate the rows

wwnde · Accepted Answer · 2020-07-22 13:49:46Z

1

groupby .cumsum() of where name =A and subtract fast value in each group from the rest

 df['x']=df.groupby((df.name=='A').cumsum())['x'].apply(lambda s:s.sub(s.iloc[0]))


 name  x
t        
0    A  0
0    B -3
1    A  0
2    A  0
2    B  4
2    C -2

answered Jul 22, 2020 at 13:49

wwnde

26.7k6 gold badges22 silver badges38 bronze badges

Collectives™ on Stack Overflow

Pandas update values using loc with repeated indices

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related