CODEWITHSUNDEEP
python-3.xpandasdataframe
LenkaLenka
LenkaLenka

Reputation: 51

DataFrame difference between rows based on multiple columns

I am trying to calculate the difference between rows based on multiple columns. The data set is very large and I am pasting dummy data below that describes the problem:

enter image description here

if I want to calculate the daily difference in weight at a pet+name level. So far I have only come up with the solution of concatenating these columns and creating multiindex based on the new column and the date column. But I think there should be a better way. In the real dataset I have more than 3 columns I am using calculate row difference. 

df['pet_name']=df.pet + df.name

df.set_index(['pet_name','date'],inplace = True)
df.sort_index(inplace=True)

df['diffs']=np.nan

for idx in t.index.levels[0]:
    df.diffs[idx] = df.weight[idx].diff()

Upvotes: 1

Views: 933

Answers (2)

jezrael
jezrael

Reputation: 862761

Use groupby by 2 columns:

df.groupby(['pet', 'name'])['weight'].diff()

All together:

#convert dates to datetimes
df['date'] = pd.to_datetime(df['date'])
#sorting
df = df.sort_values(['pet', 'name','date'])
#get differences per groups
df['diffs'] = df.groupby(['pet', 'name', 'date'])['weight'].diff()

Sample:

np.random.seed(123)

N = 100
L = list('abc')
df = pd.DataFrame({'pet': np.random.choice(L, N),
                   'name': np.random.choice(L, N),
                   'date': pd.Series(pd.date_range('2015-01-01', periods=int(N/10)))
                              .sample(N, replace=True),
                   'weight':np.random.rand(N)})


df['date'] = pd.to_datetime(df['date'])
df = df.sort_values(['pet', 'name','date'])
df['diffs'] = df.groupby(['pet', 'name', 'date'])['weight'].diff()

df['pet_name'] = df.pet + df.name
df = df.sort_values(['pet_name','date'])
df['diffs1'] = df.groupby(['pet_name', 'date'])['weight'].diff()

print (df.head(20))
        date name pet    weight     diffs pet_name    diffs1
1 2015-01-02    a   a  0.105446       NaN       aa       NaN
2 2015-01-03    a   a  0.845533       NaN       aa       NaN
2 2015-01-03    a   a  0.980582  0.135049       aa  0.135049
2 2015-01-03    a   a  0.443368 -0.537214       aa -0.537214
3 2015-01-04    a   a  0.375186       NaN       aa       NaN
6 2015-01-07    a   a  0.715601       NaN       aa       NaN
7 2015-01-08    a   a  0.047340       NaN       aa       NaN
9 2015-01-10    a   a  0.236600       NaN       aa       NaN
0 2015-01-01    b   a  0.777162       NaN       ab       NaN
2 2015-01-03    b   a  0.871683       NaN       ab       NaN
3 2015-01-04    b   a  0.988329       NaN       ab       NaN
4 2015-01-05    b   a  0.918397       NaN       ab       NaN
4 2015-01-05    b   a  0.016119 -0.902279       ab -0.902279
5 2015-01-06    b   a  0.095530       NaN       ab       NaN
5 2015-01-06    b   a  0.894978  0.799449       ab  0.799449
5 2015-01-06    b   a  0.365719 -0.529259       ab -0.529259
5 2015-01-06    b   a  0.887593  0.521874       ab  0.521874
7 2015-01-08    b   a  0.792299       NaN       ab       NaN
7 2015-01-08    b   a  0.313669 -0.478630       ab -0.478630
7 2015-01-08    b   a  0.281235 -0.032434       ab -0.032434

Upvotes: 2

BENY
BENY

Reputation: 323286

Base on your description , you can try groupby 

df['pet_name']=df.pet + df.name
df.groupby('pet_name')['weight'].diff()

Upvotes: 2

Related Questions