Delete Table Row based on column value

I am trying to loop through a table and remove any row that has a column with a specific value. See sample code below. This is not working, what should i be doing and is there a better way? I do not have access to the stored procedures to filter in the query.

table = event.source.parent.getComponent(“table”)
data = system.dataset.toPyDataSet(table.data)

newData = system.dataset()

for row in data:
if row[“ColName”] == “value”:
newData = system.dataset.deleteRow(data, row)

table.data = newData

The easiest way is by using a MutablePyDataSet.

Here is the code:

table = event.source.parent.getComponent('Table')
data = pa.dataset.toData(table.data)
rowsToRemove = data.findRows(lambda row: row["ColName"]=="value")
data.removeRows(rowsToRemove)
table.data = data.toDataSet()

“lambda” is Python syntax for creating an anonymous function. The “findRows” method finds the rows you want to delete by evaluating the lambda function for each row.

Edit – A new version of the PA Power Scripting Module was just released. A function can now be passed directly to the removeRows method, like this:

table = event.source.parent.getComponent('Table')
data = pa.dataset.toData(table.data)
data.removeRows(lambda row: row["ColName"]=="value")
table.data = data.toDataSet()

It could also be done like this:

table = event.source.parent.getComponent('Table')
data = pa.dataset.toData(table.data)
for row in data.copy():
	if row["ColName"] == "value":
		data.removeRow(row)
table.data = data.toDataSet()

Documentation for the MutablePyDataSet is here: doc.perfectabstractions.com/modu … taset.html

MutablePyDataSet is part of the PA Power Scripting Module.

Best,

Hi Jerry,
Your code will only delete the last row it finds, and if it finds none, return garbage. Also, deleting a row changes the following row numbers, complicating the loop. The best approach is to find all the row numbers you want to delete, then delete them all at once.table = event.source.parent.getComponent("table") c = table.getColumnIndex("ColName") unwanted = [] for r in range(table.rowCount): if table.getValueAt(r, c)=="value": unwanted.append(r) newData = system.dataset.deleteRows(table, unwanted)

Thanks pturmel! Had to make a small modification to the code, needed table.data. Below is what worked for me:

table = event.source.parent.getComponent('table')
data = table.data
unwanted = []

for r in range(data.rowCount):
	if data.getValueAt(r, "column")=="02":
		unwanted.append(r)

newData = system.dataset.deleteRows(data, unwanted)
table.data = newData
3 Likes

[quote=“jerry101”]Thanks pturmel! Had to make a small modification to the code[/quote]Awww, caught posting untested code… :blush:
But you’re welcome!

1 Like

7 posts were split to a new topic: Insert or update based on selected row