Problem
I'm doing a bunch of research on a long list of companies and I want to know what their websites are. A good indicator that a company has be acquired is that either its website is missing or their compay website points to a company with a different name.
So I want to take a column of company names, submit them to a web search and get back the most likely website. I'm ok with imperfect reuslt, but it way better than seaching each one manually.
Solution
I'm starting with a column named "Companies" in my JMP table. I get the values as a list, and then iterate through the list with a search. I'm capturing the result of the r in a new list, and after the search is complete I use Set Values to make to add the values to an empty column in the original table.
It might be possible to do this all in JMP, but I didn't know how. I do however know how to run
I started out with their example, which requires a couple of packages: beautifulsoup4 and google (i.e. pip install google). This code returns one search result.
(This is Python)
try:
from google import search
except ImportError:
print("No module named 'google' found")
# to search
query = "SAS Institute inc"
for j in search(query, tld="co.in", num=1, stop=1, pause=2):
print(j)
So then I just wrapped the python code in a little JSL to manage getting the list, iterating through the search and the results.
names default to here(1);
outlist={}; clist=:Company<<get values();
for (i=1, i<=nitems(list), i++,
text=substitute("\[
try:
from google import search
except ImportError:
print("No module named 'google' found")
# to search
query = "xyzxyz"
for j in search(query, tld="co.in", num=1, stop=1, pause=2):
print(j)
]\" , "xyzxyz", as name(clist[i]) );
path=Get Current Directory();
save text file(path||"search.py", text);
x = RunProgram(
executable("/Users/user/anaconda/bin/python"),
options(path||"search.py")
,readfunction("text")
);
insert into(outlist,x);
);
show(outlist);
:websites<<set values(outlist);
The only real trick to keep an eye on is that the entire text of the python code is in the Substitute function. In the Substitute, "xyzxyz" is remove and replaced with the column value. In future versions of JMP (like v14) there are going to be some better ways to handle variables and working with python, but here I'm writing a text file, the python code to disk and then running the python program.
The program comes back with a value that I add to a list. One company name goes in and one item comes out. Because this is a 1:1, I can just put the list into a column and everything should line up. A few of my returned results needed work, but most came out perfect.
Discussion
I'm nearly 100% sure there are better solutions to this, but this took about 15 min to put together and and after running for 10 min I had a list that was about 99% done. So, it you've got something better that what I have, please, please post it. The flexibility to go back and forth between JMP and other packages, and to use other tools with JMP makes getting my work done a lot easier. In my example I had about 600 names to work through. The copy-paste tedium could have drug on for hours but with this I got all the work done and still had time to blog about it in less time that it would have taken me to do it manually. Plus, tomorrow when I get the second half of the list. I can just run the script again.
