我有以下文本,我想刮取数据项并将它们保存在excel中.有没有办法在Python中执行此操作？
text = """
ANNUAL COMPENSATION LONG-TERM COMPENSATION
--------------------------------------- -------------------------------------
AWARDS PAYOUTS
-------------------------- ----------
SECURITIES
OTHER RESTRICTED UNDERLYING ALL OTHER
NAME AND PRINCIPAL ANNUAL STOCK OPTIONS/ LTIP COPMPENSA-
POSITION YEAR SALARY ($) BONUS ($) COMPENSATION ($) AWARD(S) ($)(1) SAR'S (#) PAYOUTS($) TION($)(3)
------------------ ---- ---------- --------- ---------------- --------------- ---------- ---------- ----------
JOHN W. WOODS 1993 $595,000 $327,250 There is no $203,190.63 18,000 $29,295
Chairman, President, & 1992 $545,000 $245,250 compensation 166,287.50 18,825 (2) Not $29,123
Chief Executive Officer 1991 $515,000 $283,251 required to be 45,000 Applicable
of AmSouth & AmSouth disclosed in
Bank N.A. this column.
C. STANLEY BAILEY 1993 $266,667(4) $133,333 117,012.50 4,500 $11,648
Vice Chairman, AmSouth 1992 $210,000 $84,000 42,400.00 4,800 $12,400
& AmSouth Bank N.A. 1991 $186,750 $82,170 161,280.00 9,750
C. DOWD RITTER 1993 $266,667(4) $133,333 117,012.50 4,500 $13,566
Vice Chairman, AmSouth 1992 $210,000 $84,000 42,400.00 4,800 $12,920
& AmSouth Bank N.A. 1991 $188,625 $82,995 161,280.00 9,750
WILLIAM A. POWELL, JR. 1993 $211,335 $95,101 11,000 $124,548
President, AmSouth 1992 $330,000 $132,000 98,050.00 11,100 $22,225
and Vice Chairman, 1991 $308,000 $169,401 24,000
AmSouth Bank N.A.
Retired in 1993
A. FOX DEFUNIAK, III 1993 $217,000 $75,950 52,971.88 4,500 $11,122
Senior Executive Vice 1992 $200,000 $62,000 42,400.00 4,800 $11,240
President, Birmingham 1991 $177,500 $78,100 161,280.00 9,750
Banking Group,
AmSouth Bank N.A.
E. W. STEPHENSON, JR. 1993 $177,833 $71,133 52,971.88 3,400 $ 9,256
Senior Executive Vice 1992 $150,000 $45,000 27,825.00 3,150 $ 8,560
President, AmSouth 1991 $140,000 $52,488 107,520.00 6,750
and Chairman & Chief
Executive Officer,
AmSouth Bank of Florida
"""
现在,我只是想用一个带有’|’的csv样式格式来获取它用于分隔数据项的符号,然后手动将数据提取到excel：
tmp = open('tmp.txt','w')
tmp.write(text)
tmp.close()
data1 = []
for line in open('tmp.txt'):
line = line.lower()
if 'SALARY' in line:
line = line.replace(' ','|')
line = line.replace('--', '')
line = line.replace('- -', '')
line = line.replace('- -', '')
line = line.replace('(1)', '')
line = line.replace('(2)', '')
line = line.replace('(3)', '')
line = line.replace('(4)', '')
line = line.replace('(5)', '')
line = line.replace('(6)', '')
line = line.replace('(7)', '')
line = line.replace('(8)', '')
line = line.replace('(9)', '')
line = line.replace('(10)', '')
line = line.replace('(11)', '')
line = line.replace('(S)', '')
line = line.replace('($)', '')
line = line.replace('(#)', '')
line = line.replace('$', '')
line = line.replace('-0-', '0')
line = line.replace(')', '|')
line = line.replace('(', '|-')
line = re.sub(r'\s(\d)', '|\\1', line)
line = line.replace(' ', '')
line = line.replace('||', '|')
data1.append(line)
data = ''.join(data1)
问题是我必须做成千上万次,并且需要永远地通过每个表并保存我需要的项目.有没有办法创建一个字典,可以跟踪最左边列中列出的每个人的年份,工资,奖金,其他年度报酬等内容？
解决方法:
以下是一些可以帮助您入门的代码：
text = """JOHN ...""" # text without the header
# These can be inferred if necessary
cols = [0, 24, 29, 39, 43, 52, 71, 84, 95, 109, 117]
db = []
row = []
for line in text.strip().split("\n"):
data = [line[cols[i]:cols[i+1]] for i in xrange((len(cols)-1))]
if data[0][0] != " ":
if row:
db.append(row)
row = map(lambda x: [x], data)
else:
for i, c in enumerate(data):
row[i].append(c)
print db
这将生成一个包含每人元素的数组.每个元素都是所有列的数组,并且将包含所有行的数组.通过这种方式,您可以轻松访问不同年份,或者执行诸如连接人员标题之类的操作：
for person in db:
print "Name:", person[0][0]
print " ".join(s.strip() for s in person[0][1:])
print
将产量：
Name: JOHN W. WOODS
Chairman, President, & Chief Executive Officer of AmSouth & AmSouth Bank N.A.
Name: C. STANLEY ...