Pandas multiple dataframes into single

Question

Been doing a lot of looking into this and still quite find the answer.

I have a list of scraped tables being pulled into Pandas via a read_html function. This returns a list of the dataframe objects. I can concat into a single dataframe but the issue is with the format of each of the table pre concat.

I need to Transpose and then concat but not sure how to Transpose when the first step is a read_html list of dataframes, the functionality can't work in this format. As a single table it is easy to set a new index and transpose into the exact format, just when in a bulk run that approach wont work. A column is essentially the headers.

The structure is as follows:

- 0 1 0 Id 470 1 Nome pozzo CLARA NORD 004 DIR A 2 Minerale GAS 3 Stato IN PRODUZIONE 4 Ubicazione MARE 5 Campo CLARA NORD 6 Concessione di coltivazione B.C 13.AS 7 Piattaforma marina CLARA NORD 8 Centrale di raccolta e trattamento FALCONARA 9 Operatore ENI 10 Anno di perforazione 2011 11 Profondit\\xe0 1.872 m

Desired output partial example:

Id Nome pozzo Minerale Stato Ubicazione 56 ALLI 004 OR OLIO IN PRODUZIONE TERRA

Single table code snippet:

#Table declared
 table = soup.find_all('table')
 table1 = str(table)
 df = pd.pandas.read_html(table1)[0] #limited for example
 df2 = pd.concat(df, ignore_index=True) #concat full list of df's
 df.columns = ['header', 'value'] #column header set for transpose
 df = df.set_index('header').T #index set and transposed

Hope you get the idea of the horizontal output. Issue is when I transpose the bulk the multiple columns/rows of the label to be the headers appear on every dataframe. There are several hundred dataframes to transpose and make one dataframe

Bulk code:

#Table declared
 table = soup.find_all('table')
 table1 = str(table)
 df = pd.pandas.read_html(table1)
 df2 = pd.concat(df, ignore_index=True) #concat full list of df's
 df2.columns = ['header', 'value'] #column header set for transpose

- header value 0 Id 56 1 Nome pozzo ALLI 004 OR 2 Minerale OLIO 3 Stato IN PRODUZIONE 4 Ubicazione TERRA 5 Campo MONTE ALPI-MONTE ENOC 6 Concessione di coltivazione VAL D\\\'AGRI 7 Centrale di raccolta e trattamento CENTRO OLIO VAL D\\\'AGRI 8 Operatore ENI 9 Anno di perforazione 2004 10 Profondit\\xe0 3.830 m 11 Id 588 12 Nome pozzo GELA 109 DIR 13 Minerale OLIO 14 Stato PRODUTTIVO NON EROGANTE 15 Ubicazione TERRA 16 Campo GELA TERRA 17 Concessione di coltivazione GELA 18 Centrale di raccolta e trattamento NUOVO CENTRO OLIO GELA 19 Operatore ENI MEDITERRANEA IDROCARBURI 20 Anno di perforazione 1997 21 Profondit\\xe0 3.493 m 22 Id 705 23 Nome pozzo LUNA 010 DIR A 24 Minerale GAS 25 Stato PRODUTTIVO NON EROGANTE 26 Ubicazione MARE 27 Campo LUNA 28 Concessione di coltivazione D.C 1.AG 29 Piattaforma marina LUNA A ... ... ...

Bulk could be like this:

Id Nome pozzo Minerale Stato Ubicazione 56 ALLI 004 OR OLIO IN PRODUZIONE TERRA
Id Nome pozzo Minerale Stato Ubicazione 54 test OR OLIO IN PRODUZIONE TERRA Id Nome pozzo Minerale Stato Ubicazione 53 test1 OR OLIO IN PRODUZIONE TERRA

Aim is to create this from the above bulk style.

Id Nome pozzo Minerale Stato Ubicazione 56 ALLI 004 OR OLIO IN PRODUZIONE TERRA
54 test OR OLIO IN PRODUZIONE TERRA 53 test1 OR OLIO IN PRODUZIONE TERRA

I assume this happens because of the two columns to start with the rows being the desired headers, hence why these are repeated every time as each dataframe will have a column 'header'.

I believe concat would work if in correct transposed format before. Please note some tables differ in columns, some have as many as 12 and some as little have 9. So for my 12 headings each dataframe value would populate unless that table didn't have that column so would null/naan that cell.

A previous option was to read each table into a df and append to a master but that was rather slow and unusable.

Any suggestions?

Answer 1

Function read_html return list of DataFrames, so you can loop in list comprehension, set_index and transpose. Last after concat add rename_axis for remove column name:

dfs = [df.set_index(0).T for df in pd.pandas.read_html(table1)]
df = pd.concat(dfs, ignore_index=True).rename_axis(None, 1)

print (df)
    Id    Nome pozzo Minerale                    Stato Ubicazione  \
0   56   ALLI 004 OR     OLIO            IN PRODUZIONE      TERRA   
1  588  GELA 109 DIR     OLIO  PRODUTTIVO NON EROGANTE      TERRA   

                   Campo Concessione di coltivazione  \
0  MONTE ALPI-MONTE ENOC               VAL D\\\'AGRI   
1             GELA TERRA                        GELA   

  Centrale di raccolta e trattamento                     Operatore  \
0          CENTRO OLIO VAL D\\\'AGRI                           ENI   
1             NUOVO CENTRO OLIO GELA  ENI MEDITERRANEA IDROCARBURI   

  Anno di perforazione Profondit\\xe0  
0                 2004        3.830 m  
1                 1997        3.493 m