Scrape Wikipedia table to CSV in Python

Question

I'm scraping a table from Wikipedia using python. I'm done with the code, but I'm having some issues extracting specific columns to CSV, and adding enclosing double apostrophes.

I want to extract for only the following column names:

Kode BPS, Nama, Ibu Kota, Populasi, Luas, Pulau.

Here's the result of the table:

And here's my code:

import requests
from bs4 import BeautifulSoup
import pandas as pd

URL = 'https://id.wikipedia.org/wiki/Demografi_Indonesia'
response = requests.get(URL)
soup = BeautifulSoup(response.text,'html.parser')

table = soup.find('table',{'class':'wikitable sortable'}).tbody
rows = table.find_all('tr')
columns = [v.text.replace('\n','') for v in rows[0].find_all('th')]

df = pd.DataFrame(columns=columns)

for i in range(1,len(rows)):
    tds = rows[i].find_all('td')

    if len(tds)==4:
        values = [tds[0].text, tds[1].text, tds[2].text, tds[3].text.replace('\n',''.replace('\xa0',''))]
    else:
        values = [td.text.replace('\n',''.replace('\xa0','')) for td in tds]

    df = df.append(pd.Series(values, index=columns), ignore_index=True)
    #print(df)

    df.to_csv(r'C:\Users\Desktop\'+'\\report.csv',index=False)

score 3 · Answer 1 · answered Mar 11 '19 at 23:26

3

How about this?

import pandas as pd
link = "https://id.wikipedia.org/wiki/Demografi_Indonesia"
tables = pd.read_html(link,header=0)[2]
df.to_csv(tables, sep='\t')

Keep it simple.

answered Mar 11 '19 at 23:26

ASH

20,759
19
87
200

1

Last line should be tables.to_csv("wiki.csv", sep='\t') – EddyWD Jan 04 '22 at 17:34

score 1 · Accepted Answer · answered Feb 20 '19 at 05:02

1

You can specify the columns in the dataframe as:

columns = ['Kode BPS', 'Nama', 'Ibu Kota', 'Populasi', 'Luas', 'Pulau']
df = pd.DataFrame(columns=columns)

Then just insert the values required.

answered Feb 20 '19 at 05:02

Sanip

1,772
1
14
29

score 1 · Answer 3 · answered Aug 25 '19 at 04:59

To convert Wikipedia page's tables to CSV (pd.read_html()) (df.empty) (df.to_csv()):

import pandas as pd

def wiki_to_csv(wikiurl = str):
    tname  = link.split("/")[-1]
    tables = pd.read_html(link, header=0)

    for i in range(len(tables)):
        if not tables[i].empty:
            fname = tname + " table " + str(i)
            tables[i].to_csv(fname, sep=',')

To scrape that exact table and select specific columns (df.rename()) (Select Columns):

import pandas as pd

link = "https://id.wikipedia.org/wiki/Demografi_Indonesia"
df = pd.read_html(link, header=0)[2]

df = df.rename(columns={'Populasi[4]':'Populasi', 'Luas (km²)[5]':'Luas'})
df = df[['Kode BPS', 'Nama', 'Ibu kota', 'Populasi', 'Luas', 'Pulau']]

df.to_csv("Indonesia.csv", sep=',')

I'm not sure what issue you are having with double quotes.

Scrape Wikipedia table to CSV in Python

3 Answers3