Need to map positional data to property names?

Fredrik_Svensson · February 7, 2025, 7:43pm

This is what I could to with Python. Any way to this in EasyMorph?

import json

Column mapping based on schema

column_names = [
"status", "id", "category_id", "active", "main_category",
"images", "product_name_en", "related_items", "product_long_description_en",
"brand", "mpn", "product_files", "document_link_name_en",
"product_name_edited_en", "product_short_description_en",
"tech_data_en", "composition_en", "brochures_data_sheets",
"rec_price", "volt_en", "country_of_origin", "hs_code",
"gtin", "weight", "meta_text_en", "product_feed",
"google_taxonomy", "supplier_part_number", "ai_snippet_en",
"ai_write_request", "source_language", "translation_requests",
"hide", "web_unit_en"
]

Sample data

data_array = [
"M", "31791", "0", "1", "",
[["M", "f24b65933296dju25b1f9c5e71efc1a86", "https://d7rh5s3nxmimagen_01.jpg"]],
"Kootector 12 x 23 mm", , "", "SBC", "P1249", , "", "", "", "", "",
, "12", "", "", "", "8427327258679", "", "", "", "", "", "", , , , "", ""
]

Convert array to dictionary

mapped_data = {}
for i, key in enumerate(column_names):
mapped_data[key] = data_array[i]

Handle nested images

if isinstance(mapped_data["images"], list) and mapped_data["images"]:
mapped_data["images"] = [
{"status": img[0], "id": img[1], "org": img[2]} for img in mapped_data["images"]
]

Convert product files (if needed)

if isinstance(mapped_data["product_files"], list) and mapped_data["product_files"]:
mapped_data["product_files"] = [
{"status": file[0], "id": file[1], "file": file[2]} for file in mapped_data["product_files"]
]

Print JSON output

print(json.dumps(mapped_data, indent=2))

dgudkov · February 8, 2025, 3:40pm

Alas, I don't read Python. Maybe someone else can.

Also, try to re-phrase your question without using a programming language. In most cases, it's sufficient to provide a sample input, describe the necessary transformation logic, and a sample result that is obtained from the sample input.

Fredrik_Svensson · February 8, 2025, 10:54pm

Column Mapping Based on Schema**

A list of column names (column_names) is defined. These represent the expected schema of the dataset.
A sample data array (data_array) is given, containing product-related data field
Mapping Data to a Dictionary**
The script iterates through the column_names list and assigns corresponding values from data_array to a dictionary called mapped_data.
This ensures that each column name is mapped to the correct data position in data_array.

mattf · February 8, 2025, 11:39pm

Hi,
It appears your python is converting a data array into JSON, using another array to provide the JSON entity names and handling some nesting within the array for images and files.

Where would the data array be coming from? And is your end goal for it to be output in JSON?

Regards
Matt

Fredrik_Svensson · February 9, 2025, 7:27am

There are two arrays. One with the Property names and one with the actually data. The end result would be in a table if possible. I could do this outside of EM, but it would be much easier, if possible if it could be done within EM.

dgudkov · February 10, 2025, 4:45am

Here is a sample project that does the following:

maps an array to a list of column names, making the array a dictionary
if images exist, makes then an embedded JSON array

The mapping is easy - you just append a column with array values to the column with dictionary keys. BTW, in your example, the lists have different lengths - 34 column names, but only 33 array values.

Handling "images" takes a bit more effort but is still straightforward.

The result is a JSON. You can remove the last step to keep it as a table.

I didn't add logic to handle "product files". However, it's similar to "images" so you can add it easily.

array-to-json.morph (10.8 KB)

Hope that helps

Fredrik_Svensson · February 10, 2025, 7:25pm

Thanks! Mapping is easy, however this is not standard JSON, I am not able to get the data in that way. Is there a way of include some other code to parse the input as Array and Column Names. The solution needs to handle different number of Properties, sometimes its 50 and sometimes its 150.

dgudkov · February 10, 2025, 7:38pm

I'm not sure I understand. What exactly is not standard JSON? In what way you can get the data?

I don't understand that either. Can you please explain it with an example?

Fredrik_Svensson · February 10, 2025, 8:26pm

I solved it in a different way, but now I’m wondering how I can clone the Property table's data so that I can map it to the actual data in the Data table. In the Property table, I only have as many rows as there are properties, but in the Data table, I have all the data. I was looking for a function that could replicate the Properties dataset X number of times.

dgudkov · February 10, 2025, 8:51pm

Enumerate rows in both tables and do a merge, or use "Cross-merge" to duplicate rows.

Fredrik_Svensson · February 11, 2025, 11:43pm

The only trick right now is that the Pivot functionality is only give me one row back even that I have 10 of thousands of rows. Is this a feature or bug?

dgudkov · February 12, 2025, 12:17am

You can see the description of the "Pivot" action in the web-help: transformations:pivot [EasyMorph Help]

Fredrik_Svensson · February 12, 2025, 3:17pm

Thanks! It says: "This action is used to make aggregated, cross-tabular tables from data lists." I dont want a aggregated, I dont want to be as a Pivot.

dgudkov · February 12, 2025, 3:18pm

A pivot is typically an aggregation. Can you provide an example of what you need to achieve?

Fredrik_Svensson · February 12, 2025, 6:40pm

Data:
Property,Data
Product ID,30794
Quantity,1
Image ID,fa4d6c0acee9506922bdda07ff33d1bc
Image URL,https://yyy.cloudfront.net/CMP9893/30/WSB.jpg
Product Name,High Temperature Wire Stripper Station - 120V
Description,A high-precision soldering station designed for working with SMD components, featuring ergonomic design and power efficiency
Brand,ABC
Model, ZSB-1B
Category,Soldering Equipment
Voltage, 120V
EAN,8427327570276
Property,Data
Product ID,30794
Quantity,1
Image ID,fa4d6c0acee9506922bdda07ff33d1bc
Image URL,https://yyy.cloudfront.net/CMP9893/30/WSB.jpg
Product Name,High Temperature Wire Stripper Station - 120V
Description,A high-precision soldering station designed for working with SMD components, featuring ergonomic design and power efficiency
Brand,ABC
Model, ZSB-1B
Category,Soldering Equipment
Voltage, 120V
EAN,8427327570276

Pivot:
Product ID,Quantity,Image ID,Image URL,Product Name,Description,Brand,Model,Category,Voltage,EAN
30794,1,fa4d6c0acee9506922bdda07ff33d1bc,https://yyy.cloudfront.net/CMP9893/30/WSB.jpg,High Temperature Wire Stripper Station - 120V,"A high-precision soldering station designed for working with SMD components, featuring ergonomic design and power efficiency",ABC,ZSB-1B,Soldering Equipment,120V,8427327570276
30795,2,b1d2c3f4a5e67890123456789abcdef0,https://yyy.cloudfront.net/CMP9893/31/WSB.jpg,Advanced Soldering Station - 230V,"A high-performance soldering station with precise temperature control, designed for professionals.",XYZ,ZSB-2C,Soldering Equipment,230V,8427327570277
30796,5,c1d2e3f4a5b67890123456789abcdef1,https://yyy.cloudfront.net/CMP9893/32/WSB.jpg,Portable Soldering Iron - 18V,A lightweight and portable soldering iron with fast heating capabilities for fieldwork.,DEF,PSI-18V,Soldering Tools,18V,8427327570278

Fredrik_Svensson · February 12, 2025, 7:09pm

In C#.Net it could look like this:

"using System;
using System.Collections.Generic;
using System.IO;
using Newtonsoft.Json;

class Product
{
public Dictionary<string, string> Properties { get; set; } = new();
}

class JSONParser
{
public static List ParseJSON(string filePath, Dictionary<int, string> schema)
{
var products = new List();
var jsonData = File.ReadAllText(filePath);
var rawData = JsonConvert.DeserializeObject<List<List>>(jsonData);

    foreach (var item in rawData)
    {
        if (item.Count > 0)
        {
            var product = new Product();
            
            for (int i = 0; i < item.Count; i++)
            {
                if (schema.ContainsKey(i))
                {
                    product.Properties[schema[i]] = item[i]?.ToString();
                }
            }
            
            products.Add(product);
        }
    }
    
    return products;
}

}

class Program
{
static void Main()
{
string filePath = "data.json"; // Update with actual path

    // Schema defining column index to property names
    Dictionary<int, string> schema = new Dictionary<int, string>
    {
        {1, "ProductID"},
        {3, "Quantity"},
        {5, "ImageData"},
        {6, "ProductName"},
        {8, "Description"},
        {9, "Brand"},
        {10, "Model"},
        {18, "Voltage"},
        {20, "EAN"}
    };
    
    List<Product> products = JSONParser.ParseJSON(filePath, schema);
    
    foreach (var product in products)
    {
        Console.WriteLine("Product:");
        foreach (var kvp in product.Properties)
        {
            Console.WriteLine($"{kvp.Key}: {kvp.Value}");
        }
        Console.WriteLine();
    }
}

}
"

dgudkov · February 13, 2025, 12:08am

Use the "Any" aggregation function in Pivot when values are unique per group.

Here is an example:
pivot.morph (10.2 KB)

Note that Pivot puts columns in alphabetical order, by default, so a few more steps are required to put columns in the expected order.