Alteryx Designer Desktop Discussions

AConcernedAnalyst · ‎04-22-2022

Hi all,

Hope you are well. I have a column (called regiontables) of data that appears to have HTML/XML (not entirely sure which one) of a table in each record. My goal is to extract the bolded parts and create a table from this for each record. I have mainly been trying utilize beautifulsoup within the python tool, but I am not really having much luck (code posted below). I went straight to this approach because this is what google led me to, but I am open to any ways to solve this problem at this point. Any help would be extremely welcomed!

For example (dummy data):

record 1 for region 1 has this:

<TABLE cellSpacing=0 cellPadding=0 width="95%" border=1>
<TBODY>
<TR>
<TH bgColor=#cfdff4 width="11%">Retailer</TH>
<TH bgColor=#cfdff4 width="11%">Order No.</TH>
<TH bgColor=#cfdff4 width="11%">Invoice Number</TH>
<TH bgColor=#cfdff4 width="13%">Contact Date</TH>
<TH bgColor=#cfdff4 width="13%">Count</TH>
<TH bgColor=#cfdff4 width="42%">Item Description Description</TH></TR>
<TR>
<TD align=center>CA123</TD>
<TD align=center>12345</TD>
<TD align=center>147249</TD>
<TD align=center>20151221</TD>
<TD align=center>50</TD>
<TD>[51423] </TD></TR>
<TR>
<TD align=center>BB174</TD>
<TD align=center>47119B</TD>
<TD align=center>141149</TD>
<TD align=center>20151208</TD>
<TD align=center>50</TD>
<TD>[73892] 5th Gen & Ipad Cover</TD></TR>
<TR>
<TD align=center>MN149</TD>
<TD align=center>47233A</TD>
<TD align=center>147209</TD>
<TD align=center>20151023</TD>
<TD align=center>50</TD>
<TD>[378293] Xbox Controller</TD></TR></TBODY></TABLE>

record 2 for region 2:

<TABLE cellSpacing=0 cellPadding=0 width="95%" border=1>
<TBODY>
<TR>
<TH bgColor=#cfdff4 width="11%">Retailer</TH>
<TH bgColor=#cfdff4 width="11%">Order No.</TH>
<TH bgColor=#cfdff4 width="11%">Invoice Number</TH>
<TH bgColor=#cfdff4 width="13%">Contact Date</TH>
<TH bgColor=#cfdff4 width="13%">Count</TH>
<TH bgColor=#cfdff4 width="42%">Item Description Description</TH></TR>
<TR>
<TD align=center>CA555</TD>
<TD align=center>534234</TD>
<TD align=center>54678</TD>
<TD align=center>20151221</TD>
<TD align=center>50</TD>
<TD>[2123] </TD></TR>
<TR>
<TD align=center>BB174</TD>
<TD align=center>47119B</TD>
<TD align=center>141149</TD>
<TD align=center>20151208</TD>
<TD align=center>50</TD>
<TD>[73892] 5th Gen & Ipad Cover</TD></TR>
<TR>
<TD align=center>MN149</TD>
<TD align=center>47233A</TD>
<TD align=center>147209</TD>
<TD align=center>20151023</TD>
<TD align=center>50</TD>
<TD>[378293] PS5 Controller</TD></TR></TBODY></TABLE>

Python Code

# List all non-standard packages to be imported by your
# script here (only missing packages will be installed)
from ayx import Package
Package.installPackages(['pandas','numpy','bs4','lxml'])

from ayx import Alteryx

import pandas as pd
import numpy as np

df = Alteryx.read('#1')

df.shape

df.columns

desired_cols = ['REGION_TABLES]

df = df[desired_cols]

#Text parsing
from bs4 import BeautifulSoup as bs

soup = df.applymap(lambda df: bs(df).find('tbody'))

It is at this point where I get an error and this is as far as I could get

NameError                                 Traceback (most recent call last)
<ipython-input-2-70a343cf1c6e> in <module>
      1 #Find table
      2 
----> 3 soup = df.applymap(lambda df: bs(df).find('tbody'))
      4 
      5 print(soup)

NameError: name 'df' is not defined

Thank you!

AkimasaKajitani · ‎04-23-2022

Hi @AConcernedAnalyst ,

I think there is a problem at bellow point( After the REGION_TABLE there is needed the single quotation.).

desired_cols = ['REGION_TABLES]

But if it is the cause of this problem, Python tool shows the error at this point.

This is the workable code.

from ayx import Alteryx
import pandas as pd
import numpy as np


df = Alteryx.read("#1")
df.shape
df.columns
desired_cols = ['REGION_TABLES']
df = df[desired_cols]


#Text parsing
from bs4 import BeautifulSoup as bs

soup = df.applymap(lambda df: bs(df).find('tbody'))

flying008 · ‎04-23-2022

Hi,@AConcernedAnalyst

There is a so boring way for you.

录制_2022_04_23_16_10_14_520.gif

AConcernedAnalyst · ‎05-04-2022

Hi @flying008

Would you be able to share the regex that was used to parse out the details from each row?

flying008 · ‎05-04-2022

Hi,@AConcernedAnalyst

Regex:

1- Regular Expression: (=Calibri>)(.+)(<\/FONT>)

2- Output Method: Replace

3- Replacement Text: $2

4- Don't check 'Copy unmatched text to output.'

AConcernedAnalyst · ‎05-05-2022

@flying008

If possible, would you be able to post the WF of your solution? I am kind of lost on what the settings for some of the tools look.

flying008 · ‎05-05-2022

Hi, @AConcernedAnalyst

Unfortunately I can't provide WF due to compliance rules.

AConcernedAnalyst · ‎05-06-2022

Hi @flying008

Understood, no problem.

I was able to sort of get to the results I am looking for (like 85% of the way there) after using your posts, but did some things slightly different from the way you did things. I wonder if I can get an extra 5-10% if I follow steps more like you did.

Could you please let me know the configurations for the following tools?

- First text to columns tool ( I think it generates rows based off of line breaks \n)

- Dynamic select tool

flying008 · ‎05-06-2022

Hi,@AConcernedAnalyst

please see that.

录制_2022_05_07_08_25_58_854.gif

Alteryx Designer Desktop Discussions

Data Parsing from Column of HTML Code