Summing Pandas columns between two rows

user_6484248 0
Viewed 208

I have a Pandas dataframe with columns labeled Ticks, Water, and Temp, with a few million rows (possibly billion on a complete dataset), but it looks something like this

...
'Ticks'  'Water'    'Temp'
  215       4      26.2023
  216       1      26.7324
  217      17      26.8173
  218       2      26.9912
  219      48      27.0111
  220       1      27.2604
  221      19      27.7563
  222      32      28.3002
...

(All temperatures are in ascending order, and all 'ticks' are also linearly spaced and in ascending order too)

What I'm trying to do is to reduce the data down to a single 'Water' value for each floored, integer 'Temp' value, and just the first 'Tick' value (or last, it doesn't really have that much of an effect on the analysis).

The current direction I'm working on doing this is to start at the first row and save the tick value, check if the temperature is an integer value greater than the previous, add the water value, move to the next row check the temperature value, add the water value if it's not a whole integer higher. If the temperature value is an integer value higher, append the saved 'tick' value and integer temperature value and the summed water count to a new dataframe.

I'm sure this will work but, I'm thinking there should be a way to do this a lot more efficiently using some type of application of df.loc or df.iloc since everything is nicely in ascending order.

My hopeful output for this would be a much shorter dataset with values that look something like this:

...
'Ticks'  'Water'  'Temp'
  215      24       26
  219      68       27
  222      62       28
...
pandas python
2 Answers

Use GroupBy.agg and Series.astype

new_df = (df.groupby(df['Temp'].astype(int))
            .agg({'Ticks' : 'first', 'Water' : 'sum'})
           #.agg(Ticks = ('Ticks', 'first'), Water = ('Water', 'sum'))
            .reset_index()
            .reindex(columns=df.columns)
         )
print(new_df)

Output

   Ticks  Water  Temp
0    215     24    26
1    219     68    27
2    222     32    28
user_118842370

I have some trouble understanding the rules for which ticks you want in the final dataframe, but here is a way to get the indices of all Temps with equal floored value:

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import pandas as pd
import numpy as np

data = pd.DataFrame({
        'Ticks': [215, 216, 217, 218, 219, 220, 221, 222],
        'Water': [4, 1, 17, 2, 48, 1, 19, 32],
        'Temp': [26.2023, 26.7324, 26.8173, 26.9912, 27.0111, 27.2604, 27.7563, 28.3002]})

# first floor all temps
data['Temp'] = data['Temp'].apply(np.floor)

# get the indices of all equal temps
groups = data.groupby('Temp').groups
print(groups)

# maybe apply mean?
data = data.groupby('Temp').mean()
print(data)

hope this helps

user_108253620
Related