Best Of

Just to clarify, but is StudentIndex always populated and is it unique for each partition?

Hey kpmwinston - I recently had this same requirement and solved it via MagicETL and using the Dynamic Unpivot + Python Scriprting blocks. The idea goes something like this:

1. Add dataset A as a datasouce in MagicETL
2. Add dataset B as a 2nd datasource in MagicETL
3. Drag two unpivot blocks into your flow, and connect one to Dataset A, one to Dataset B
4. This will transform both datasets a 'vertical' stacked dataset (turning your rows into columns)
5. So both datasets should now be laid out like this:

In your case, as part of setting up the unpivot block, the product column above would be your column names (Customer Name, Customer Address), and sales column above would be equivalent to their values.

1. Connect both unpivot blocks to python scripting blocks and leverage the fuzzywuzzy python library to compare the 'values' from dataset A and dataset B to get a confidence score.
2. This same process could be done on the column names if you wanted to do a fuzzy match to determine if the different column names between both datasets are related. If they are you could leverage a bit more python to conform the column names between both datasets automatically.

I won't go into a lot of detail on the step above, but the following link provides some sample MagicETL python scripting using the fuzzy wuzzy python library that outputs the matches and confidence scores that you can then connect to an output dataset. In the example link, this person is trying to match addresses from 2 different sources where the addresses are very similar but may not match 100% (so you cant leverage standard joins between them) which sounds nearly identical to what you're trying to achieve.

I know this isn't a detailed step by step, but it should get you close enough to an actual solution to your problem.

Get Help | DataCrew (circle.so)

Hi pstrauss,

You can do this in a MagicETL. The best way I've found to do this is using the Python Scripting Block. In the example I put together for you, I wanted to make it simple to configure which words end up capitalized without having to reconfigure my ETL every time I want to add a new preposition. In the design pattern below, I just used a Domo Webform to store a list of my words so all you need to do is pop new values into it.

1) There are 2 data sources in this ETL. The first is a table containing rows of articles text. The second is my dataset of prepositions.

2) Connect both sources to a Python Scripting block and add the python code shown in the screenshot at the bottom of this post.

3) Make sure you change any references of 'Articles' or 'Prepositions' to match your dataset names. Then, change 'ArticleText' in the code to whatever the column name is in your articles dataset.

4) Once you make sure all dataset name and column name references match your existing datasets, run a preview and check the 'SCHEMA' tab on the Python Script block to ensure it's properly picking up the output schema.

You'll notice a simple capitalise() function. All it does is take your prepositions dataset and convert it into a list. Then it takes your article dataset and converts the article text itself into a list. It then goes through your article text and checks to see each word is in your prepositions list. If it is, it capitalises it with the title() function. If not, it leaves that word unmodified.

What you are left with is an output dataset where the article text has your prepositions capitalized because we've run a pandas apply() function to your article data and telling it to execute the capitalise() function on your data.

Edit: I just realized you wanted to opposite of capitalizing, and instead lowercasing your prepositions. Thats easy enough to do by changing the "v.title() if v in list..." to "v.lower() if v in list..."

Apologies for the mix up! This should still get you 99% of the way there. I hope this helps! :)

Python Script

Articles Dataset

Prepositions Dataset

Final ETL Output

Notice all instances of and, to, from, the, with are now all capitalized