text: import pandas
  import numpy as np
  import statsmodels.api as sm
  import statsmodels.formula.api as smf
  import pandas as pd
  
  def pro():
      nobs = 100
      X = np.random.random((nobs, 2))
      X = sm.add_constant(X)
      beta = [1, .1, .5]
      e = np.random.random(nobs)
      y = np.dot(X, beta) + e
      results = sm.OLS(y, X).fit()
      print(results.summary())
  
  import csv
  import xlrd
  
  PROV_LIST = ['北京', '天津', '上海', '重庆市', '湖北省', '广东省']
  
  def merge():
      data = list()
      # [prov, time, gdp, pergdp, ei, il, lnpergdp, ui, pilot, post, lnlctfp]
      energy = open('control variables/能源消费总量.csv')
      f = open('control variables/外商固定资产投资.csv')
      ind = open('control variables/第二产业占比.csv')
      pergdp = open('control variables/人均GDP.csv')
      gdp = open('control variables/地区GDP.csv')
      urban = open('control variables/城镇人口比.csv')
      # gdp
      reader = list(csv.reader(gdp))
      for line in reader[1:331]: 
          if int(line[2]) <= 2016:
              data.append([
                  line[1], int(line[2]), float(line[3])
              ])
          
      # per gdp
      cnt = 0
      reader = list(csv.reader(pergdp))
      for idx, line in enumerate(reader[1:331]):
          if int(line[2]) <= 2016:
              data[cnt].append(float(line[3]))
              cnt += 1
  
      # ei
      cnt = 0
      reader = list(csv.reader(energy))
      for idx, line in enumerate(reader[1:331]):
          if int(line[1]) <= 2016:
              data[cnt].append(float(line[2]) / data[cnt][2])
              cnt += 1
  
      cnt = 0
      # fdi
      reader = list(csv.reader(f))
      for idx, line in enumerate(reader[1:301]):
          if int(line[2]) <= 2016:
              data[cnt].append( float(line[3]) / data[cnt][2])
              cnt += 1
          
      # il
      cnt = 0
      reader = list(csv.reader(ind))
      for idx, line in enumerate(reader[1:331]):
          if int(line[2]) <= 2016:
              data[cnt].append( float(line[3]) / 100)
              cnt += 1
  
      # lnpergdp
      for i in range(len(data)):
          data[i].append(np.log(data[i][3]))
      
      # ui
      cnt = 0
      reader = list(csv.reader(urban))
      for idx, line in enumerate(reader[1:331]):
          if int(line[2]) <= 2016:
              data[cnt].append( float(line[3]) / 100)
              cnt += 1
      
      # pilot, post
      for i in range(len(data)):
          prov = data[i][0]
          if prov in PROV_LIST:
              data[i].append(1)
          else:
              data[i].append(0)
          prov = data[i][-1]
          year = data[i][1]
          # if year < 2013 and year >= 2008:
          #     data[i].append(0)
          # elif year >= 2013 and year <= 2016:
          #     data[i].append(1)
          # else:
          #     print('illegal data')
              
          if year < 2009:
              data[i].append(0)
          else:
              data[i].append(1)
          data[i].append(data[i][-1] * prov)  
          
          if year < 2010:
              data[i].append(0)
          else:
              data[i].append(1)
          data[i].append(data[i][-1] * prov)  
              
          if year < 2011:
              data[i].append(0)
          else:
              data[i].append(1)
          data[i].append(data[i][-1] * prov)  
              
          if year < 2012:
              data[i].append(0)
          else:
              data[i].append(1)
          data[i].append(data[i][-1] * prov)  
              
          if year < 2013:
              data[i].append(0)
          else:
              data[i].append(1)
          data[i].append(data[i][-1] * prov)  
              
          if year < 2014:
              data[i].append(0)
          else:
              data[i].append(1)
          data[i].append(data[i][-1] * prov)  
              
          if year < 2015:
              data[i].append(0)
          else:
              data[i].append(1)
          data[i].append(data[i][-1] * prov)  
              
          if year < 2016:
              data[i].append(0)
          else:
              data[i].append(1)
          data[i].append(data[i][-1] * prov)  
  
      # lnlctfp
  
      worksheet = xlrd.open_workbook('lnlctfp.xlsx')
      sheet = worksheet.sheet_by_name('Sheet1')
      cnt = 0
      for row in range(2, 29):
          for col in range(2, 11):
              c = sheet.cell(row, col)
              data[cnt].append(np.log(c.value))
              cnt += 1
          if cnt == 20*9:
              cnt = 22*9
          if cnt == 27*9:
              cnt = 28*9
              
      # lnlci
      worksheet = xlrd.open_workbook('lnlci.xlsx')
      sheet = worksheet.sheet_by_name('Sheet1')
      cnt = 0
      for row in range(2, 29):
          for col in range(2, 11):
              c = sheet.cell(row, col)
              data[cnt].append(np.log(c.value))
              cnt += 1
          if cnt == 20*9:
              cnt = 22*9
          if cnt == 27*9:
              cnt = 28*9
  
      # eps
      worksheet = xlrd.open_workbook('eps.xlsx')
      sheet = worksheet.sheet_by_name('Sheet1')
      cnt = 0
      for row in range(1, 28):
          for col in range(2, 11):
              c = sheet.cell(row, col)
              data[cnt].append(c.value)
              cnt += 1
          if cnt == 20*9:
              cnt = 22*9
          if cnt == 27*9:
              cnt = 28*9
          
      # ind_num1
      worksheet = xlrd.open_workbook('industrial_num.xls')
      sheet = worksheet.sheet_by_name('Sheet1')
      cnt = 0
      print(len(data))
      for row in range(1, 28):
          for col in range(2, 11):
              c = sheet.cell(row, col)
              data[cnt].append(np.log(c.value))
              cnt += 1
          if cnt == 20*9:
              cnt = 22*9
          if cnt == 27*9:
              cnt = 28*9
      
      # ind_num2
      worksheet = xlrd.open_workbook('industrial_num.xls')
      sheet = worksheet.sheet_by_name('Sheet2')
      cnt = 0
      print(len(data))
      for row in range(1, 28):
          for col in range(2, 11):
              c = sheet.cell(row, col)
              data[cnt].append(np.log(c.value))
              cnt += 1
          if cnt == 20*9:
              cnt = 22*9
          if cnt == 27*9:
              cnt = 28*9
              
      # ind_num3
      worksheet = xlrd.open_workbook('industrial_num.xls')
      sheet = worksheet.sheet_by_name('Sheet3')
      cnt = 0
      print(len(data))
      for row in range(1, 28):
          for col in range(2, 11):
              c = sheet.cell(row, col)
              data[cnt].append(np.log(c.value))
              cnt += 1
          if cnt == 20*9:
              cnt = 22*9
          if cnt == 27*9:
              cnt = 28*9
              
      # ind_num4
      worksheet = xlrd.open_workbook('industrial_num.xls')
      sheet = worksheet.sheet_by_name('Sheet4')
      cnt = 0
      print(len(data))
      for row in range(1, 28):
          for col in range(2, 11):
              c = sheet.cell(row, col)
              data[cnt].append(np.log(c.value))
              cnt += 1
          if cnt == 20*9:
              cnt = 22*9
          if cnt == 27*9:
              cnt = 28*9
      
      # forest1
      worksheet = xlrd.open_workbook('forest.xls')
      sheet = worksheet.sheet_by_name('Sheet1')
      cnt = 0
      print(len(data))
      for row in range(1, 28):
          for col in range(2, 11):
              c = sheet.cell(row, col)
              data[cnt].append((c.value))
              cnt += 1
          if cnt == 20*9:
              cnt = 22*9
          if cnt == 27*9:
              cnt = 28*9
  
      # forest2
      worksheet = xlrd.open_workbook('forest.xls')
      sheet = worksheet.sheet_by_name('Sheet2')
      cnt = 0
      print(len(data))
      for row in range(1, 28):
          for col in range(2, 11):
              c = sheet.cell(row, col)
              data[cnt].append((c.value))
              cnt += 1
          if cnt == 20*9:
              cnt = 22*9
          if cnt == 27*9:
              cnt = 28*9
              
      # forest3
      worksheet = xlrd.open_workbook('forest.xls')
      sheet = worksheet.sheet_by_name('Sheet3')
      cnt = 0
      print(len(data))
      for row in range(1, 28):
          for col in range(2, 11):
              c = sheet.cell(row, col)
              data[cnt].append((c.value))
              cnt += 1
          if cnt == 20*9:
              cnt = 22*9
          if cnt == 27*9:
              cnt = 28*9
      
      outfile = open('did_data.csv', 'w')
      writer = csv.writer(outfile)
      writer.writerow(['prov', 'time', 'gdp', 'pergdp', 'ei', 'fdi', 'il', 'lnpergdp', 'ui', 
                       'pilot', 'post09', 'cross09', 'post10', 'cross10', 'post11', 'cross11', 'post12', 'cross12', 'post13', 'cross13', 
                       'post14', 'cross14', 'post15', 'cross15', 'post16', 'cross16',
                       'lnlctfp', 'lnlci', 'eps', 'ind_num1', 'ind_num2', 'ind_num3', 'ind_num4', 'forest1', 'forest2', 'forest3'])
      
      writer.writerows(data)
      
  EAST = ['北京', '天津', '河北省', '辽宁省', '上海', '江苏省', '浙江省', '福建省', '山东省', '广东省', '海南']
  MIDDLE = ['山西省', '吉林省', '黑龙江省', '安徽省', '江西省', '河南省', '湖北省', '湖南省']
  WEST = ['内蒙古自治区', '广西壮族自治区', '重庆', '四川省', '贵州省', '云南省', '西藏自治区', '陕西省', '甘肃省', '青海省', '宁夏回族自治区', '新疆维吾尔自治区']
      
  def split():
      file = open('did_input.csv')
      reader = list(csv.reader(file))
      east = list()
      mid = list()
      west = list()
      for line in reader[1:]:
          if line[0] in EAST:
              east.append(line)
          elif line[0] in MIDDLE:
              mid.append(line)
          elif line[0] in WEST:
              west.append(line)
          else:
              print(line[0])
              exit(0)
  
      fout = open('east.csv', 'w')
      writer = csv.writer(fout)
      writer.writerow(reader[0])
      writer.writerows(east)
      
      fout = open('middle.csv', 'w')
      writer = csv.writer(fout)
      writer.writerow(reader[0])
      writer.writerows(mid)
  
      fout = open('west.csv', 'w')
      writer = csv.writer(fout)
      writer.writerow(reader[0])
      writer.writerows(west)   
      
  def main():
      merge()
      split()
      df = pd.read_csv('did_input.csv')
      print(df.tail)
      # prov_dummy = pd.get_dummies(df['prov'], prefix="Prov_dum")
      # print(prov_dummy.head())
      # df = df.join(prov_dummy)
      # print(df)
  
      prov = df['prov']
      new_prov = list()
      rcd = dict()
      cnt = 0
      for item in prov:
          if item not in rcd:
              rcd[item] = cnt
              cnt += 1
          new_prov.append(rcd[item])
      new_prov = pandas.DataFrame({"id_prov": new_prov})
      df = df.join(new_prov)
      idx = list(range(243))
      id = pandas.DataFrame({"id": idx})
      df = df.join(id)
      print(df)
      
      est = smf.ols(formula='lnlctfp ~ forest1 + cross13 + ei + fdi + il + lnpergdp + ui + C(id_prov, Treatment) + C(time, Treatment)', data=df).fit()
      
      print(est.summary())
      print(est.params)
      
  if __name__ == '__main__':
      main()