title: Make this code run for several excel files
body: <p>So I want to run this script for several excel files, so instead of df3, I will import several excel files, and merge all the results into a dataframe.</p>
  <p>Here is the main code example</p>
  <pre><code>
  import pandas as pd
  
  d = {'City': ['Tokyo','Tokyo','Lisbon','Tokyo','Tokyo','Lisbon','Lisbon','Lisbon','Tokyo','Lisbon','Tokyo','Tokyo','Tokyo','Lisbon','Tokyo','Tokyo','Lisbon','Lisbon','Lisbon','Tokyo','Lisbon','Tokyo'], 
       'Card': ['Visa','Visa','Master Card','Master Card','Visa','Master Card','Visa','Visa','Master Card','Visa','Master Card','Visa','Visa','Master Card','Master Card','Visa','Master Card','Visa','Visa','Master Card','Visa','Master Card'],
       'Colateral':['Yes','No','Yes','No','No','No','No','Yes','Yes','No','Yes','Yes','No','Yes','No','No','No','Yes','Yes','No','No','No'],
       'Client Number':[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22],
       'DebtPaid':[0.8,0.1,0.5,0.30,0,0.2,0.4,1,0.60,1,0.5,0.2,0,0.3,0,0,0.2,0,0.1,0.70,0.5,0.1]}
  
  df = pd.DataFrame(data=d)
  
  df2=df.groupby(['City','Card','Colateral'])['DebtPaid'].\
             value_counts(bins=[-0.001,0,0.25,0.5,0.75,1,1.001,2],normalize=True)
  
  d = {'City': ['Tokyo','Tokyo','Lisbon','Tokyo','Tokyo','Lisbon','Lisbon','Lisbon','Tokyo','Lisbon','Tokyo','Tokyo','Tokyo','Lisbon','Tokyo','Tokyo','Lisbon','Lisbon','Lisbon','Tokyo','Lisbon','Tokyo'], 
       'Card': ['Visa','Visa','Master Card','Master Card','Visa','Master Card','Visa','Visa','Master Card','Visa','Master Card','Visa','Visa','Master Card','Master Card','Visa','Master Card','Visa','Visa','Master Card','Visa','Master Card'],
       'Colateral':['Yes','No','Yes','No','No','No','No','Yes','Yes','No','Yes','Yes','No','Yes','No','No','No','Yes','Yes','No','No','No'],
       'Client Number':[23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44],
       'Total Debt':[100,240,200,1000,50,20,345,10,600,40,50,20,100,30,100,600,200,200,150,700,50,120]}
  
  df3 = pd.DataFrame(data=d)
  
  #First merge dataframes
  df_out = df2.rename('Prob').reset_index().merge(df3, on=['City', 'Card', 'Colateral'])
  
  #Use the right and left attributes of pd.Interval
  df_out['lower'] = [x.left for x in df_out['DebtPaid']]
  df_out['upper'] = [x.right for x in df_out['DebtPaid']]
  
  #Calculate lower and upper partial prices
  df_out['l_partial'] = df_out[['lower', 'Prob', 'Total Debt']].prod(axis=1)
  df_out['u_partial'] = df_out[['upper', 'Prob', 'Total Debt']].prod(axis=1)
  
  #Sum partial prices to get lower and upper price grouped on Client Number
  final = df_out.groupby('Client Number')[['l_partial', 'u_partial']]\
        .agg(lower_price=('l_partial', 'sum'),
             upper_price=('u_partial', 'sum')).clip(0,np.inf)
  
  
  
  w = (final['upper_price'].sum() + final['lower_price'].sum()) / 2 
  y = 1000
  z = ((w/y)-1)*100
  
  d1 = {'1': [w,y,z],
       'Index':['Estimate','Real','Error']}
       
  results = pd.DataFrame(data=d1).set_index('Index')
  print(results)
  
  </code></pre>
  <p>This is what I tried to do in order to run the script with several excel files without sucess:</p>
  <pre><code>files = [1,2,3,4,5]
  
  for x in files:
      df3 = pd.read_excel(str(x) + '.xlsx')
  
  #First merge dataframes
      df_out = df2.rename('Prob').reset_index().merge(df3, on=['City', 'Card', 'Colateral'])
  
  #Use the right and left attributes of pd.Interval
      df_out['lower'] = [x.left for x in df_out['DebtPaid']]
      df_out['upper'] = [x.right for x in df_out['DebtPaid']]
  
  #Calculate lower and upper partial prices
      df_out['l_partial'] = df_out[['lower', 'Prob', 'Total Debt']].prod(axis=1)
      df_out['u_partial'] = df_out[['upper', 'Prob', 'Total Debt']].prod(axis=1)
  
  #Sum partial prices to get lower and upper price grouped on Client Number
      final = df_out.groupby('Client Number')[['l_partial', 'u_partial']]\
        .agg(lower_price=('l_partial', 'sum'),
             upper_price=('u_partial', 'sum')).clip(0,np.inf)
  
  
      w = (final['upper_price'].sum() + final['lower_price'].sum()) / 2 
      y = 1000
      z = ((w/y)-1)*100
  
      d1 = {x : [w,y,z],
       'Index':['Estimate','Real','Error']}
       
      results = pd.DataFrame(data=d1).set_index('Index')
  
  results
  </code></pre>
  <p><a href="https://i.stack.imgur.com/19pZv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/19pZv.png" alt="enter image description here" /></a></p>
  <p>It only shows results for one excel file. Do you know how I can solve this?</p>