Scraping Columns with Pandas (Column Entries w/ more than 1 word writes two columns)

[BrandonKastning]

snippsat,

Thank you for this new knowledge and sharing this code. Wonderful example!

Without applying your teaching to my code (yet); I found a work around that managed to pull the data in.

I disabled:

 index=False

Flag/Parameter.

Then I had to do the following using Libre Calc

Once disabled; Python picked up the correct table with tables[0] (Strange; I am still unsure what index=False truly does at this time).

The output against the .ods it generated an additional row of header names. One that is different from another.

I tried removing the duplicate row w/ 3 column headings that were under a single header name. Libre Calc gave me an error; so I decided to try copy and paste and the following worked great for a CSV save in 7 steps.

Default Settings on Save As Dialogs were used and worked fine!















then I use step2.py as the Payload after Manually Naming the Column Headers in Libre Calc!

step1.py code (to generate the .ods):

import pandas as pd
url = "https://en.wikipedia.org/wiki/List_of_municipalities_in_Alabama"
tables = pd.read_html(url) 

df = tables[0]
#df = df.drop('Map', axis=1)

#df.to_excel("AL_Alabama_Cities.ods", index=False, engine="odf")
df.to_excel("AL_Alabama_Cities.ods", engine="odf")

step2.py code:

import pandas as pd
import mysql.connector
from sqlalchemy import create_engine

myd = pd.read_csv('AL_Alabama_Cities.CSV.csv')

engine = create_engine('mysql+mysqlconnector://brandon:[email protected]/Exodus_J3x_Dev_Bronson')

myd.to_sql(name='AL_Cities_CSV', con=engine, if_exists='replace', index=False)

Thank you again snippsat and everyone for this forum time/expertise!

Best Regards,

Brandon Kastning

Thank you again for this forum! How do I determine the tables[#]? Is it a guessing game or is is there an attribute or property within the browser code that could aid me in finding the correct tables[#]?

A web site can have many tables,so have to look at site(count) or test out like tables[0], tables[1],tables[6].... and see if get wanted result.

There is match in pandas.read_html that can use string or regex to match something i table wanted.
Example Timeline of programming languages ,let say we want Python table we can match name Guido van Rossum.

import pandas as pd
 
df = pd.read_html('https://en.wikipedia.org/wiki/Timeline_of_programming_languages', match='Guido van Rossum')
df[0].head(13)

Output:
	Year	Name	Chief developer, company	Predecessor(s)
0	1990	Sather	Steve Omohundro	Eiffel
1	1990	AMOS BASIC	François Lionet and Constantin Sotiropoulos	STOS BASIC
2	1990	AMPL	Robert Fourer, David Gay and Brian Kernighan a...	NaN
3	1990	Object Oberon	H Mössenböck, J Templ, R Griesemer	Oberon
4	1990	J	Kenneth E. Iverson, Roger Hui at Iverson Software	APL, FP
5	1990	Haskell	NaN	Miranda
6	1990	EuLisp	NaN	Common Lisp, Scheme
7	1990	Z Shell (zsh)	Paul Falstad at Princeton University	ksh
8	1991	GNU E	David J. DeWitt, Michael J. Carey	C++
9	1991	Oberon-2	Hanspeter Mössenböck, Wirth	Object Oberon
10	1991	Oz	Gert Smolka and his students	Prolog
11	1991	Q	Albert Gräf	NaN
12	1991	Python	Guido van Rossum	ABC, C

So if a match it will always be df[0].
Without match it would be table 9:

df[9].head(13)