How to save multiple pandas dataframes to Excel

Question

I want to use pandas to extract web table content.

Here are the code:

import pandas as pd
import requests

ids = [1722044,1722045,1722046,1722047]
str = 'http://vip.win007.com/AsianOdds_n.aspx?id={}'
for id in ids:
  url = str.format(id)
  asianodds = pd.read_html(url, header = 0)
  asianodds[0]
  df_NaN = asianodds[0]

#Data cleaning
  asianodds = df_NaN.drop(df_NaN[df_NaN.多盘口 == '多盘口'].index)

#Delete Columns
  asianodds.drop('多盘口', inplace=True, axis=1)
  asianodds.drop('历史资料', inplace=True, axis=1)

# 澳门 data 
  macau_asianodds = asianodds.iloc[0:1]
  asianodds.iloc[0:1].to_excel("c:/logs/test.xls")
  print(macau_asianodds)

Expected output is 4 records. But, the Excel file had just one record.

Answer 1

• Currently you code overwrites the file each time asianodds.iloc[0:1].to_excel("c:/logs/test.xls") runs in the loop. The data is not appended to the file.
• Combine all the data into a single dataframe and then save it to Excel.
• Also see Appending Pandas DataFrame to existing Excel document as an alternate solution.

ids = [1722044,1722045,1722046,1722047]
str = 'http://vip.win007.com/AsianOdds_n.aspx?id={}'

# add the dataframe for each ids, to a list
df_list = list()

for id in ids:
    url = str.format(id)
    asianodds = pd.read_html(url, header = 0)
    asianodds[0]
    df_NaN = asianodds[0]

    #Data cleaning
    asianodds = df_NaN.drop(df_NaN[df_NaN.多盘口 == '多盘口'].index)

    #Delete Columns
    asianodds.drop('多盘口', inplace=True, axis=1)
    asianodds.drop('历史资料', inplace=True, axis=1)

    # 澳门 data 
    macau_asianodds = asianodds.iloc[0:1]

    # save each dataframe to a list
    df_list.append(asianodds.iloc[0:1])

# combine the dataframes
df = pd.concat(df_list).reset_index(drop=True)

# save dataframe to an excel file
df.to_excel("c:/logs/test.xls", index=False)

display(df)

  博彩公司    初盘    初盘.1   初盘.2    终盘   终盘.1  终盘.2
0   澳门     0.94      半球     0.92   1.02     半球     0.84
1   澳门     0.96  平手/半球    0.90   0.88      平手    0.98
2   澳门     0.88      半球     0.98   0.90     半球    0.96
3   澳门     0.94   受让一球    0.92   1.08  受让一球    0.78