I realize there are already some posts regarding Transposing of data, however a straight Transpose won’t work for what I need. I’m looking at getting a dataset that the first two columns dictate the labels and colums for the Barchart component. I’m trying to figure out the best way to transpose the data without having to do multiple iterations. What’s the most efficient way to arrange my dataset similarly to how a pivot table works. I could use the Generic Dataset but would have to iterate a few times in order to match the row labels up with the rest of the data, or I could use a dictionary however I’m not sure how to transform the dictionary into a dataset afterwards. Any suggestions would be appreciated.
You’ll still need to do two iterations, one for the dataset, and one for the dictionary.
# Build dataset to simulate wherever the data is coming from.
csvHeaders = ['col1', 'col2', 'col3', 'col4']
csvData = [
['BA1M10', '7215CLUSTR', 77.0, 184],
['BA1M100', '8215000000', 43.0, 155],
['BA1M100', '7215000000', 45.0, 90],
['BA1M20A', 'SYS7STPCBA', 82.0, 170],
['BA1M20B', '7215CLUSTR', 81.0, 166],
['BA1M20B', 'SYS7STPCBA', 5.5, 2],
['BA1M35', '7215000000', 83.0, 156],
['BA1M70', '7215000000', 99.0, 152],
['BA1M70', 'MASTER721501', 728.0, 1],
['BA1M800', '7215000000', 30.0, 57],
['BA1M800', '8215000000', 30.0, 155],
['BA1M90', '7215000000', 31.0, 123],
['BA1M90', '8215000000', 34.0, 55]
]
dataIn = system.dataset.toDataSet(csvHeaders, csvData)
# Get number of rows from dataset
nRows = dataIn.getRowCount()
# Get column from dataset containing trnsposed column names.
# Using set() eliminates duplicates. We will also use this list
# later as the headers for the transposed dataset.
colList = sorted(list(set(dataIn.getColumnAsList(0))))
# Create blank column dictionary with null values
colDict = {i:None for i in colList}
# Transpose data into a dictionary
transposeDict = {}
for row in range(nRows):
# Grab values from the dataset
rowKey = dataIn.getValueAt(row,1)
colKey = dataIn.getValueAt(row,0)
value = dataIn.getValueAt(row,2)
# If the row key doesn't already exist, add it with the blank column dict.
if rowKey not in transposeDict.keys():
transposeDict[rowKey] = colDict.copy()
# Set column value
transposeDict[rowKey][colKey] = value
# Create list of lists from dictionary
dataOut = []
for row in sorted(transposeDict.keys()):
dataOut.append([row] + [transposeDict[row][col] for col in colList])
datasetOut = system.dataset.toDataSet(['rowName'] + colList, dataOut)
Output:
row | rowName | BA1M10 | BA1M100 | BA1M20A | BA1M20B | BA1M35 | BA1M70 | BA1M800 | BA1M90
----------------------------------------------------------------------------------------------
0 | 7215000000 | None | 45.0 | None | None | 83.0 | 99.0 | 30.0 | 31.0
1 | 7215CLUSTR | 77.0 | None | None | 81.0 | None | None | None | None
2 | 8215000000 | None | 43.0 | None | None | None | None | 30.0 | 34.0
3 | MASTER721501 | None | None | None | None | None | 728.0 | None | None
4 | SYS7STPCBA | None | None | 82.0 | 5.5 | None | None | None | None
What does the raw data look like? You might like the pivot capabilities of the view() expression function from my Simulation Aids module. Your expression binding would look something like this:
view("SELECT rowName, sum(qtyName) AS 'Grand Total' GROUP BY rowName PIVOT qtyName FOR colName",
{Root Container.Path.To.Raw.Data})
Thanks for the reply. Here’s what I ended up doing and after looking at @JordanCClark response I can re-write it to be more efficient. I am an infant when it comes to python.
from com.inductiveautomation.ignition.common import BasicDataset
from java.lang import String as jString, Integer as jInteger
bData = BasicDataset()
ds = event.source.RawData
pds = system.dataset.toPyDataSet(ds)
labels = []
header = []
dict = {}
colType = [jString]
#Create list of Products and Workstations to help create dictionary
for row in pds:
if row['Product'] not in labels:
labels.append(row['Product'])
if row['Workbench_ID'] not in header:
header.append(row['Workbench_ID'])
# Use list of Workstations and Products to create nested dictionary preloaded with the value of 0 check for if the dictionary
# has the entry for the header first and if not create it then append other data to that dictionary for that workstation
for i in header:
colType.append(jInteger)
for j in labels:
if i in dict:
dict[i].update({j:0})
else:
dict.update({i:{j:0}})
# print dict
# Update the nested dictionary from the dataset with matching values
for row in pds:
dict[row['Workbench_ID']][row['Product']] = row['TACTTIME (Median)']
#print dict
#print \r
# get dictionary values list per workstation
data = []
keylist = list(dict.keys())
label = dict[keylist[0]].keys()
#print \r
#print label
# Insert the first column header into the station list for the dataset header.
data.append(label)
d = {}
for key, value in dict.iteritems():
d.update({key:dict[key].values()})
#print \r
#print d
for i in header:
data.append(d[i])
header.insert(0,'Label')
bData.setAllDirectly(header,colType,data)