# Move row values to column in dataset

**URL:** <https://forum.inductiveautomation.com/t/move-row-values-to-column-in-dataset/64473>\
**Category:** Ignition\
**Tags:** vision\
**Created:** [September 26, 2022, 12:32pm UTC](https://forum.inductiveautomation.com/t/move-row-values-to-column-in-dataset/64473 "2022-09-26T12:32:10Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [September 26, 2022, 12:32pm UTC](https://forum.inductiveautomation.com/t/move-row-values-to-column-in-dataset/64473/1 "2022-09-26T12:32:10Z")

</div>

Hi, this is the code i am using. but i am getting 2R \* 1C… But **i want 1R \* 2C.** How to achieve this?  
what change i want to do in my script. Please let me know

```auto
filteredData = system.dataset.filterColumns(ds11, eastWest)
print filteredData 

colNames = list(filteredData .getColumnNames())
dataOut = []
s = []
for colNum in range(filteredData .getColumnCount()):

	colValues = filteredData.getColumnAsList(colNum)
	
	if all(value is None for value in colValues):
		dataOut.append(0)
	else:
		
		print colValues
		#print '...sdds...'
		dataOut.append(sum(filter(None, colValues)))
		s.append(len(filter(None, colValues)))
		
print dataOut
print s

z = [m/n for m, n in zip(dataOut, s)]
ds = system.dataset.toDataSet(['Column Name'], [[x] for x in z])
print ds
print ds.getValueAt(1,0)

```

output  
[15, 13]  
Dataset [2R ⅹ 1C]

**what i need is 1R \* 2C**

---

<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:** [September 26, 2022, 12:44pm UTC](https://forum.inductiveautomation.com/t/move-row-values-to-column-in-dataset/64473/2 "2022-09-26T12:44:10Z")

</div>

Can you post sample data for `filteredData` and what the final output should look like (IE what you are trying to do)?

---

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [September 26, 2022, 1:08pm UTC](https://forum.inductiveautomation.com/t/move-row-values-to-column-in-dataset/64473/3 "2022-09-26T13:08:30Z")

</div>

Nothing i am getting 3 columns. I am filtering t stamp from dataset

So i am geting 2R \*1C

I am running named query for a particular time and geting values… i adding the row values of each column and removing the null and taking Mean of each column sum value and want to show in single row

I want [15, 13] this list to show in dataset like this

Column1 column2  
15. 13

---

<div class="post-metadata">

**Author:** ![djs](https://avatars.discourse-cdn.com/v4/letter/d/e47774/32.png) [@djs](https://forum.inductiveautomation.com/u/djs)\
**Post date:** [September 26, 2022, 1:25pm UTC](https://forum.inductiveautomation.com/t/move-row-values-to-column-in-dataset/64473/4 "2022-09-26T13:25:38Z")

</div>

When you use `system.dataset.toDataset()`, you are providing a single _columnName_ and then a list of lists for the row data. The function then understandably takes what you provided and returns a dataset with 1 column and as many rows as there were lists in the second argument.

What you want to provide to that function is as many column names as you had rows in your `filteredData`, and then a list with only 1 list in it, but that inner list having as many items as you had rows in your `filteredData`.

Example:

```auto
headers = ["col1", "col2", "col3"]
data = [[1,2,3]]
system.dataset.toDataSet(headers, data)

```

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [September 26, 2022, 2:29pm UTC](https://forum.inductiveautomation.com/t/move-row-values-to-column-in-dataset/64473/5 "2022-09-26T14:29:34Z")

</div>

I see several things that look rather sketchy in this script.  
What happens if all the values in a column are 0 ?  
You append 0 to `dataOut`, but nothing to `s`. Which is understandable, since you then divide by values in `s`.  
But you’re also zipping `dataOut` and `s`, which won’t have the same number of values. which means your divisions are using values that were not initially aligned. And some values in dataOut are left out. Is this really what you want ?

What is `eastWest` ? Do you know how many values are in it ?  
What is the whole thing supposed to do ? As it is, it looks like maintenance nightmare: it’s pretty much impossible to understand what you’re trying to do by reading the code.  
What does `dataOut` represent ? What does `s` represent ? What is `z` ? Names matter !

If you can explain what it is that script is supposed to do, what’s its input and what’s the expected output, I’ll help with writing something more… maintainable. And that does what it’s supposed to.

edit: A few examples of what could be reworked to make things cleaner  
You’re filtering your initial dataset with `eastWest`, then iterating through the new dataset and picking columns one at a time.  
Why not just iterate through `eastWest` and pick the corresponding columns ?

```python
# if eastWest is a list of column names
for colName in eastWest:
	colIndex = ds11.getColumnIndex(colName)
	colValues = filteredData.getColumnAsList(colNum)

# if eastWest is a list of column indices
for colIndex in eastWest:
	colValues = filteredData.getColumnAsList(colNum)

```

You could also replace this part:

```python
if all(value is None for value in colValues):
	dataOut.append(0)
else:
	dataOut.append(sum(filter(None, colValues)))
	s.append(len(filter(None, colValues)))

```

by

```auto
values = filter(None, ColValues)
valuesLen = len(values)
dataOut.append(sum(values))
if valuesLen:
	s.append(valuesLen)

```

assuming you do want to not append anything when the len is 0.  
It doesn’t reduce the number of lines of codes, but it reduces clutter, and avoids repeating some operations.

---

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [September 26, 2022, 3:13pm UTC](https://forum.inductiveautomation.com/t/move-row-values-to-column-in-dataset/64473/6 "2022-09-26T15:13:13Z")

</div>

> [@prasath.t](#):
>
> `eastWest`

```auto
ds11 = system.tag.queryTagHistory(paths=[

"[MQTT Engine]Edge Nodes/VaujoursV5/Line/Takeoff/Takeoff_/DET/DET 1/Production Data/H01","[MQTT Engine]Edge Nodes/VaujoursV5/Line/Takeoff/Takeoff_/DET/DET 1/Production Data/H02"
], 
			startDate='2022-08-31 22:00:00', endDate='2022-08-31 22:00:10',returnSize=-1, aggregationMode="MinMax", returnFormat='Wide',noInterpolation = False)

```

get out from query 40R\*3C

i filtering the t stamp column by using filter data  
eastWest = ['H01',H02'] - column i want to use

column i am filtering

dataOut is used for sum the each column values

s is used get the each column count and i am dividing to get the mean of each column

i am removing the null values i am getting null values from system.tag.query that's why

---

<div class="post-metadata">

**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\
**Post date:** [September 26, 2022, 3:17pm UTC](https://forum.inductiveautomation.com/t/move-row-values-to-column-in-dataset/64473/7 "2022-09-26T15:17:11Z")

</div>

> [@pascal.fragnoud](#):
>
> ```auto
> values = filter(None, ColValues)
> valuesLen = len(values)
> dataOut.append(sum(values))
> if valuesLen:
> s.append(valuesLen)
> 
> ```

```auto
values = filter(None, ColValues)
valuesLen = len(values)
dataOut.append(sum(values))
if valuesLen:
	s.append(valuesLen)

```

i say your script after the sum the values. i want to take mean for each value.. that is missing in your scirpt

**i want to add all values in each column and take mean for that value that's it**

**finally want to pass that list to a new dataset.. each value in separate column**  
if i pass list directly to a new dataset i am getting 2R \*1C - output

but i want **1R \* 2C** output..

i don't know how to do this

if you help me to optimise the script. it will useful for me

I hope now you understand what i am trying to do?

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [September 26, 2022, 4:12pm UTC](https://forum.inductiveautomation.com/t/move-row-values-to-column-in-dataset/64473/8 "2022-09-26T16:12:24Z")

</div>

You only need to iterate through the columns once when you’re constructing the new dataset.  
(Note, I changed your filteredDataset line to use the sample data.

```python
sampleHeaders = ['date','H01','H02']
sampleData = [
              ['2021-11-23 00:00:00', None, 40],
              ['2021-11-23 00:00:00', 24, 90],
              ['2021-11-23 00:00:00', 89, None]
			 ]
dataIn = system.dataset.toDataSet(sampleHeaders, sampleData)

#filteredData = system.dataset.filterColumns(ds11, eastWest)
filteredData = system.dataset.filterColumns(dataIn, ['H01','H02'])

colNames = list(filteredData.getColumnNames())
dataOut = []

for i in range(len(colNames)):
	# Get valid values
	colValues = filter(None, filteredData.getColumnAsList(i))
	
	if len(colValues) == 0:
		dataOut.append(0)
	else:
		# Get mean of valid values and append to the row.
		dataOut.append(system.math.mean(colValues))
# Since we are only constructing one row, and system.dataset.toDataSet() requires a list of lists,
# we'll need to wrap dataOut in a list when we make the dataset
ds = system.dataset.toDataSet(colNames, [dataOut])

```

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [September 26, 2022, 5:31pm UTC](https://forum.inductiveautomation.com/t/move-row-values-to-column-in-dataset/64473/9 "2022-09-26T17:31:04Z")

</div>

> [@prasath.t](#):
>
> that is missing in your scirpt

Because it's not a script, it's a snippet. It's not meant to be copy/pasted and be functional immediately.

If you want a script...

```python
def mean(values):
	values = filter(None, values)
	if values:
		return float(sum(values)) / len(values)
	return None

# if eastWest is a list of column indices
header = [data.getColumnName(i) for i in eastWest]
means = filter(None, (mean(data.getColumnAsList(i)) for i in eastWest))
ds = system.dataset.toDataSet(header, [means])

# if eastWest is a list of column names
eastWest = ["Bar", "Baz"]
indices = [data.getColumnIndex(name) for name in eastWest]
means = filter(None, (mean(data.getColumnAsList(i)) for i in indices))
ds = system.dataset.toDataSet(eastWest, [means])

```

Pick the version you need depending on what eastWest is for you (I did ask earlier, but you didn't answer the question).

If you'd rather have a version using `system.math.mean`, replace the function call and filter with `number == number`. `system.math.mean` return `NaN` for empty lists, and `NaN != Nan`.

```python
indices = [data.getColumnIndex(name) for name in eastWest]
means = [system.math.mean(filter(None, data.getColumnAsList(i))) for i in indices]
means = [m for m in means if m == m]
ds = system.dataset.toDataSet(eastWest, [means])

```