Python - Parsing String using Regex or other method

user_19185123 0
Viewed 50

I have a python script parses excel data and formats it for further processing. Once of the excel cells has data that is just an array of fields.

My goal is to turn this mess into an simple bulleted list (grouping the different attributes by their function)

Example Cell Value :

"Additional Info":"Water Tight"|"Diameter":"3"|"Material":"Porcelain"|"Ship Carton Height":"18"|"Ship Carton Length":"11"|"Ship Carton Width":"18"|"Ship Weight":"10"|"Sold By":"S/2"|"Weight":"5"|"Assembly Instructions":"No"|"Care instructions":"Use a soft dry cloth"|"Color":"Blue"|"Contract Suitable As Is":"Yes"|"Country Of Origin":"PORTUGAL - PT"|"Finish Will vary":"Yes"|"Footprint":"1: 8.5 x 8
2: 13.5 x 6.5"|"Notes 1":"WATER TIGHT"|"Overall Diameter":"1: 6.5  Dia 
2: 8  Dia"|"Overall Dimensions Height":"H1: 13.5
H2: 8.5"|"Overall Dimensions Width":"W1: 6.5  Dia 
W2: 8  Dia"|"Overall Shape":"Round"|"RoHS":"No"|"Top Coat":"No"|"TruckShip":"Pass"

I'm trying to convert into the following format :

  • Additional Info : Water Tight
  • Diameter : 3
  • Overall Diameter : 1: 6.5 Dia 2: 8 Dia

I'm having trouble extracting the information that is between the quotes into variables.

So "Additional Info":"Water Tight" would produce 2 values, Additional Info = attribute name and Water Tight = attribute value.

What I tried : Converting the entire cell into a string and then splitting it on the pipe character and then again on the colon. However, this doesn't work when there is a value like : "Overall Dimensions Height":"H1: 13.5 H2: 8.5"

The other thing to note is that the values in the cell will change (somtimes there are more, sometimes there are less). But they always follow the same format : "Attribute Title ":"Attribute Value | "Attribute Title2 ":"Attribute Value2..."

Any ideas on how I can do this?

python
3 Answers
  1. Clean up your string so that it only contains the data you want to extract.
  2. Use split_input = str.split('|') to split on the pipe character.
  3. Use list comprehension to split each item in the resultant list into a key-value pair. Add 1 as the maxsplit argument to the split method to only split on the first occurrence of the colon.
kv_pairs = [item.split(':', 1) for item in split_input]
  1. Use dictionary comprehension to convert these key-value pairs into a dictionary object.
data = {key: value for key,value in kv_pairs}

This should provide you with the data in a format that can be easily manipulated in the manner you outlined. You'll likely want to remove your quotation marks from your strings. This can easily be done with str.replace('"', '').

user_174688630

For a possible regex solution, consider the following code as a starter for acquiring the data format you desire:

import re

string = '"Additional Info":"Water Tight"|"Diameter":"3"|"Material":"Porcelain"|"Ship Carton Height":"18"|"Ship Carton Length":"11"|"Ship Carton Width":"18"|"Ship Weight":"10"|"Sold By":"S/2"|"Weight":"5"|"Assembly Instructions":"No"|"Care instructions":"Use a soft dry cloth"|"Color":"Blue"|"Contract Suitable As Is":"Yes"|"Country Of Origin":"PORTUGAL - PT"|"Finish Will vary":"Yes"|"Footprint":"1: 8.5 x 8 2: 13.5 x 6.5"|"Notes 1":"WATER TIGHT"|"Overall Diameter":"1: 6.5 Dia 2: 8 Dia"|"Overall Dimensions Height":"H1: 13.5 H2: 8.5"|"Overall Dimensions Width":"W1: 6.5 Dia W2: 8 Dia"|"Overall Shape":"Round"|"RoHS":"No"|"Top Coat":"No"|"TruckShip":"Pass"'

matches = re.finditer(r'"(.+?)":"(.+?)"', string)
for match in matches:
    print(match)
    print(match.group(1))
    print(match.group(2))
    print()

Which outputs:

<re.Match object; span=(0, 31), match='"Additional Info":"Water Tight"'>
Additional Info
Water Tight

<re.Match object; span=(32, 46), match='"Diameter":"3"'>
Diameter
3

<re.Match object; span=(47, 69), match='"Material":"Porcelain"'>
Material
Porcelain

<re.Match object; span=(70, 95), match='"Ship Carton Height":"18"'>
Ship Carton Height
18

<re.Match object; span=(96, 121), match='"Ship Carton Length":"11"'>
Ship Carton Length
11

<re.Match object; span=(122, 146), match='"Ship Carton Width":"18"'>
Ship Carton Width
18

<re.Match object; span=(147, 165), match='"Ship Weight":"10"'>
Ship Weight
10

<re.Match object; span=(166, 181), match='"Sold By":"S/2"'>
Sold By
S/2

<re.Match object; span=(182, 194), match='"Weight":"5"'>
Weight
5

<re.Match object; span=(195, 223), match='"Assembly Instructions":"No"'>
Assembly Instructions
No

<re.Match object; span=(224, 266), match='"Care instructions":"Use a soft dry cloth"'>
Care instructions
Use a soft dry cloth

<re.Match object; span=(267, 281), match='"Color":"Blue"'>
Color
Blue

<re.Match object; span=(282, 313), match='"Contract Suitable As Is":"Yes"'>
Contract Suitable As Is
Yes

<re.Match object; span=(314, 349), match='"Country Of Origin":"PORTUGAL - PT"'>
Country Of Origin
PORTUGAL - PT

<re.Match object; span=(350, 374), match='"Finish Will vary":"Yes"'>
Finish Will vary
Yes

<re.Match object; span=(375, 413), match='"Footprint":"1: 8.5 x 8 2: 13.5 x 6.5"'>
Footprint
1: 8.5 x 8 2: 13.5 x 6.5

<re.Match object; span=(414, 437), match='"Notes 1":"WATER TIGHT"'>
Notes 1
WATER TIGHT

<re.Match object; span=(438, 478), match='"Overall Diameter":"1: 6.5 Dia 2: 8 Dia"'>
Overall Diameter
1: 6.5 Dia 2: 8 Dia

<re.Match object; span=(479, 525), match='"Overall Dimensions Height":"H1: 13.5 H2: 8.5"'>
Overall Dimensions Height
H1: 13.5 H2: 8.5

<re.Match object; span=(526, 576), match='"Overall Dimensions Width":"W1: 6.5 Dia W2: 8 Dia>
Overall Dimensions Width
W1: 6.5 Dia W2: 8 Dia

<re.Match object; span=(577, 600), match='"Overall Shape":"Round"'>
Overall Shape
Round

<re.Match object; span=(601, 612), match='"RoHS":"No"'>
RoHS
No

<re.Match object; span=(613, 628), match='"Top Coat":"No"'>
Top Coat
No

<re.Match object; span=(629, 647), match='"TruckShip":"Pass"'>
TruckShip
Pass

(the strange phenomenon with the quotes is brought about by how the printing of the match object cuts off at a certain length)

user_144383500

A csv.reader will work well here. Here's how to get started:

import csv

for row in csv.reader(bigmess.split('|'), delimiter=':'):
    print(row)

Here's the output:

['Additional Info', 'Water Tight']
['Diameter', '3']
['Material', 'Porcelain']
['Ship Carton Height', '18']
['Ship Carton Length', '11']
['Ship Carton Width', '18']
['Ship Weight', '10']
['Sold By', 'S/2']
['Weight', '5']
['Assembly Instructions', 'No']
['Care instructions', 'Use a soft dry cloth']
['Color', 'Blue']
['Contract Suitable As Is', 'Yes']
['Country Of Origin', 'PORTUGAL - PT']
['Finish Will vary', 'Yes']
['Footprint', '1: 8.5 x 8 2: 13.5 x 6.5']
['Notes 1', 'WATER TIGHT']
['Overall Diameter', '1: 6.5 Dia 2: 8 Dia']
['Overall Dimensions Height', 'H1: 13.5 H2: 8.5']
['Overall Dimensions Width', 'W1: 6.5 Dia W2: 8 Dia']
['Overall Shape', 'Round']
['RoHS', 'No']
['Top Coat', 'No']
['TruckShip', 'Pass']

Now format that data as needed.

user_4244990
Related