通过搜索excel文件的关键词定位指标所在行的信息，获取指标的相应信息。
import pandas as pd
import xlrd
import os
import warnings
#warnings.filterwarnings("ignore")
#构建236个公司的字典
companys=[]
for k in range(236):
new_company={'excel_name':'','com_name':'','bvd_number':'','status':'','country':'','state':'','county':'','DOI':'','NLF':'','NAICS':'','NAICS_NAME':''
,'SIC':'','SIC_NAME':''}
companys.append(new_company)
def del_null(ls):
while '' in ls:
ls.remove('')
return ls
#---------------------------------企业名称，bvd_number的获取
files=os.listdir('D:/company')
for k,j in enumerate(files):
excel=r'D:/company/%s'%j
companys[k]['excel_name']=j
data=xlrd.open_workbook(excel)
sheet=data.sheets()[0]
companys[k]['com_name']=sheet.cell_value(0,1)
#不规则名称
if companys[k]['com_name']=='':
companys[k]['com_name']=sheet.cell_value(0,2)
bvds=del_null(sheet.row_values(3))
#print(bvds)
if bvds:
companys[k]['bvd_number']=bvds[2]
#不规则bvd_number
if companys[k]['bvd_number']=='':
bvds=del_null(sheet.row_values(4))
if bvds:
companys[k]['bvd_number']=bvds[2]
#-------------------------------status,country,county,state,Date of Incorporation, National Legal Form, NAICS ,SIC获取
for k,j in enumerate(files):
excel=r'D:/company/%s'%j
data=xlrd.open_workbook(excel)
sheet=data.sheets()[0]
for rowidx in range(sheet.nrows):
row = sheet.row(rowidx)
for colidx, cell in enumerate(row):
if cell.value == "Status":
#print(j,del_null(sheet.row_values(rowidx)))
if len(del_null(sheet.row_values(rowidx)))==2:
companys[k]['status']=del_null(sheet.row_values(rowidx))[1]
if cell.value == "India":
companys[k]['country']='India'
if cell.value == "England":
companys[k]['country']='England'
if cell.value == "Nepal":
companys[k]['country']='Nepal'
if cell.value == "State":
#print(j,del_null(sheet.row_values(rowidx)))
if len(del_null(sheet.row_values(rowidx)))==2:
companys[k]['state']=del_null(sheet.row_values(rowidx))[1]
if cell.value == "County":
#print(j,del_null(sheet.row_values(rowidx)))
if len(del_null(sheet.row_values(rowidx)))==2:
companys[k]['county']=del_null(sheet.row_values(rowidx))[1]
if cell.value == "Date of incorporation":
#print(j,del_null(sheet.row_values(rowidx)))
if len(del_null(sheet.row_values(rowidx)))==4:
companys[k]['DOI']=del_null(sheet.row_values(rowidx))[1]
if cell.value == "National legal form":
#print(j,del_null(sheet.row_values(rowidx)))
if len(del_null(sheet.row_values(rowidx)))==4:
companys[k]['NLF']=del_null(sheet.row_values(rowidx))[1]
if cell.value == "NAICS 2017 code(s) {derived from NIC 2008 codes}":
#print(j,del_null(sheet.row_values(rowidx+2)))
if len(del_null(sheet.row_values(rowidx+2)))==3:
companys[k]['NAICS']=del_null(sheet.row_values(rowidx+2))[0]
companys[k]['NAICS_NAME']=del_null(sheet.row_values(rowidx+2))[2]
if cell.value == "NAICS 2017 code(s) {derived from US SIC codes}":
if len(del_null(sheet.row_values(rowidx+2)))==3:
companys[k]['NAICS']=del_null(sheet.row_values(rowidx+2))[0]
companys[k]['NAICS_NAME']=del_null(sheet.row_values(rowidx+2))[2]
if cell.value == "NAICS 2017 code(s) {derived from NSIC codes}":
#print(j,del_null(sheet.row_values(rowidx+2)))
if len(del_null(sheet.row_values(rowidx+2)))==3:
companys[k]['NAICS']=del_null(sheet.row_values(rowidx+2))[0]
companys[k]['NAICS_NAME']=del_null(sheet.row_values(rowidx+2))[2]
if cell.value == "NACE Rev. 2 code(s) {derived from UK SIC (2007) codes}":
if len(del_null(sheet.row_values(rowidx+2)))==3:
companys[k]['NAICS']=del_null(sheet.row_values(rowidx+2))[0]
companys[k]['NAICS_NAME']=del_null(sheet.row_values(rowidx+2))[2]
if cell.value == "US SIC code(s) {derived from NIC 2008 codes}":
#print(j,del_null(sheet.row_values(rowidx+2)))
if len(del_null(sheet.row_values(rowidx+2)))==3:
companys[k]['SIC']=del_null(sheet.row_values(rowidx+2))[0]
companys[k]['SIC_NAME']=del_null(sheet.row_values(rowidx+2))[2]
if cell.value == "US SIC code(s)":
#print(j,del_null(sheet.row_values(rowidx+2)))
if len(del_null(sheet.row_values(rowidx+2)))==3:
companys[k]['SIC']=del_null(sheet.row_values(rowidx+2))[0]
companys[k]['SIC_NAME']=del_null(sheet.row_values(rowidx+2))[2]
if cell.value == "US SIC code(s) {derived from UK SIC (2007) codes}":
#print(j,del_null(sheet.row_values(rowidx+2)))
if len(del_null(sheet.row_values(rowidx+2)))==3:
companys[k]['SIC']=del_null(sheet.row_values(rowidx+2))[0]
companys[k]['SIC_NAME']=del_null(sheet.row_values(rowidx+2))[2]
if cell.value == "US SIC code(s) {derived from NSIC codes}":
#print(j,del_null(sheet.row_values(rowidx+2)))
if len(del_null(sheet.row_values(rowidx+2)))==3:
companys[k]['SIC']=del_null(sheet.row_values(rowidx+2))[0]
companys[k]['SIC_NAME']=del_null(sheet.row_values(rowidx+2))[2]
df=pd.DataFrame(companys)
print(df)
df.to_csv('d:/基本指标.csv')
#---------------------------------------------测试
网友评论