interate over python pandas dataframe from last row to first

Question

I have a dataframe with statistical data that is cumulated with each new row. Every day a new row is added. Now I want to iterate over my column so that each row (starting from last) is substracted from the row above. The new value should be put into a new column. This is how my dataframe looks and the values in the column 'diff' are my desired outcome:

     time        In     diff
0  2017-06-26  7.086      
1  2017-06-27  8.086     1
2  2017-06-28  10.200    2.114

this is what I came up with:

for x in df['In']:
    df['diff'] = df.iloc[-1] - df.iloc[-2]

but thats not it. How do I start the loop from the last row and how do I make the iloc more dynamic? Can someone help? thank you!

jezrael · Accepted Answer · 2017-06-28 13:12:15Z

4

You can use Series.diff:

df['diff'] = df['In'].diff()
print (df)
         time      In   diff
0  2017-06-26   7.086    NaN
1  2017-06-27   8.086  1.000
2  2017-06-28  10.200  2.114

answered Jun 28, 2017 at 13:12

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Comments

piRSquared · Accepted Answer · 2017-06-28 13:12:08Z

3

Use pd.Series.diff

df.assign(Diff=df.In.diff())

answered Jun 28, 2017 at 13:12

piRSquared

296k68 gold badges509 silver badges654 bronze badges

2 Comments

Burray Over a year ago

I dont understand your post. I think whats missing here is a new column. But when I try df['diff'] = df.assign(Diff=df.In.diff()) i get an ValueError: Wrong number of items passed 11, placement implies 1

piRSquared Over a year ago

@Burray assign creates a copy of original dataframe with a new column. Use: df = df.assign(Diff=df.In.diff())

Andrew L · Accepted Answer · 2017-06-28 13:12:00Z

1

This can be done using shift():

df
       In        time
0   7.086  2017-06-26
1   8.086  2017-06-27
2  10.200  2017-06-28


df.sort_values('time', inplace=True)

df['diff'] = df['In'] - df['In'].shift(1)

df
       In       time   diff
0   7.086 2017-06-26    NaN
1   8.086 2017-06-27  1.000
2  10.200 2017-06-28  2.114

answered Jun 28, 2017 at 13:12

Andrew L

7,1083 gold badges28 silver badges30 bronze badges

Comments

aquil.abdullah · Accepted Answer · 2017-06-28 13:17:28Z

1

Here is all you need to do.

 df['diff'] = df.In - df.In.shift(1)

 # In [16]: df
 # Out[16]:
 #        time      In   diff
 # 0  2017-06-26   7.086    NaN
 # 1  2017-06-27   8.086  1.000
 # 2  2017-06-28  10.200  2.114

answered Jun 28, 2017 at 13:17

aquil.abdullah

3,1673 gold badges24 silver badges40 bronze badges

Collectives™ on Stack Overflow

interate over python pandas dataframe from last row to first

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related