text_html_output_1.png
import pandas as pd

url = 'https://www.news18.com/cricketnext/ipl-auction-2022/'

a = pd.read_html(url)
a

GT = a[1]
GT['Team'] = 'GT'
GT.rename(columns={'2022 Squad GT': 'Players'}, inplace=True)
GT

CSK = a[2]
CSK['Team'] = 'CSK'
CSK.rename(columns={'2022 Squad CSK': 'Players'}, inplace=True)
CSK

final = GT.append(CSK, ignore_index=True)
final

DC = a[3]
DC['Team'] = 'DC'
DC.rename(columns={'2022 Squad DC': 'Players'}, inplace=True)
DC

final = final.append(DC, ignore_index=True)
final

KKR = a[4]
KKR['Team'] = 'KKR'
KKR.rename(columns={'2022 Squad KKR': 'Players'}, inplace=True)
KKR

final = final.append(KKR, ignore_index=True)
final

PBK = a[5]
PBK['Team'] = 'PBK'
PBK.rename(columns={'2022 Squad PBKS': 'Players'}, inplace=True)
PBK

final = final.append(PBK, ignore_index=True)
final

LSG = a[6]
LSG['Team'] = 'LSG'
LSG.rename(columns={'2022 Squad LSG': 'Players'}, inplace=True)
LSG

final = final.append(LSG, ignore_index=True)
final

MI = a[7]
MI['Team'] = 'MI'
MI.rename(columns={'2022 Squad MI': 'Players'}, inplace=True)
MI

final = final.append(MI, ignore_index=True)
final

RCB = a[8]
RCB['Team'] = 'RCB'
RCB.rename(columns={'2022 Squad RCB': 'Players'}, inplace=True)
RCB

final = final.append(RCB, ignore_index=True)
final

RR = a[9]
RR['Team'] = 'RR'
RR.rename(columns={'2022 Squad RR': 'Players'}, inplace=True)
RR

final = final.append(RR, ignore_index=True)
final

SRH = a[10]
SRH['Team'] = 'SRH'
SRH.rename(columns={'2022 Squad SRH': 'Players'}, inplace=True)
SRH

final = final.append(SRH, ignore_index=True)
final

test = a[11]

test.rename(columns={'Base Price IN ₹ (CR.)': 'Base Price'}, inplace=True)
test.rename(columns={'Player': 'Players'}, inplace=True)
test.drop('Base Price IN $ (000)', axis=1, inplace=True)

test = test[['Players', 'Base Price', 'TYPE', 'COST IN ₹ (CR.)', 'Cost IN $ (000)', '2021 Squad', 'Team']]

final = final.append(test, ignore_index=True)
final

final.info()