How to find rate of change across successive rows using time and data columns after grouping by a different column using pandas?

pandas percentage change two columns
pandas difference between rows in a column
pandas time difference between rows
pandas pct_change across columns
python pandas pct_change example
calculate percentage pandas groupby
pandas percentage of each value in column
pandas pct_change groupby

I have a pandas DataFrame of the form:

df

ID_col time_in_hours data_col
  1        62.5         4
  1        40           3
  1        20           3
  2        30           1
  2        20           5
  3        50           6

What I want to be able to do is, find the rate of change of data_col by using the time_in_hours column. Specifically,

rate_of_change = (data_col[i+1] - data_col[i]) / abs(time_in_hours[ i +1] - time_in_hours[i])

Where i is a given row and the rate_of_change is calculated separately for different IDs

Effectively, I want a new DataFrame of the form:

new_df

ID_col time_in_hours data_col  rate_of_change
  1        62.5         4          NaN
  1        40           3         -0.044
  1        20           3          0
  2        30           1          NaN
  2        20           5          0.4
  3        50           6          NaN

How do I go about this?


You can use groupby:

s = df.groupby('ID_col').apply(lambda dft: dft['data_col'].diff() / dft['time_in_hours'].diff().abs())
s.index = s.index.droplevel()
s

returns

0         NaN
1   -0.044444
2    0.000000
3         NaN
4    0.400000
5         NaN
dtype: float64

pandas.DataFrame.diff, DataFrame.groupby · pandas. Periods to shift for calculating difference, accepts negative values. Take difference over rows (0) or columns (1). Returns Percent change over given number of periods. DataFrame.shift. Shift index by desired number of periods with an optional time freq. Difference with previous column. df.pivot_table(index='Date',columns='Groups',aggfunc=sum) results in. data Groups one two Date 2017-1-1 3.0 NaN 2017-1-2 3.0 4.0 2017-1-3 NaN 5.0 Personally I find this approach much easier to understand, and certainly more pythonic than a convoluted groupby operation. Then if you want the format specified you can just tidy it up:


You can actually get around the groupby + apply given how your DataFrame is sorted. In this case, you can just check if the ID_col is the same as the shifted row.

So calculate the rate of change for everything, and then only assign the values back if they are within a group.

import numpy as np

mask = df.ID_col == df.ID_col.shift(1)
roc = (df.data_col - df.data_col.shift(1))/np.abs(df.time_in_hours - df.time_in_hours.shift(1))

df.loc[mask, 'rate_of_change'] = roc[mask] 
Output:
   ID_col  time_in_hours  data_col  rate_of_change
0       1           62.5         4             NaN
1       1           40.0         3       -0.044444
2       1           20.0         3        0.000000
3       2           30.0         1             NaN
4       2           20.0         5        0.400000
5       3           50.0         6             NaN

How to find Percentage Change in pandas, So you are interested to find the percentage change in your data. using pandas pct_change() api and how it can be used with different data Increment to use from time series API (e.g. 'M' or BDay()) The first row will be NaN since that is the first value for column A, B and C. pct_change in groupby. Where i is a given row and the rate_of_change is calculated separately for different IDs. Effectively, I want a new DataFrame of the form: new_df. ID_col time_in_hours data_col rate_of_change 1 62.5 4 NaN 1 40 3 -0.044 1 20 3 0 2 30 1 NaN 2 20 5 0.4 3 50 6 NaN How do I go about this?


You can use pandas.diff:

df.groupby('ID_col').apply(
   lambda x: x['data_col'].diff() / x['time_in_hours'].diff().abs())

ID_col   
1       0         NaN
        1   -0.044444
        2    0.000000
2       3         NaN
        4    0.400000
3       5         NaN
dtype: float64

Getting frequency counts of a columns in Pandas DataFrame , Getting frequency counts of a columns in Pandas DataFrame. Given a Pandas dataframe, we need to find the frequency counts of each item in one or more columns of this dataframe. Method #1: Using Series.value_counts() After grouping a DataFrame object on one column, we can apply count() method on the  To expand on Shawn's comment. The order of the Compute Using pane can at times make a difference The sort order of dimension pills in the Compute using list box on Advanced dialog makes a difference in these situations: - Order Along is Automatic or - At the Level is not "Deepest", and not the last one in the list, eg "Date Level" in this screen shot (two options in the drop-down mean the same


Group By: split-apply-combine, In fact, in many situations we may wish to split the data set into groups and do something Since the set of object instance methods on pandas data structures are For DataFrame objects, a string indicating a column to be used to group. New in version 0.24. You can get quite creative with the label mapping functions. Hi, Thanks for posting the sample data in such a useful form! Whenever you post a question, you should always say what version of Oracle you're using, too. Displaying multiple columns from one row as one column on multiple rows is called Unpivoting.


Selecting Subsets of Data in Pandas: Part 3 - Dunder Data, In this article, we will use the assignment statement, but only after we select a Before we change any of the data in this DataFrame, we will add a single we can use a list or NumPy array with different values for each row. Let's create the column BONUS RATE , with a list of numbers between 0 and 1. Upon hitting the edit button, It should create a branch/fork and let you issue a PR on it: "Octotip: You are editing a file in a project you do not have write access to.


Time Series Analysis Tutorial with Python, Get Google Trends data of keywords and see how they vary over time while For more on time series with pandas, check out the Manipulating Time the columns of your DataFrame df so that they have no whitespaces in them. also chained methods: you called methods on an object one after another. what I want to know is how many rows have entries from the same column. For example, row 0 has entries from columns a and b. row 1 from columns b and c. row 2 from columns a,b and c. an row 3 from columns a and c. therefore there are 4 rows with unique column combinations and 4 changes. The mean is then 1.