Dividing each row by the previous one

Question

I have pandas dataframe:

df = pd.DataFrame()
df['city'] = ['NY','NY','LA','LA']
df['hour'] = ['0','12','0','12']
df['value'] = [12,24,3,9]

   city hour value
0   NY  0   12
1   NY  12  24
2   LA  0   3
3   LA  12  9

I want, for each city, to divide each row by the previous one and write the result into a new dataframe. The desired output is:

city ratio
NY   2
LA   3

What's the most pythonic way to do this?

Answer 1

First divide by shifted values per groups:

df['ratio'] = df['value'].div(df.groupby('city')['value'].shift(1))
print (df)
  city hour  value  ratio
0   NY    0     12    NaN
1   NY   12     24    2.0
2   LA    0      3    NaN
3   LA   12      9    3.0

Then remove NaNs and select only city and ratio column:

df = df.dropna(subset=['ratio'])[['city', 'ratio']]
print (df)
  city  ratio
1   NY    2.0
3   LA    3.0