Categories
Python Code

Comparing values in SQL against previously scraped data

If you have scraped data on more than one occasion and want to check if a value has changed in a column since the previous scrape you could use this:

select col1, col2 

from TABLENAME 

group by col1, col2 

having count(col2) <2

This will compare and check if a value for col2 has changed since the previous scrape

Let’s put some real names into this:

select CLAIMNO, STATUSID 

from PREV 

group by CLAIMNO, STATUSID 

having count(STATUSID) <2

We can see from our results that from our entire comparison, only 1 claim has had a status change

We now know that since the last time we scraped the site, only one of our claims has been updated by “them”

This has been achieved by using “group by” – where we have many records in our table, and we’ve picked out where there are not 2 matching STATUSID values for a given CLAIM

Summary

We have used SQL to identify where a column value has changed compared to the previous time we checked by grouping 2 columns and checking if the count is less than 2.

If you would like more information please contact me via the contact page

See also : https://stackoverflow.com/questions/1786533/find-rows-that-have-the-same-value-on-a-column-in-mysql#1786568

Categories
Python Code

Parsing Scraped Data with Pandas

Once you have some data you’ll need to find the relevant parts and use them for further analysis. Rather than use the proverbial Excel sheet you can write code to automate the task.

Consider the following :

import pandas as pd
df=pd.read_csv('SalesRecords.csv')

The following code will match where the row contains “Dijbouti” and

return the value that is in the column “Order Priority provided you add “iloc[0]” or whatever index number you require.

df.loc[df['Country']=='Djibouti',['Order Priority']]
Categories
Python Code

Debugging Python Code

& using Snippets

This article will cover how to achieve the following, using Python :

  • Reduce the need for print statements
  • Reduce the need to type repetitive code

Why not automate some of your coding to allow you to automate the ‘boring stuff’ – anything that you do regularly could be saved as a snippet and you won’t have to type that section of code from scratch each time….

If you find you have print statements everywhere, or are repeating the same code in many projects then this will be of interest.

First, these are the 3 shortcuts you will want to memorise:

vscode shortcuts
Example of a snippet that provides “hello world” via intellisense – note you can also add variables within the snippet
“I Do the Code to Print Hello World”: {
        “prefix”: “hellw”,
        “body”: [
            “print(‘Hello World’)\n”,
            “print(‘Hello to you’, ${user_name})\n”,
            “$2”
        ],
        “description”: “This Snippet Prints Hello World”
    }
By typing “hellw” intellisense will insert the snippet of code that you have assigned to that name
This is the code that gets placed into your code that you are currently writing