Extract title and table body from multiple urls(using beautifulsoup) to dataframe

Question

I have multiple urls that I wish to scrape. I want to extract all the tables and their corresponding titles from those websites in one dataframe. I am able to extract the tables and their respective titles but I cannot append them both to the desired dataframe because the number of rows in each table varies. Here is the code that I have used:

urls = ['https://csr.gov.in/companyprofile.php?year=FY%202018-19&CIN=U15114DL1997PTC084568',
       'https://csr.gov.in/companyprofile.php?year=FY%202018-19&CIN=U85100DL2011PTC220589',
       'https://csr.gov.in/companyprofile.php?year=FY%202018-19&CIN=U72200DL2013NPL249988',
       'https://csr.gov.in/companyprofile.php?year=FY%202018-19&CIN=U70100MH2013PTC247696',
       'https://csr.gov.in/companyprofile.php?year=FY%202018-19&CIN=U72211KA1986PTC008043']
dataframes = []
for url in urls:
    response = requests.get(url)
    soup = BeautifulSoup(response.content, "html.parser")
    table = soup.findAll("table", {'class':'table'})[2] # Find the "table" tag in the page
    rows = table.find_all("tr") # Find all the "tr" tags in the table
    title = soup.find('h2').text  # Find all the title of the page
    cy_data = []
    for row in rows:
        cells = row.find_all("td") #  Find all the "td" tags in each row
        cy_data.append([cell.text for cell in cells]) # For each "td" tag, get the text inside it

    dataframes.append(pd.DataFrame(cy_data))
template = pd.DataFrame(data = [[pd.NA, pd.NA, pd.NA, pd.NA]], columns = [0,1,2,3])
dataframes= [i if not i.empty else template for i in dataframes]
data14 = pd.concat(dataframes)
data = pd.DataFrame(data14)
data

Output:(I have only pasted a few columns for better understanding)

0   None  None        None    
1   1     Promoting.. Education
2   2     Homes for.. Senior Citizens Welfare
3   Grand Total       None  
0   32,90 NaN         NaN
1   70,0  NaN         NaN   
2   0.00  NaN         NaN
3   0.00  NaN         NaN   
0   NaN   NaN         NaN
0   NaN   NaN         NaN   
0   None  None        None  
1   1     NA          Other Central Government Funds    
2   Grand Total       None

Desired Output:

0   None  None        None       Danisco (india) Private Limited               
1   1     Promoting.. Education  Danisco (india) Private Limited
2   2     Homes for.. Senior..   Danisco (india) Private Limited
3   Grand Total       None       Danisco (india) Private Limited
0   32,90 NaN         NaN        Khc Healthcare India Private Limited
1   70,0  NaN         NaN        Khc Healthcare India Private Limited
2   0.00  NaN         NaN        Khc Healthcare India Private Limited
3   0.00  NaN         NaN        Khc Healthcare India Private Limited
0   NaN   NaN         NaN        Goods And Services Tax Network
0   NaN   NaN         NaN        Wonder City Buildcon Private Limited
0   None  None        None       Waters (india) Private Limited
1   1     NA          Other..    Waters (india) Private Limited 
2   Grand Total       None       Waters (india) Private Limited

user_10035985 · Answer

You can use this example, how to parse first table for 2014-15:

import requests
import pandas as pd
from bs4 import BeautifulSoup


urls = ['https://csr.gov.in/companyprofile.php?year=FY%202018-19&CIN=U15114DL1997PTC084568',
       'https://csr.gov.in/companyprofile.php?year=FY%202018-19&CIN=U85100DL2011PTC220589',
       'https://csr.gov.in/companyprofile.php?year=FY%202018-19&CIN=U72200DL2013NPL249988',
       'https://csr.gov.in/companyprofile.php?year=FY%202018-19&CIN=U70100MH2013PTC247696',
       'https://csr.gov.in/companyprofile.php?year=FY%202018-19&CIN=U72211KA1986PTC008043']

all_data = []
for url in urls:
    print(url)

    soup = BeautifulSoup(requests.get(url).content, 'html.parser')
    panel = soup.select_one('.panel')
    table = panel.select_one('#datatable')

    # no first table found:
    if not table:
        all_data.append({
            'Title': soup.h2.text
        })
        continue

    # parse headers
    headers = [th.get_text(strip=True) for th in table.select('thead th')] + ['Title']

    # parse data rows
    for tr in table.select('tbody tr:not(:has(td[colspan]))'):
        row = [td.get_text(strip=True) for td in tr.select('td')] + [soup.h2.text]
        all_data.append(dict(zip(headers, row)))

df = pd.DataFrame(all_data)
df = df.drop(columns=['Feedback'])
print(df)
df.to_csv('data.csv')

Prints:

  S.No.                      CSR Project(s)           Development Sector(s)      State            District Project Amount Outlay  Amount Spent     Mode of Implementation                                  Title
0     1  Promoting education among children                       Education      Delhi  NEC/ Not mentioned            600,000.00    400,000.00  Other Implementing Agency        Danisco (india) Private Limited
1     2           Homes for Senior Citizens         Senior Citizens Welfare      Delhi  NEC/ Not mentioned          1,500,000.00  1,000,000.00  Other Implementing Agency        Danisco (india) Private Limited
2   NaN                                 NaN                             NaN        NaN                 NaN                   NaN           NaN                        NaN   Khc Healthcare India Private Limited
3   NaN                                 NaN                             NaN        NaN                 NaN                   NaN           NaN                        NaN         Goods And Services Tax Network
4   NaN                                 NaN                             NaN        NaN                 NaN                   NaN           NaN                        NaN   Wonder City Buildcon Private Limited
5     1                                  NA  Other Central Government Funds  PAN India  NEC/ Not mentioned                  0.00          0.00                   Combined         Waters (india) Private Limited

and saves data.csv (screenshot from LibreOffice):

Extract title and table body from multiple urls(using beautifulsoup) to dataframe

1 Answers