title: Export multiple html tables to Excel with Pandas as the Middleman
body: <p>I'm collecting data over the years 1981-2018 from a website, whereby the <a href="ftp://ftp.cpc.ncep.noaa.gov/htdocs/degree_days/weighted/daily_data/2018/Population.Heating.txt" rel="nofollow noreferrer">this link</a> shows the 2018 data:</p>
  
  <p>If one changes <code>2018</code> to a year from 1981-2018 in the aforementioned link one obtains the remaining dataset. </p>
  
  <p>Using <strong>Pandas</strong> and <strong>urllib.request</strong> I collect the data as follows:</p>
  
  <pre><code>url = ['ftp://ftp.cpc.ncep.noaa.gov/htdocs/degree_days/weighted/daily_data/' + \
     '{}'.format(i) + '/Population.Heating.txt' for i in range(1981,2019)]
  data_url = [pd.read_csv(url[i], sep=" ", header=None) for i in range(len(url))]
  </code></pre>
  
  <p><strong>Questions</strong></p>
  
  <p>First, is there a cleaner and more efficient way of collecting the data from the links than the above list comprehension? Second, how would I export the entire list comprehension to an Excel spreadsheet?</p>
  
  <p>I had tried the following method for exporting, however; the code only exported the year 2018:</p>
  
  <pre><code>from pandas import ExcelWriter
  
  writer = ExcelWriter('PythonExport.xlsx')
  for i in range(len(data_url)):
      data_url[i].to_excel(writer,'Sheet1')
  writer.save()
  </code></pre>
  
  <p>To address the question of why I didn't directly import the data to Excel: Ultimately, I would like to have the data for each year in a DataFrame, namely one column contains the Region' data and the other column contains the 'Conus' data. In trying to construct this DataFrame it seemed to be easier to munge the data in Excel than working with the list comprehension <code>data_url</code> from above, then use the data to build the DataFrame. </p>