Removing Rows from a Table

Ignition
,
1

Hey all,

I’m trying to delete rows from a data table depending on what .csv file I imported and i’m having trouble creating a loop which reads each row as a conditional statement.

Dispatch code.PNG736×600 6.94 KB

Dispatch code 1.PNG562×554 23.9 KB

As you can see I’m trying to compare each value in the last column (“ID”) to 3 and remove the entire row if it does not equal 3. I can get it to work if I call out individual cells in the table but since the .csv files can vary in length I need something more like a loop. The .csv upload piece of the code works perfectly but i’m not exactly sure what I need to do to create that conditional loop the .getValueAt command is not working.

2

I believe you need to use a FOR loop to loop the data in the table.

py_data = system.dataset.toPyDataSet(table.data)

for row in py_data:
      if event.source.parent.getValueAt([], 'ID') != 3:
           system.deleteRow('Table').data

I’m not sure everything needed to execute this code is there, but it should be a good start

1 Like
3 
import csv
path = system.file.openFile("csv")
data = csv.reader(open(path))
header = data.next()
datasetData = []
for row in data:
	if row[-1] == 3:
		datasetData.append(row)

table.data = system.dataset.toDataSet(headers, datasetData)

Rather than selectively delete rows after reading the entire file, just skip those rows when constructing the dataset. row[-1] will refer to the last element in the sequence of columns on that row, so even if the file format changes, it will always check the last column.

5 Likes
4

Both of you are life savers thank you so much!

5

@PGriffith So I have spent about a day struggling to get this to work and python/ignition is still throwing me a executing script error.

import csv
path = system.file.openFile("csv")
csvData = csv.reader(open(path))
header = csvData.next()

datasetData = []
for row in csvData:
	if row[-1] == 3:
		datasetData.append(row)

table.data = system.dataset.toDataSet(header, datasetData)
#since I'm using just a standard button and Table 
event.source.parent.getComponent('Table').data = table.data

I have also tried @dkhayes method with no luck either. I attached the .csv file that ive been practicing with at the bottom.

Also since row[-1] is suppose to analyze the last cell of a row, how would one go about analyzing intermediate columns.

Dispatch dummy.csv (133 Bytes)

6

Try this; I think a DictReader is more appropriate if you might be getting elements in different orders.

data = ["Sample,Location,Latitude,longitude,ID",
"1,1,1,1,3",
"2,11,1,3,3",
"3,1,1,2,3",
"4,11,1,1,0",
"5,1,1,2,0",
"6,1,11,2,3",
"7,1,1,2,3",
"8,1,1,1,0"]

import csv
csvData = csv.DictReader(data)
headers = [h.capitalize() for h in csvData.next().keys()] #just goes through the headers to format them the same

datasetData = []
for row in csvData:
	if row['ID'] == '3': #all items are read as strings, so either cast row['ID'] to an integer, or do a string<->string comparison
		datasetData.append(row.values())

ds = system.dataset.toDataSet(headers, datasetData)
print ds
print ds.getColumnNames()
7

The OP may be referring more to slicing (tutorial here):
https://www.pythoncentral.io/how-to-slice-listsarrays-and-tuples-in-python/

@agweb.salinas: In your current code, ‘table’ in line 11 is not defined. That’s where your error is happening.

The file you posted has a Byte Order Marker (BOM) at the beginning of the file. I don’t know if that’s intentional, or just an artifact of making a csv file in Excel or whatever you used to make it. If it normally doesn’t, then don’t worry about it, But to make the results consistent, the file should be opened using the codecs library.

import csv, codecs

#path = 'C:\\Test\\Dispatch Dummy.csv'

path = system.file.openFile("csv")
if path != None:
  csvData = csv.reader(codecs.open(path, encoding='utf-8-sig'))

  header = [h.capitalize() for h in csvData.next()]
  datasetData = []

  for row in csvData:
    if row[-1] == '3':
      datasetData.append(row)
dataOut = system.dataset.toDataSet(header, datasetData)
			
event.source.parent.getComponent('Table').data = dataOut
5 Likes