Column renaming python | col1-keep, col2-headers, col3-values

Question

I try to find a way how to do following in Knime

I have a file like

|    Column      |
|----------------|
|a.type_1 = "aaa"|
|a.type_2 = "bbb"|
|a.type_3 = "ccc"|
|b.type_1 = "ddd"|
|b.type_2 = "bbb"|
|b.type_3 = "eee"|
|----------------|

By splitting I have reached

|Name|  Type  | Description|
|--------------------------|
|a   | type_1 | "aaa"      |
|a   | type_2 | "bbb"      |
|a   | type_3 | "ccc"      |
|b   | type_1 | "ddd"      |
|b   | type_2 | "bbb"      |
|b   | type_3 | "eee"      |
|--------------------------|

But in the end I want to have a table like

|Name| type_1 | type_2 | type_3 |
|-------------------------------|
|a   | "aaa"  |  "bbb" | "ccc"  |
|b   | "ddd"  | "bbb"  | "eee"  |
|-------------------------------|

Can you please give a tip how to do that? It would be also very helpful in case you share how to do that in python.

Thank you!

Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Jun 01 '22 at 12:34
You want to manipulate text data. The "columns" aren't a data structure. Right? If so, I would load the "row" data in the second table as a dictionary of dictionaries (where each dictionary has keys of 'Name', 'Type' and 'Description'). Then I would loop and create a new dictionary based on the keys. — kcw78, Jun 01 '22 at 12:45

score 0 · Answer 1 · answered Jun 01 '22 at 14:02

This creates a simple dictionary of your data. It prints both table formats: 1) with Name/Type/Description columns, then 2) with Name/type_1/type_2/type_3 columns. I didn't bother with table headers and footers. You can add that. :-)

row_dict = { 
        'row1': { 'Name':'a', 'Type':'type_1', 'Description':'aaa'},
        'row2': { 'Name':'a', 'Type':'type_2', 'Description':'bbb'},
        'row3': { 'Name':'a', 'Type':'type_3', 'Description':'ccc'},
        'row4': { 'Name':'b', 'Type':'type_1', 'Description':'ddd'},
        'row5': { 'Name':'b', 'Type':'type_2', 'Description':'bbb'},
        'row6': { 'Name':'b', 'Type':'type_3', 'Description':'eee'}  
            }

for row, data in row_dict.items():
    print(f"| {data['Name']} | {data['Type']} | {data['Description']} |")
print()    
    
new_dict = {}  # key: Name; Value dictionary of dictionaries of type
for row, data in row_dict.items():
    rname = data['Name']
    rtype = data['Type'] 
    rdesc = data['Description']
    if data['Name'] not in new_dict:
        new_dict[rname] = {rtype:rdesc}
    else:
        new_dict[rname].update({rtype:rdesc})
        
for row, data in new_dict.items():  
        #pr_str = f"| {row} | "
        pr_str = ''.join([f' {d} |' for d in data.values()])
        print('| ' + row + ' | ' + pr_str)

Output:

| a | type_1 | aaa |
| a | type_2 | bbb |
| a | type_3 | ccc |
| b | type_1 | ddd |
| b | type_2 | bbb |
| b | type_3 | eee |


| a |  aaa | bbb | ccc |
| b |  ddd | bbb | eee |

score 0 · Answer 2 · answered Jun 15 '22 at 00:33

There is a KNIME node for such table transformation (pivot):

https://hub.knime.com/knime/extensions/org.knime.features.base/latest/org.knime.base.node.preproc.pivot.Pivot2NodeFactory

Pivot is also supported by pandas data frames:

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.pivot.html

Column renaming python | col1-keep, col2-headers, col3-values

2 Answers2