Combine Dataset Values based on Column Value

Ignition
1

@JordanCClark
Do you have any magic to combine these two rows based on that row 1 has 2 in the ITEM column? I can do it with some ugly nested for loops, and I’m not good with list comprehensions, didn’t know if my script is the best way. Thanks!

before

row | STYLE  | ACKNO  | ACKLN | ITEM     | FAB           | WELT | COUNT | TOTAL | CLASS | PATTERN
-------------------------------------------------------------------------------------------------
0   | 990530 | 640403 | 60    | A-PILL 1 | OAK ISLAND 23 |      | 2.0   | 2.0   | A     | Y      
1   | 990530 | 640403 | 60    | A-PILL 2 | BLUECOVE 22   |      | 2.0   | 2.0   | B     | Y

should look like this afterwards

row | STYLE  | ACKNO  | ACKLN | ITEM     | FAB                       | WELT | COUNT | TOTAL | CLASS | PATTERN
-------------------------------------------------------------------------------------------------
0   | 990530 | 640403 | 60    | A-PILL 1 | OAK ISLAND 23/BLUECOVE 22 |      | 2.0   | 2.0   | A     | Y

My current script

for i,row in enumerate(ds2):
	item = row['ITEM']
	if '2' in item:
		fab2 = row['FAB']
		for j,row in enumerate(ds2):
			if row['ITEM'] == item.replace('2','1'):
				newFab = row['FAB'] + '/' + fab2
				newData = system.dataset.setValue(ds2, j,'FAB',newFab)
				newData = system.dataset.deleteRow(newData, i)
2

2 cents: Your way looks fine. It’s a hard problem to ‘generalize’, and the best script is the one you remember how it works in six months/five years :slight_smile:.

4 Likes
3

+1 to Paul’s statement. But, since you asked… :laughing:

Converting to a PyDataSet will let you use subscripts, and grab an entire row as a list. We can then modify that list directly.

# Convert to PyDataSet
pyDS = system.dataset.toPyDataSet(ds2)

# Get the column names
headers = list(pyDS.getColumnNames())

# Get the index of the column we want to concatenate
idx = headers.index('FAB')

# Get the first row of the PyDataSet
dataOut = list(pyDS[0])

# Join the column values
dataOut[idx] = '/'.join(pyDS.getColumnAsList(idx))

# Make a new Dataset
newDS = system.dataset.toDataSet(headers, [dataOut])
2 Likes