How to scrape data which is in HTML table format?

user_2230961 0
Viewed 85

I am trying to scrape data from the https://www.msamb.com/ApmcDetail/ArrivalPriceInfo website.

Here is the data which I want to scrape. So, in highlighted drop-down selection box there 148 commodities.

As of now, I am manually copying the data by selecting each individual commodities. This is taking a lot of manual effort to extract the data.

Commodities data snapshot

So, in order to make it automatic, I have started using Python. The following are the libraries I am using in Python (3.7.8) code.

  1. selenium
  2. BeautifulSoup
  3. pandas

This is my Python code.

from selenium import webdriver
from bs4 import BeautifulSoup
import pandas as pd

from selenium.webdriver.support.ui import Select
#from selenium.webdriver.common.by import By

driver = webdriver.Chrome(executable_path='G:/data/depend/chromedriver.exe')
driver.get('https://www.msamb.com/ApmcDetail/ArrivalPriceInfo/')

commodity = Select(driver.find_element_by_id("CommoditiesId"))

#able to select commodities by value
commodity.select_by_value('08005')

# Iterating over the all the commodity an fetching <td> element
for option in commodity.options:
    #print(option.text)
    soup = BeautifulSoup(option.text)
    print(soup)    
    rows = soup.select('tr')
    print(rows)
    for row in rows[1:]:
        td = row.find_all('td')
        print(td)
        APMC = td[0].text.strip()
        print(APMC)

Here, I am able to get the commodities by id equals to CommoditiesId from the drop-down selection box.

Once the list of commodities(148) fetched, I am trying to parse the HTML table content fetched for that particular commodity. Here I am able to print the commodity name for each iteration, but not able to print the APMC, Variety, Unit, Quantity, Lrate, Hrate, Modal columns data.

If above resolve then, I want the output in ~|~ delimited format and want to add two columns i.e. Date, Commodity. So, sample output will look like this (as of now, manually preparing below data file).

Date~|~Commodity~|~APMC~|~Variety~|~Unit~|~Quantity~|~Lrate~|~Hrate~|~Modal
    2020-07-11~|~APPLE~|~KOLHAPUR~|~QUINTAL~|~17~|~8500~|~14500~|~11500
    2020-07-11~|~APPLE~|~CHANDRAPUR-GANJWAD~|~QUINTAL~|~9~|~15000~|~17000~|~16000
    2020-07-11~|~APPLE~|~NASHIK~|~DILICIOUS- No.1~|~QUINTAL~|~60~|~9500~|~16000~|~13000
    2020-07-11~|~AMBAT CHUKA~|~PANDHARPUR~|~~|~NAG~|~7~|~10~|~10~|~10
    2020-07-10~|~AMBAT CHUKA~|~PUNE-MANJRI~|~~|~NAG~|~400~|~3~|~6~|~4
    2020-07-10~|~AMBAT CHUKA~|~PUNE~|~LOCAL~|~NAG~|~1300~|~4~|~5~|~4
pandas web-scraping selenium beautifulsoup python
2 Answers

You can save them into txt files and you can do something like this df = pd.read_csv("out.txt",delimiter='~|~') , or

date = df['Date'] commodity = df['Commodity']

you can append the apmc into list, and read_as dataframe at the end.

user_86937840

This script will go through all pages and saves them to standard csv and ~|~ delimited text file:

import requests
import numpy as np
import pandas as pd
from bs4 import BeautifulSoup


url = 'https://www.msamb.com/ApmcDetail/ArrivalPriceInfo'
detail_url = 'https://www.msamb.com/ApmcDetail/DataGridBind?commodityCode={code}&apmcCode=null'
headers = {'Referer': 'https://www.msamb.com/ApmcDetail/ArrivalPriceInfo'}

soup = BeautifulSoup(requests.get(url).content, 'html.parser')
values = [(o['value'], o.text) for o in soup.select('#CommoditiesId option') if o['value']]

all_data = []
for code, code_name in values:
    print('Getting info for code {} {}'.format(code, code_name))
    soup = BeautifulSoup(requests.get(detail_url.format(code=code), headers=headers).content, 'html.parser')

    current_date = ''
    for row in soup.select('tr'):
        if row.select_one('td[colspan]'):
            current_date = row.get_text(strip=True)
        else:
            row = [td.get_text(strip=True) for td in row.select('td')]
            all_data.append({
                'Date': current_date,
                'Commodity': code_name,
                'APMC': row[0],
                'Variety': row[1],
                'Unit': row[2],
                'Quantity': row[3],
                'Lrate': row[4],
                'Hrate': row[5],
                'Modal': row[6],
            })

df = pd.DataFrame(all_data)
print(df)
df.to_csv('data.csv')                                       # <-- saves standard csv
np.savetxt('data.txt', df, delimiter='~|~', fmt='%s')       # <-- saves .txt file with '~|~' delimiter

Prints:

...

Getting info for code 08071 TOMATO
Getting info for code 10006 TURMERIC
Getting info for code 08075 WAL BHAJI
Getting info for code 08076 WAL PAPDI
Getting info for code 08077 WALVAD
Getting info for code 07011 WATER MELON
Getting info for code 02009 WHEAT(HUSKED)
Getting info for code 02012 WHEAT(UNHUSKED)
            Date        Commodity          APMC Variety     Unit Quantity Lrate Hrate Modal
0     18/07/2020      AMBAT CHUKA    PANDHARPUR    ----      NAG       50     5     5     5
1     16/07/2020      AMBAT CHUKA    PANDHARPUR    ----      NAG       50     5     5     5
2     15/07/2020      AMBAT CHUKA    PANDHARPUR    ----      NAG      100     9     9     9
3     13/07/2020      AMBAT CHUKA    PANDHARPUR    ----      NAG       16     7     7     7
4     13/07/2020      AMBAT CHUKA          PUNE   LOCAL      NAG     2400     4     7     5
...          ...              ...           ...     ...      ...      ...   ...   ...   ...
4893  12/07/2020    WHEAT(HUSKED)        SHIRUR   No. 2  QUINTAL        2  1400  1400  1400
4894  17/07/2020  WHEAT(UNHUSKED)  SANGLI-MIRAJ    ----  QUINTAL      863  4000  4600  4300
4895  16/07/2020  WHEAT(UNHUSKED)  SANGLI-MIRAJ    ----  QUINTAL      475  4000  4500  4250
4896  15/07/2020  WHEAT(UNHUSKED)  SANGLI-MIRAJ    ----  QUINTAL      680  3900  4400  4150
4897  13/07/2020  WHEAT(UNHUSKED)  SANGLI-MIRAJ    ----  QUINTAL     1589  3900  4450  4175

[4898 rows x 9 columns]

Saves data.txt:

0~|~18/07/2020~|~AMBAT CHUKA~|~PANDHARPUR~|~----~|~NAG~|~50~|~5~|~5~|~5
1~|~16/07/2020~|~AMBAT CHUKA~|~PANDHARPUR~|~----~|~NAG~|~50~|~5~|~5~|~5
2~|~15/07/2020~|~AMBAT CHUKA~|~PANDHARPUR~|~----~|~NAG~|~100~|~9~|~9~|~9
3~|~13/07/2020~|~AMBAT CHUKA~|~PANDHARPUR~|~----~|~NAG~|~16~|~7~|~7~|~7
4~|~13/07/2020~|~AMBAT CHUKA~|~PUNE~|~LOCAL~|~NAG~|~2400~|~4~|~7~|~5
5~|~12/07/2020~|~AMBAT CHUKA~|~PUNE~|~LOCAL~|~NAG~|~1700~|~3~|~8~|~5
6~|~19/07/2020~|~APPLE~|~KOLHAPUR~|~----~|~QUINTAL~|~3~|~9000~|~14000~|~11500
7~|~18/07/2020~|~APPLE~|~KOLHAPUR~|~----~|~QUINTAL~|~12~|~8500~|~15000~|~11750
8~|~18/07/2020~|~APPLE~|~NASHIK~|~DILICIOUS- No.1~|~QUINTAL~|~110~|~9000~|~16000~|~13000
9~|~18/07/2020~|~APPLE~|~SANGLI-PHALE BHAJIPALAM~|~LOCAL~|~QUINTAL~|~8~|~12000~|~16000~|~14000
10~|~17/07/2020~|~APPLE~|~MUMBAI-FRUIT MARKET~|~----~|~QUINTAL~|~264~|~9000~|~12000~|~10500
...

Screenshot of csv file from LibreOffice:

enter image description here

user_100359850
Related