Month: May 2022

SQL: Create a temporary table

On By Ben Larson Ph.D.In Analytics, Languages, MS SQL Server, sqlLeave a comment

Temporary tables are a great way of working on complex data requests. They are easy to create and they delete themselves after every session, so you do not have to worry about creating a big mess with a bunch of tables you need to go clean up later.

In this tutorial, I am going to use a real world example from my work in Verizon’s Cyber Security Department. This is a simplified version of ask, and I am using completely made up data. There is no data from Verizon on my website every. I simply discuss use cases to make learning analytics more grounded in the real world

Below is a list of dates, PhoneNum: phone numbers called about, and the CallerNum: the number the person is calling from. While there are many legitimate reasons for someone to call customer support from another number (I drop and break my phone so I borrow my co-workers phone and call customer support to request a replacement), a number that calls in repeatedly about many different numbers is a red flag of someone that could be a fraudster.

If you want to play along, you can download the data set here:

numberslistDownload

I am using MySql in this example as my database, but I will include the code for SQL Server, Teradata, and Oracle platforms as well.

So the ask is find CallerNum that is calling about many different PhoneNum

While I am sure you can make a complex subquery to do this job, but I’m going to show you how to use temporary tables to make this ask very simple:

As you can see above, I loaded the data into a table called dbtest.numberslist

Now to find out how many CallerNum are calling about multiple PhoneNum, a simple solution is to get a list of all distinct combinations of PhoneNum and CallerNum and then do a count of CallerNums from this distinct list. Since the list is distinct, a CallerNum calling in about the same PhoneNum will only appear once, so a CallerNum calling about multiple PhoneNums will appear multiple times.

So using temporary tables, I will create a table that holds the distinct call combinations

MySql (code is create temporary table <table name> then query to fill table

create temporary table distCalls
select distinct phonenum, callerNum from dbtest.numberslist;
Select * from distCalls -- shows what is in the table now

Now, lets see if we can find potential fraud callers, let us do a count of callerNum from the distinct temporary table

As you can see above, there are 4 numbers that have called about 4 distinct phone numbers during this time period. Again, this could be for legitimate reasons, but this is still something we look at when trying to find questionable activity.

MySQL Code

create temporary table distCalls select distinct phonenum, callerNum

from dbtest.numberslist;

SQL Server

Select distinct PhoneNum, CallerNum 
into #distCalls
from dbtest.numberslist
go 

#tableName -- indicated temporary tables in SQL Server

Teradata

Create volatile table distCalls as (
select distinct PhoneNum, CallerNum
from dbtest.numberslist)
with data
on commit preserve rows;

with data and on commit preserve rows are needed at the end if you want any data to be in your table when you go to use it

Oracle

Create private temporary table distCalls as
select distinct PhoneNum, CallerNum
from dbtest.numberslist;

Remember, temp tables delete themselves after each session (each time you log off the database). If you are working in the same session and need to recreate the temp table for some reason, you can always drop the table just as you would any other table object in SQL.

Where to find real data sets for data science training

On By Ben Larson Ph.D.In Analytics, machine learningLeave a comment

Practice makes perfect. But finding good data sets to practice with can be a pain. Here my current list for best places to find practice data sets:

Python Web Scraping: Using Selenium to automate web

On By Ben Larson Ph.D.In Analytics, Python, web scrapingLeave a comment

This is follow up to how to connect to Chrome using Selenium. If you do not know how to get to a website on Chrome using Selenium, go here

To refresh. here is the code we used to open up a web page (in this case Wikipedia’s home page)

If you run this code, you should find yourself on the home page for Wikipedia

Okay, so now lets learn how to interact with page, the first thing I am going to do is to select the English language version of the page. There are a few ways go about this, but one of the easier approaches is to look at the HTML code that creates the page and to use xpaths or titles to find the object you are looking at.

Right click on the link for English and click inspect from the drop down.

If you get a body link first, you might need to right click and hit inspect again

To check if you have the right element, hover your mouse over it, and it will be highlighted on the webpage

Once you have the right element, right click on it, go to copy>Copy Xpath

Chose Xpath, not full Xpath, it makes for easier coding. You XPath should look something like this: //*[@id=”js-link-box-en”]/strong * When you go to try this, your XPath may look different. As websites are constantly updated, many of the Xpaths get updated as well. Go with the one you find when you Inspect the HTML code yourself

Now we are going to use selenium to “Find” the element we want. The code is dr.find_element_by_XPath(‘//*[@id=”js-link-box-en”]/strong’) *Note the use of single quote around the XPath, it is better to use them as many XPaths will contain double quotes

Once you have run that code, Selenium knows what element you are looking at, you can interact with it now. Let’s “click” the link

Note something i did in the code, I added a link= before my find element command. This assigned the element now to a variable. I can now use the “click()” method the variable inherited from the selenium.webdriver object to click on the English link

I could have just done this: dr.find_element_by_xpath(‘//*[@id=”js-link-box-en”]/strong’).click()

But by assigning the variable it is a) cleaner code and b) the link can be reused by my code later. Remember, it is a law of programming that you will always have to go back and fix something you haven’t seen in 6 months, so make the code as clean as possible to make future you less likely to develop a drinking problem due to having to fix poorly written code.

If you run the code above, you will move to the home English page

Lets try one more thing, lets typing a search into the search bar:

Right click > inspect the search bar, then right click>copy>copy xpath the selection in the HTML code

Now that you have the XPath, lets use the find_element_by_xpath code and a new command, send_keys() to input characters into the search box

Finally, right click on the magnifying glass>inspect>copy>copy Xpath and let us click on it to finish our search. (remember to hover over to make sure you have the right link)

Now you should find yourself on the Data Science page of Wikipedia

Now remember — the xpaths I have on this page will likely be out of date by the time you try this, so make sure to inspect the elements and get the correct XPaths for this work for you.

Python Web Scraping / Automation: Connecting to Chrome with Selenium

On By Ben Larson Ph.D.In Python, web scrapingLeave a comment

Selenium is a Python package that allows you to control web browsers through Python. In this tutorial (and the following tutorials), we will be connecting to Googles Chrome browser, Selenium does work with other browsers as well.

First you will need to download Selenium, you can use the following commands depending on your Python distribution

c:\> Pip install selenium

c:\> Conda install selenium

If you are on a work computer or dealing with a restrictive VPN, the offline install option may help you: Selenium_Install_Offline

Next you need to download the driver that let’s you manage Chrome through Python.

Start by determining what version of Chrome you have on your computer

Click on the three dots in the right corner of your Chrome browser, select Help> About Google Chrome

Go to https://chromedriver.chromium.org/downloads to download the file that matches your Chrome version. (note, this is something you will need to do every time Chrome is updated, so get used to it.)

Open up the zipfile you downloaded, you will find a file called chromedriver.exe

Put it somewhere you can find, put in the following code to let Python know where to find it. 

from selenium import webdriver
options = webdriver.ChromeOptions()
options.add_argument("--start-maximized")
dr = webdriver.Chrome('C:/Users/larsobe/Desktop/chromedriver.exe',chrome_options=options)

Now to see if this works, use the following line, (you can try another website if you choose)   

Note the message Chrome is being controlled by automated test software.

You are now running a web browser via Python.