Pandas combining same row into new column while preserving the row(not a simple group-by)

Question

I am a pandas beginner and in need of some help.

I have the following pandas dataframe:

ID         Val-A   Val-B

aab12      lower   -30
dbc11      lower   -10
aab12      upper   50
dbc11      upper   20

I want to produce a new dataframe from the previous one and can't think of a way to do it:

ID         Val-A  Val-B  upper-lower
aab12      lower  -30    80
aab12      upper  50     80
dbc11      lower  -10    30
dbc11      upper  20     30

Any help will be highly appreciated!

score 1 · Accepted Answer · answered Oct 16 '17 at 14:14

1

I think you need sort_values first with reset_index for nice monotonic unique index and then transform with abs and sum:

df = df.sort_values('ID').reset_index(drop=True)
df['upper-lower'] = df['Val-B'].abs().groupby(df['ID']).transform(sum)

print (df)
      ID  val-A  Val-B  upper-lower
0  aab12  lower    -30           80
1  aab12  upper     50           80
2  dbc11  lower    -10           30
3  dbc11  upper     20           30

answered Oct 16 '17 at 14:14

jezrael

822,522
95
1,334
1,252

If it is 50,30, (not negative):-) – BENY Oct 16 '17 at 14:29
I need it to work with negatives that's why I gave this exact example... – Tom Oct 16 '17 at 14:31
@Tom - So is possible use `abs` ? – jezrael Oct 16 '17 at 14:34
if abs solves this issue then yeah, I was referring to what @Wen said – Tom Oct 16 '17 at 14:35

BENY · Answer 2 · 2017-10-16T14:30:31.650

0

Assuming you have two positive number:

df['upper-lower']=df.groupby('ID')['Val-B'].apply(lambda x : x.diff().abs().bfill())
df.sort_values('ID')
Out[394]: 
      ID  Val-A  Val-B  upper-lower
0  aab12  lower    -30         80.0
2  aab12  upper     50         80.0
1  dbc11  lower    -10         30.0
3  dbc11  upper     20         30.0

edited Oct 16 '17 at 14:30

answered Oct 16 '17 at 14:24

BENY

317,841
20
164
234

Pandas combining same row into new column while preserving the row(not a simple group-by)

2 Answers2