If you want to learn about Data Analysis with Pandas and Python and you’re not familiar with Kaggle, check it out!
Time to read article : 5 mins
TLDR;
We show how to use idxmax and apply with Pandas
Introduction
Here we will look at some functions in Pandas which will help with ‘EDA’ – exploratory data analysis.
Using Kaggle is much like Jupyter Notebook
Once you have signed in you can locate Pandas tutorials and begin learning and testing your understanding by running through the exercises and if you get stuck there are hints, and also the solution.
Pandas ‘idxmax’ example:
One such exercise is shown here, where you are asked:
Which wine is the “best bargain”? Create a variable bargain_wine with the title of the wine with the highest points-to-price ratio in the dataset.
The Kaggle solution locates the row where idxmax is True, and returns the ‘title’
In this next example we see how you can “apply” a function to each row in your dataframe:
Using a function on all rows
Pandas ‘apply’ example:
“Find number of dealers by area”
import pandas as pd
import numpy as np
pd.set_option('display.max_colwidth', 150)
df = pd.read_csv("used_car.csv")
df.head(6)
Name
Address
Phone
0
PakWheels Karachi
Suit No : 303 Third Floor Tariq Centre Main Tariq Road
3105703505
1
PakWheels Lahore
37 Commercial Zone, Liberty Market Lahore
614584545
2
PakWheels Islamabad
37 Commercial Zone, Liberty Market Lahore
614584545
3
Sam Automobiles
8 Banglore town,7/8 Block near awami markaz shahrah e faisal karachi
3422804414
4
Marvel Motors
27-E, Ali Plaza, Fazal e Haq Road, Blue Area, Islamabad
518358006
5
Merchants Automobiles
Plot 167 /C Shop#4 Parsa City luxuria PECHS Block 3 at Main khalid bin waleed road
2134552897
In [152]:
# create a function to assign a city based on address detailsdef area(row):
if 'lahore' in (str(row.Address)).lower():
return "Lahore"
elif "faisalbad" in (str(row.Address)).lower():
return "Faisalbad"
elif "karachi" in (str(row.Address)).lower():
return "Karachi"
elif "islamabad" in (str(row.Address)).lower():
return "Islamabad"
else:
return "Other"
ans = df.apply(area, axis=1)
ans
0 Other
1 Lahore
2 Lahore
3 Karachi
4 Islamabad
...
2332 Other
2333 Other
2334 Other
2335 Other
2336 Other
Length: 2337, dtype: object
# Check how many times each city occurs in the dataframe
ans.value_counts()
Other 1433
Karachi 471
Lahore 331
Islamabad 102
dtype: int64
Next, what if we want to find out if there are other dealerships that use the same phone number?
Pandas ‘isin’ with ‘loc’
df.loc[df.Phone.isin(['614584545'])]
Name
Address
Phone
1
PakWheels Lahore
37 Commercial Zone, Liberty Market Lahore
614584545
2
PakWheels Islamabad
37 Commercial Zone, Liberty Market Lahore
614584545
437
Rehman Motors
Old Bahawalpur Road,Multan
614584545
We’ve found 3 business names, and 2 addresses that share the same phone number….
Summary
Kaggle is free and even if you are not pursuing a career in data science you can still gain valuable Python skills from it.
postgres=# c jml;
You are now connected to database "jml" as user "postgres".
jml=# CREATE TABLE PRODUCTS (id serial, product varchar(40), price money);
CREATE TABLE
jml=#
insert into table
jml=# INSERT INTO PRODUCTS (product,price) VALUES('jug',29.99);
INSERT 0 1
jml=# INSERT INTO PRODUCTS (product,price) VALUES('pot',9.99) RETURNING *;
id | product | price
----+---------+-------
2 | pot | £9.99
(1 row)
INSERT 0 1
select from a table
jml=# SELECT product FROM products WHERE price < '29.99';
product
---------
pot
(1 row)
store ip addresses in a table
jml=# CREATE TABLE inet_test (
jml(# address INET
jml(# );
CREATE TABLE
#### table created
jml=# d
List of relations
Schema | Name | Type | Owner
--------+-----------------+----------+----------
public | inet_test | table | postgres
public | products | table | postgres
public | products_id_seq | sequence | postgres
(3 rows)
jml=# INSERT INTO inet_test (address) VALUES ('192.168.1.0/24');
INSERT 0 1
jml=# INSERT INTO inet_test (address) VALUES ('172.16.11.0/24') RETURNING *;
address
----------------
172.16.11.0/24
(1 row)
Use CIDR notation
jml=# CREATE TABLE cidr_test (
jml(# address CIDR
jml(# );
INSERT INTO cidr_test (address) VALUES ('192.168.10/24');
INSERT INTO cidr_test (address) VALUES ('192.168.10');
INSERT INTO cidr_test (address) VALUES ('192.168.100.128/25');
INSERT INTO cidr_test (address) VALUES ('192.168.100.128');
jml=# INSERT INTO cidr_test (address) VALUES ('192.168.10/24');
INSERT 0 1
jml=# INSERT INTO cidr_test (address) VALUES ('192.168.10');
INSERT 0 1
jml=# INSERT INTO cidr_test (address) VALUES ('192.168.100.128/25');
INSERT 0 1
jml=# INSERT INTO cidr_test (address) VALUES ('192.168.100.128');
INSERT 0 1
jml=# select * from cidr_test;
address
--------------------
192.168.10.0/24
192.168.10.0/24
192.168.100.128/25
192.168.100.128/32
(4 rows)
Create postgres user for Python Script
Syntax =
CREATE USER user3 WITH LOGIN SUPERUSER CREATEDB CREATEROLE INHERIT NOREPLICATION CONNECTION LIMIT -1 VALID UNTIL '2025-04-03T11:50:38+05:30' PASSWORD 'password3';
Full commands to run to set up user on Raspberry Pi (aka 'The Server') :
postgres=# pi@pi4:~$ sudo -i -u postgres postgres-# postgres@pi4:~$ psql postgres-# psql (11.11 (Raspbian 11.11-0+deb10u1)) postgres-# Type "help" for help. postgres-# postgres=# CREATE USER user3 WITH postgres-# LOGIN postgres-# SUPERUSER postgres-# CREATEDB postgres-# CREATEROLE postgres-# INHERIT postgres-# NOREPLICATION postgres-# CONNECTION LIMIT -1 postgres-# VALID UNTIL '2025-04-03T11:50:38+05:30' postgres-# PASSWORD 'password3';
Check the new user has been added
\du
Install psycopg2 to connect to postgres using Python
Link Extractors are the objects used for extracting links from web pages using scrapy.http.Response objects. “A link extractor is an object that extracts links from responses.” Though Scrapy has built in extractors like scrapy.linkextractors import LinkExtractor, you can customize your own link extractor based on your needs by implementing a simple interface. The scrapy link extractor makes use of w3lib.url Have a look at the source code for w3lib.url : https://w3lib.readthedocs.io/en/latest/_modules/w3lib/url.html
# -*- coding: utf-8 -*-
#+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
#|r|e|d|a|n|d|g|r|e|e|n|.|c|o|.|u|k|
#+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
import scrapy
from scrapy import Spider
from scrapy import Request
from scrapy.crawler import CrawlerProcess
from scrapy.linkextractors import LinkExtractor
import os
class Ebayspider(Spider):
name = 'ebayspider'
allowed_domains = ['ebay.co.uk']
start_urls = ['https://www.ebay.co.uk/deals']
try:
os.remove('ebay2.txt')
except OSError:
pass
custom_settings = {
'CONCURRENT_REQUESTS' : 2,
'AUTOTHROTTLE_ENABLED': True,
'AUTOTHROTTLE_DEBUG': True,
'DOWNLOAD_DELAY': 1
}
def __init__(self):
self.link_extractor = LinkExtractor(allow=\
"https://www.ebay.co.uk/e/fashion/up-to-50-off-superdry", unique=True)
def parse(self, response):
for link in self.link_extractor.extract_links(response):
with open('ebay2.txt','a+') as f:
f.write(f"\n{str(link)}")
yield response.follow(url=link, callback=self.parse)
if __name__ == "__main__":
process = CrawlerProcess()
process.crawl(Ebayspider)
process.start()
Summary
The above code gets all of the hrefs very quickly and give you the flexibility to omit or include very specific attirbutes
If you are scraping a website with pop up messages asking you to agree to accept cookies.
This can prevent your scraper from continuing to the pages you want to scrape.
How can you get past this with your scraper if it is in an iframe?
How do you get past these?
Using Selenium you need to switch to the iframe (which you can identify using browser tools / inspect in Chrome).
Here is a video showing the general concept:
Scrape a site with pop up “accept cookies” message
If you just want the basic idea….
driver.switch_to.frame(iframe)
“click on accept or ok”
Instead of “click on accept or ok” you need to identify the @id, or @class name and use that in an XPATH so you can send the .click() commend with Selenium.
driver.switch_to.default_content()
Once past this you need to return to the default (or parent) content to carry on with the navigation towars the page you want to scrape.
driver = webdriver.Chrome(executable_path=path_chrome)
# find the frame using id, title etc.
frame = driver.find_elements_by_xpath("//iframe[@title='iframe_to_get']")
# switch the webdriver object to the iframe.
driver.switch_to.frame(frame[i])
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
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.
