Separate field name and value from long line in python

Question

I have python custom data like this:

---

(item_id[s]-b2tc34x;item_name[s]-Fan Blade;item_price[i]-2450)(item_id[s]-b3td42h;item_name[s]-Fan Cable)

note:

• parentheses () is used to denote each items.
• semi-colons i.e. ;, used to separate each field in an item.
• dashes i.e. -, will to associate field names and field values.
• field that has empty value will be marked as "NAN"

and the result should be like this:

---

item_id,item_name,item_price

---

b2tc34x,Fan Blade,2450

---

b3td42h, Fan Cable,NAN

---

How can I solve this problem? I've tried using split() but the result not same as what expected. And also how can I assign datatype for each header if given sample followed by datatype inside bracket ([s] string, [i] integer). please help. thank you in advance.

Answer 1

The code below should work if your data is clean enough. The logic is first to split the input string into items, and then use a dictionary to clean up the data for each item separately. If there are any items whose details are split across multiple lines, you can just join the lines into a long string, and input into the below directly.

import re
import numpy as np
import pandas as pd

str_input = '(item_id-b2tc34x;item_name-Fan Blade;item_price-2450)(item_id-b3td42h;item_name-Fan Cable)'
split_items_data = re.split(r'\(|\)',str_input)
split_items_data = [i.strip() for i in split_items_data if len(i) > 0]

def str2item(item_str):
    item_str = re.split(r';|-', item_str)
    item_str = [i.strip() for i in item_str if len(i) > 0]
    item_details = {}

    for field_name, field_value  in zip(item_str[::2], item_str[1::2]):
        item_details[field_name] = field_value

    for field_name in ['item_id', 'item_name', 'item_price']:
        if field_name not in item_details:
            item_details[field_name] = np.nan
    return item_details

df = pd.DataFrame([str2item(i) for i in split_items_data])

Answer 2

At a high level, here is what I would do:

1. use regex to discern each "item" (look at the re module, particularly the findall function). Hint: your regex expression should be something like (.*)
2. Then, for each "item string", split that string using ; as the delimiter to get the item's properties.
3. For each of those property strings, split using -.

In pseudo-code:

all_items = do_regex_matching_on_input_string()

for item in all_items:
    item_property_pairs_strings = split item string using ;
    
    for property_pair_str in item_property_pairs:
        current_property_pair = split property_pair_str using -
        
        item_prop_name = first component of current_property_pair
        item_prop_value = second component of current_property_pair