conditional aggregation

[nagydavid]

Hi All,

I need some help regarding implementing conditional aggragation in ibis.

table_mutated = table_subset.mutate(
    timestamp=table_subset.CREATETIME.truncate(resolution)
)

# Define the case expression for aggregation
aggregation_expr = case().when(
    table_mutated.DATAPOINT_NAME == "flow",
    table_mutated.DATAPOINT_VALUE.sum() / resolution_to_seconds(resolution)
).when(
    table_mutated.DATAPOINT_NAME == "power",
    table_mutated.DATAPOINT_VALUE.sum()
).else_(
    table_mutated.DATAPOINT_VALUE.mean()
).end()

# Perform the group by and aggregation
table_aggregated = table_mutated.group_by(
    [table_mutated.timestamp.name("timestamp"), table_mutated.DATAPOINT_NAME.name("DATAPOINT_NAME")]
).aggregate(DATAPOINT_VALUE=aggregation_expr)

if it would be SQL i would do similar like this, but now I am lost.

Thanks the help in advance.

best D.

ps .: There was another topic regarding conditional aggregation, but that is not working either.

[gforsyth]

Hi @nagydavid !

Can you explain a little more about what is going wrong?

Does the above code error? Or is it returning an unexpected result?

If you can provide a small representative dataset (dummy data is fine) with the output you are looking for we will be in a much better position to help you.

[nagydavid]

HI @gforsyth , thanks for the reply.

Lets say this is my data:

import numpy as np
import pandas as pd
import random
from datetime import datetime, timedelta


datapoint_values = np.random.randint(0,100,60)
timestamp = pd.date_range(start="2021-01-01 00:00:00", periods=60, freq="180s")

#repeat datapoint_names 20 times
datapoint_names = ["a","b","c"]*20

test = pd.DataFrame({"datapoint_names":datapoint_names,
                     "datapoint_values":datapoint_values,
                        "timestamp":timestamp})

datapoint_names	datapoint_values	timestamp
a	12	2021-01-01 00:00:00
b	43	2021-01-01 00:03:00
c	72	2021-01-01 00:06:00
a	50	2021-01-01 00:09:00
b	34	2021-01-01 00:12:00
c	41	2021-01-01 00:15:00
a	1	2021-01-01 00:18:00
b	20	2021-01-01 00:21:00

I would like to aggregate the datapoint_values, based on the grouping of datapoint_names and timestamp. But the conditions are

if(datapoint_names == "a"):
   sum(datapoint_values)/60
elif(datapoint_names == "b"):
   sum(datapoint_values)
else:
   mean(datapoint_values)

I hope it is better

[lostmygithubaccount]

Hi @nagydavid, I think you want something like the below. Hope this helps! Let us know if you have any questions/feedback.

Gist for better syntax highlighting: https://gist.github.com/lostmygithubaccount/bf8e60832dd2d8b7b80b4bde1f1c0266

Setup the same data from your example:

[ins] In [1]: import numpy as np
         ...: import pandas as pd
         ...: import random
         ...: from datetime import datetime, timedelta
         ...:
         ...:
         ...: datapoint_values = np.random.randint(0, 100, 60)
         ...: timestamp = pd.date_range(start="2021-01-01 00:00:00", periods=60, freq="180s")
         ...:
         ...: # repeat datapoint_names 20 times
         ...: datapoint_names = ["a", "b", "c"] * 20
         ...:
         ...: test = pd.DataFrame(
         ...:     {
         ...:         "datapoint_names": datapoint_names,
         ...:         "datapoint_values": datapoint_values,
         ...:         "timestamp": timestamp,
         ...:     }
         ...: )
         ...:
         ...: test.head()
Out[1]:
  datapoint_names  datapoint_values           timestamp
0               a                45 2021-01-01 00:00:00
1               b                30 2021-01-01 00:03:00
2               c                48 2021-01-01 00:06:00
3               a                80 2021-01-01 00:09:00
4               b                67 2021-01-01 00:12:00

[ins] In [2]: import ibis

[ins] In [3]: ibis.options.interactive = True

[ins] In [4]: t = ibis.memtable(test)
         ...: t
Out[4]:
┏━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┓
┃ datapoint_names ┃ datapoint_values ┃ timestamp           ┃
┡━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━┩
│ string          │ int64            │ timestamp           │
├─────────────────┼──────────────────┼─────────────────────┤
│ a               │               45 │ 2021-01-01 00:00:00 │
│ b               │               30 │ 2021-01-01 00:03:00 │
│ c               │               48 │ 2021-01-01 00:06:00 │
│ a               │               80 │ 2021-01-01 00:09:00 │
│ b               │               67 │ 2021-01-01 00:12:00 │
│ c               │               11 │ 2021-01-01 00:15:00 │
│ a               │               16 │ 2021-01-01 00:18:00 │
│ b               │               59 │ 2021-01-01 00:21:00 │
│ c               │               78 │ 2021-01-01 00:24:00 │
│ a               │               13 │ 2021-01-01 00:27:00 │
│ …               │                … │ …                   │
└─────────────────┴──────────────────┴─────────────────────┘

Simple group by aggregating by datapoint_names:

[ins] In [5]: t.group_by(t.datapoint_names).aggregate(t.datapoint_values.sum().name('sum'))
Out[5]:
┏━━━━━━━━━━━━━━━━━┳━━━━━━━┓
┃ datapoint_names ┃ sum   ┃
┡━━━━━━━━━━━━━━━━━╇━━━━━━━┩
│ string          │ int64 │
├─────────────────┼───────┤
│ a               │   860 │
│ b               │  1052 │
│ c               │  1112 │
└─────────────────┴───────┘

Now, construct the aggregation cases you've specified for a, b, and e (else):

[ins] In [6]: a_agg = t.datapoint_values.sum(where=t.datapoint_names == 'a') / 60
         ...: a_agg
Out[6]: 14.333333333333334

[ins] In [7]: b_agg = t.datapoint_values.sum(where=t.datapoint_names == "b")
         ...: b_agg
Out[7]: 1052

[ins] In [8]: e_agg = t.datapoint_values.sum(
         ...:     where=(t.datapoint_names != "a") & (t.datapoint_names != "b")
         ...: )
         ...: e_agg
Out[8]: 1112

To get these in one table, simply call agg/aggregate with these expressions (which you could write inline):

[ins] In [9]: t.agg([a_agg, b_agg, e_agg])
Out[9]:
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━┓
┃ Divide(Sum(datapoint_values, Equals(datapoint_names, 'a')), 60) ┃ Sum(datapoint_values, Equals(datapoint_names, 'b')) ┃ … ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━┩
│ float64                                                         │ int64                                               │ … │
├─────────────────────────────────────────────────────────────────┼─────────────────────────────────────────────────────┼───┤
│                                                       14.333333 │                                                1052 │ … │
└─────────────────────────────────────────────────────────────────┴─────────────────────────────────────────────────────┴───┘

Rename them so you can see all three columns:

[nav] In [10]: t.agg([a_agg.name("a"), b_agg.name("b"), e_agg.name("e")])
Out[10]:
┏━━━━━━━━━━━┳━━━━━━━┳━━━━━━━┓
┃ a         ┃ b     ┃ e     ┃
┡━━━━━━━━━━━╇━━━━━━━╇━━━━━━━┩
│ float64   │ int64 │ int64 │
├───────────┼───────┼───────┤
│ 14.333333 │  1052 │  1112 │
└───────────┴───────┴───────┘

You can then group by timestamp (though they're all unique in this example data):

[nav] In [11]: t.group_by(t.timestamp).agg([a_agg.name("a"), b_agg.name("b"), e_agg.name("e")])
Out[11]:
┏━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━┳━━━━━━━┓
┃ timestamp           ┃ a        ┃ b     ┃ e     ┃
┡━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━╇━━━━━━━┩
│ timestamp           │ float64  │ int64 │ int64 │
├─────────────────────┼──────────┼───────┼───────┤
│ 2021-01-01 00:00:00 │ 0.750000 │  NULL │  NULL │
│ 2021-01-01 00:03:00 │      nan │    30 │  NULL │
│ 2021-01-01 00:06:00 │      nan │  NULL │    48 │
│ 2021-01-01 00:09:00 │ 1.333333 │  NULL │  NULL │
│ 2021-01-01 00:12:00 │      nan │    67 │  NULL │
│ 2021-01-01 00:15:00 │      nan │  NULL │    11 │
│ 2021-01-01 00:18:00 │ 0.266667 │  NULL │  NULL │
│ 2021-01-01 00:21:00 │      nan │    59 │  NULL │
│ 2021-01-01 00:24:00 │      nan │  NULL │    78 │
│ 2021-01-01 00:27:00 │ 0.216667 │  NULL │  NULL │
│ …                   │        … │     … │     … │
└─────────────────────┴──────────┴───────┴───────┘

Just to show some aggregation by timestamp, truncate to hours:

[nav] In [12]: t.group_by(t.timestamp.truncate("H")).agg([a_agg.name("a"), b_agg.name("b"), e_agg.name("e")])
Out[12]:
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━┳━━━━━━━┓
┃ TimestampTruncate(timestamp) ┃ a        ┃ b     ┃ e     ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━╇━━━━━━━┩
│ timestamp                    │ float64  │ int64 │ int64 │
├──────────────────────────────┼──────────┼───────┼───────┤
│ 2021-01-01 00:00:00          │ 4.316667 │   321 │   280 │
│ 2021-01-01 01:00:00          │ 5.633333 │   303 │   353 │
│ 2021-01-01 02:00:00          │ 4.383333 │   428 │   479 │
└──────────────────────────────┴──────────┴───────┴───────┘

[lostmygithubaccount]

the sum on e_agg should be a mean instead to match what you asked for

[nagydavid]

Hi @lostmygithubaccount

It works beautifully. Thanks

ps.: love the account name