# Help combining two datasets based on Condition

**URL:** <https://forum.inductiveautomation.com/t/help-combining-two-datasets-based-on-condition/32339>\
**Category:** Ignition\
**Tags:** scripting\
**Created:** [February 17, 2020, 4:46pm UTC](https://forum.inductiveautomation.com/t/help-combining-two-datasets-based-on-condition/32339 "2020-02-17T16:46:34Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [February 17, 2020, 4:46pm UTC](https://forum.inductiveautomation.com/t/help-combining-two-datasets-based-on-condition/32339/1 "2020-02-17T16:46:34Z")

</div>

The first dataset, ds1, has several columns, the first column is ‘user’. I have a second dataset, ds2, that has two columns [‘premp#’,‘prname’]. I want to add a column to ds1 where it inserts the correct employee number, premp#, by the correct user (user = prname). I’m having trouble figuring out how to do the comparison between user and prname

---

<div class="post-metadata">

**Author:** ![zacslade](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacslade/32/3587_2.png) [@zacslade](https://forum.inductiveautomation.com/u/zacslade)\
**Post date:** [February 17, 2020, 5:15pm UTC](https://forum.inductiveautomation.com/t/help-combining-two-datasets-based-on-condition/32339/2 "2020-02-17T17:15:43Z")

</div>

I think something like this would do it:

```auto
newColumnName = "premp#"
newColumnData = []
for row in range(ds1.getRowCount()):
	for ds2index in range(ds2.getRowCount()):
		if ds1.getValueAt(row, "user") == ds2.getValueAt(ds2index, "prname"):
			newColumnData.append(ds2.getValueAt(ds2index, "premp#"))
			break

ds1 = system.dataset.addColumn(ds1, 0, newColumnData, newColumnName)

```

But if it’s a large table, you’ll probably get more efficiency by sorting both columns first, and then looping through ds1 but manually incrementing your index in ds2 whenever the value at “user” changes in ds1, and then incrementing again if they don’t match.  
… like this:

```auto
newColumnName = "premp#"
newColumnData = []
ds1 = system.dataset.sort(ds1, "user")
ds2 = system.dataset.sort(ds2, "prname")
ds2index = 0

for row in range(ds1.getRowCount()):
	while (ds1.getValueAt(row, "user") != ds2.getValueAt(ds2index, "prname")):
		ds2index += 1
	if ds1.getValueAt(row, "user") == ds2.getValueAt(ds2index, "prname"):
		newColumnData.append(ds2.getValueAt(ds2index, "premp#"))

ds1 = system.dataset.addColumn(ds1, 0, newColumnData, newColumnName)

```

Of course, both of these depend on the idea that every user has a prname. If not, you’ll be stuck using the first one, and you’ll have to append empty rows sometimes, like this:

```auto
newColumnName = "premp#"
newColumnData = []
success = 0
for row in range(ds1.getRowCount()):
	for ds2index in range(ds2.getRowCount()):
		if ds1.getValueAt(row, "user") == ds2.getValueAt(ds2index, "prname"):
			newColumnData.append(ds2.getValueAt(ds2index, "premp#"))
			success = 1
			break
	if success == 0:
		newColumnData.append(0)

ds1 = system.dataset.addColumn(ds1, 0, newColumnData, newColumnName)

```

---

<div class="post-metadata">

**Author:** ![bpreston](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@bpreston](https://forum.inductiveautomation.com/u/bpreston)\
**Post date:** [February 17, 2020, 5:15pm UTC](https://forum.inductiveautomation.com/t/help-combining-two-datasets-based-on-condition/32339/3 "2020-02-17T17:15:49Z")

</div>

Would something like this work? The top part is just creating a dataset to test it with.

```auto
header = ['name','number']
ds1 = [['bp',123],['pb',321]]
ds1 = system.dataset.toDataSet(header,ds1)

header = ['premp#','prname']
ds2 = [[123,'bp'],[321,'pb']]
ds2 = system.dataset.toDataSet(header,ds2)
print ds1
print ds2

ds1 = system.dataset.toPyDataSet(ds1)
ds2 = system.dataset.toPyDataSet(ds2)
print ds1
print ds2

fd = []

for name, num in ds1:
	for premp, prname in ds2:
		if prname == name:
			fd.append([name,num,premp])
			break

header = ['name','number','premp']		
fd = system.dataset.toDataSet(header,fd)

```

Right after I hit post I saw zacslade beat me to it.

---

<div class="post-metadata">

**Author:** ![witman](https://avatars.discourse-cdn.com/v4/letter/w/d2c977/32.png) [@witman](https://forum.inductiveautomation.com/u/witman)\
**Post date:** [February 17, 2020, 5:48pm UTC](https://forum.inductiveautomation.com/t/help-combining-two-datasets-based-on-condition/32339/4 "2020-02-17T17:48:33Z")

</div>

Here’s an approach similar to @zacslade’s third version for handling possible no match rows, only it converts the datasets to PyDataSets for more readable code. I’m not sure what difference it may make to efficiency.

```python
# Create sample datasets.
ds1 = system.dataset.toDataSet(['user','other'],[['juser', 'emp3'],['kuser', 'emp2'],['muser','noMatch']])
ds2 = system.dataset.toDataSet(['premp#','prname'],[[2, 'kuser'],[1, 'luser'],[3, 'juser']])

# Create new column for ds1 with employee numbers.
newColumn = []
for record in system.dataset.toPyDataSet(ds1):
	# Default empNumber to None if no match found.
	empNumber = None
	for row in system.dataset.toPyDataSet(ds2):
		if record['user'] == row['prname']:
			empNumber = row['premp#']
			break
	newColumn.append(empNumber)

# Create new dataset from ds1 with new column inserted second from left.
ds3 = system.dataset.addColumn(ds1, 1, newColumn, 'premp#', int)

# Print results.
print "ds1"
for row in system.dataset.toPyDataSet(ds1):
	print [col for col in row]
print "ds2"
for row in system.dataset.toPyDataSet(ds2):
	print [col for col in row]
print "ds3"
for row in system.dataset.toPyDataSet(ds3):
	print [col for col in row]

```

Output:

```nohighlight
>>> 
ds1
[u'juser', u'emp3']
[u'kuser', u'emp2']
[u'muser', u'noMatch']
ds2
[2, u'kuser']
[1, u'luser']
[3, u'juser']
ds3
[u'juser', 3, u'emp3']
[u'kuser', 2, u'emp2']
[u'muser', None, u'noMatch']
>>> 

```

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [February 17, 2020, 6:28pm UTC](https://forum.inductiveautomation.com/t/help-combining-two-datasets-based-on-condition/32339/5 "2020-02-17T18:28:07Z")

</div>

I had to make a few small changes to deal with types and whitespace from the datasets (came from query) to make @witman’s script work. Appreciate the help everyone! This is the best forum I’ve ever used 🙂