[Python] How to use a filter expression in PIconnect summary in python

I am pulling data from PI historian and plotting graphs to check environmental and quality performance. This used to be done in excel, but I am using python to make the job more automated and easier. The idea is to show data for a year broken up in 7 day averages. The issue is that sometimes there was no production for some of the days in the 7 day period. Normalising the data to production then yields very high numbers up to infinity. I want to use a filter expression to pull the data so that data is returned when my machine speed tag is larger than 0. This was achieved in excel with the following formula

=@PIAdvCalcExpFilVal(Tag;Start date;"+7d";Filter tag;"average (time-weighted)";"time-weighted";"compressed";"10m";0;1;0;"")


My code thus far is as follows

import pandas as pd
import numpy as np
from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta
import PIconnect as PI
from OSIsoft.AF.PI import PIException
import pytz
from datetime import date

PI.PIConfig.DEFAULT_TIMEZONE = ' '.join(pytz.country_timezones['za'])
bed = PI.PIServer(server="xxxx")

name = [prod_n, ft1002_n, ft2005_n, ft9000_n, ft8005_n, ft0017_n, ft0035_n, ft5004_n, ft5001_n, ft5002_n, ft8b02_n, ft5003_n,
at4001_n, at4005_n, kol_n, dcm_n, hcl_n] = [
bed.search('*u3*tot*prod*')[0],
bed.search('*u3*ft1002*')[0], # varswater [L/min]
bed.search('*u3*ft2005*')[0], # uitvloeisel [L/min]
bed.search('*u3*ft9000*')[0], # totale stoom [t/h]
bed.search('*u3*f*8005*')[0], # stoom na BW- en Mkaste [t/h]
bed.search('*cd96*f*0017*')[0], # kondensaat terugvoer [L/min]
bed.search('*u3*f*0035*')[0], # demin water na BBW kas [L/min]
bed.search('*u3*fc5004*')[0], # demin water na berol verdunning [L/min]
bed.search('*u3*fc5001*')[0], # demin water na HW storte 1 [L/min]
bed.search('*u3*fc5002*')[0], # demin water na HW storte 2 [L/min]
bed.search('*u3*fc8b02*mv*')[0], # demin water na H2SO4 verdunning [L/min]
bed.search('*u3*fc5003*mv*')[0], # berolvloei [L/min]
bed.search('*u3*at4001*md*')[0], # masjienrigting vog [%]
bed.search('*u3*at4005*bd9*')[0], # basisgewig [gsm bd]
bed.search('*u3*sht*spot*')[0], # kolletjietelling [kolle/m2]
bed.search('*u3*sht*dcm*')[0], # DCM uitgetrektes [%]
bed.search('*u3*sht*hcl*')[0] # HCl onoplosbares [dpm]
]
filter_etiket = bed.search('*u3*mach*speed*')[0] # masjiensnelheid [m/min]

def dataverwerking(name, filter_etiket):
data = []
for i in name:
j = pd.DataFrame(
i.summaries(
start_time = beg, end_time = eind, interval = '7d',
summary_types = 2 | 32, calculation_basis = 0#, filter_expression = f"{filter_etiket} > 0"
))
data.append(j.map(lambda x: np.nan if isinstance(x, PIException) else x))
return data

beg = tyd_za("2024.10.01") # FJ 2024 begindatum
eind = tyd_za(date.today()) # vandag

data = prod, ft1002, ft2005, ft9000, ft8005, ft0017, ft0035, ft5004, ft5001, ft5002, ft8b02, ft5003, at4001, at4005, kol, dcm, hcl, = dataverwerking(name, filter_etiket)


In the code above the filter expression in the function dataverwerking() is commented out because it gives an unexpected keyword argument type error as shown below.

TypeError Traceback (most recent call last)
Cell In[22], line 1
----> 1 data = prod, ft1002, ft2005, ft9000, ft8005, ft0017, ft0035, ft5004, ft5001, ft5002, ft8b02, ft5003, at4001, at4005, kol, dcm, hcl, = dataverwerking(name, filter_etiket)

Cell In[21], line 8, in dataverwerking(name, filter_etiket)
5 data = []
6 for i in name:
7 j = pd.DataFrame(
----> 8 i.summaries(
9 start_time = beg, end_time = eind, interval = '7d',
10 summary_types = 2 | 32, calculation_basis = 0, filter_expression = f"{filter_etiket} > 0"
11 ))
12 data.append(j.map(lambda x: np.nan if isinstance(x, PIException) else x))
13 return data

TypeError: PISeriesContainer.summaries() got an unexpected keyword argument 'filter_expression'


Looking at the PIconnect documentation there is an option to filter using filter_expression in recorded_values(), but not in summary(). This does seem to have worked in excel however, so I believe it should be possible in python as well.

I tried filtering manually using pandas, but the issue is that the filtering should happen before the averages for the 7 day period is retrieved.

Save for the inability to filter to machine speed, the code is working well for the intended purpose.

Is there a way to filter within the summary() function, or is there perhaps a workaround to achieve the same purpose without having to pull all the recorded values? The issue with the latter is that the server starts crashing when retrieving that much data.

Continue reading...