Pandas DataFrame subtract values

gloria_1990 0
Viewed 25

Im new to python

I have a data frame (df) which has the following structure:

ID rate Sequential number
a 150 1
a 150 1
a 50 2
b 250 1
c 25 1
d 25 1
d 40 2
d 25 3

The ID are customers, the value are monthly rates and Sequential number is a number that always increases by 1, if the customer changes the monthly rate

I want to do the following: for every ID find the maximum value in the column Sequential number, take the associated value in the column rate, find the minimum value in the column Sequential number and take associated value in the column rate and subtracting the rates.

At the end I want to have a additional column to my data frame with the difference of the rates. Maybe the loop could do the following:

for id in df() 
    find max() in column Sequential number  and get value in rates - 
     min () in column Sequential number and get value in rates
return difference

The new df_new should be this

ID rate Sequential number rate_diff
a 150 1 0
a 150 1 0
a 50 2 -100
b 250 1 0
c 25 1 0
d 25 1 0
d 40 2 0
d 30 3 5

If an ID has only one entry, the rate_diff should be 0

I tried already the lambda Function:

df['diff_rate'] = df.groupby('ID')['rate'].transform(lambda x : x-x.min())

but this returns

ID rate Sequential number rate_diff
a 150 1 100
a 150 1 100
a 50 2 0
b 250 1 0
c 25 1 0
d 25 1 0
d 40 2 15
d 30 3 10

Maybe someone of you have a small workaround for this! :-)

lambda dataframe pandas
1 Answers

One approach with indexing:

g = df.groupby('ID')['Sequential number']
IMAX = g.idxmax()
IMIN = g.idxmin()
df['rate_diff'] = 0
df.loc[IMAX, 'rate_diff'] = (df.loc[IMAX, 'rate'].to_numpy()
                            -df.loc[IMIN, 'rate'].to_numpy()
                             )

Another with groupby.transform+where:

g = df.sort_values(by=['ID', 'Sequential number']).groupby('ID')

m = g['Sequential number'].idxmax()
df['rate_diff'] = (g['rate'].transform(lambda x: x.iloc[-1]-x.iloc[0])
                            .where(df.index.isin(m), 0)
                   )

output:

  ID  rate  Sequential number  rate_diff
0  a   150                  1          0
1  a   150                  1          0
2  a    50                  2       -100
3  b   250                  1          0
4  c    25                  1          0
5  d    25                  1          0
6  d    40                  2          0
7  d    30                  3          5
mozway0
Related