Using Python pandas, how do I create a function to calculate the proportion of rows that represent a lower value than the previous row?

user_12493036 0
Viewed 505

Using Python pandas, how do I create a function to calculate the proportion of rows that represent a lower value than the previous row? So in other words, I need a function to iterate through the values under a particular series column of a Pandas Data Frame and only count those values where the next row's value (under say column called 'Mileage') is less than the current row's value. Like say you have this: Mileage: row 1: 30 row 2: 20 row 3: 40 row 4: 50 row 5: 60 row 6: 55 row 7: 75

If the counter is working correctly, it would spot that row 2's value of 20 is less than row 1's value of 30 and so it would add +1 to counter (count that one).
In the example above, another row that it should count is row 6: 55 which is < than its previous row 5: 60 and so count that one. And so the final count would be: 2. And then I can divide that final count by total row count to get a proportion.

Thank you in advance for any help!

pandas counter dataframe python
2 Answers

You can use the pandas function shift() like this:

import pandas as pd
data = {'mileage': [30,20,40,50,60,55,75] }
df = pd.DataFrame(data)
smaller_rows = (df.mileage < df.mileage.shift()).sum()
print(smaller_rows)
out[]: 2

How does it work? Shift(), as the name says shifts the values of the mileage column by 1 row further (default 1, any amount can be specified via the key periods). Then both DataFrames are compared to each other which creates an array of booleans. Applying sum() will count the number of True's.

To get the proportion, you want to divide smaller_rows by the total amount of rows, like this:

proportion = smaller_rows/len(df)
user_137063890

You can do this using the series.shift function:

proportion = len(df[df['Mileage'] < df['Mileage'].shift(1)])/len(df)
print(proportion)

output:

0.2857142857142857

the part of the code:

df[df['Mileage'] < df['Mileage'].shift(1)]

Uses masking to only select rows that meet that condition (in this case 2), and so we take the len of that divided by the total len of the df and get the proportion. .shift(1) allows you to access the next rows value so you can compare to the current row in this way.

user_84682640
Related