Step 1 : 导入模块
import pandas as pd import os
Step 2 : 查看工作路径
该工作路径应为想读取文件的文件夹,若python的工作路径不是该文件夹,则应用os.chdir来进行切换
os.getcwd() '/Users/chenyuwang/Desktop/IAQF/data/dataList'
Step 3 : 获取文件夹中文件名为.xlsx的文件名,并存在一个list中
path = os.getcwd() ##os.chdir(path) 若想改变工作路径可以用chdir函数 filelist = [] for root,
dirs,files in os.walk(path): for file in files: if os.path.splitext(file)[1] ==
'.xlsx': filelist.append(file) filelist ['201708.xlsx', '200908.xlsx',
'201509.xlsx', '201704.xlsx', '201211.xlsx', '201303.xlsx', '200904.xlsx',
'200912.xlsx', '201712.xlsx', '201207.xlsx', '201010.xlsx', '201505.xlsx',
'201102.xlsx', '201801.xlsx', '201401.xlsx', '201006.xlsx', '201007.xlsx',
'201512.xlsx', '201103.xlsx', '201011.xlsx', '201504.xlsx', '201206.xlsx',
'201601.xlsx', '201302.xlsx', '200905.xlsx', '201705.xlsx', '201210.xlsx',
'201508.xlsx', '200909.xlsx', '201709.xlsx', '201702.xlsx', '201305.xlsx',
'200902.xlsx', '201610.xlsx', '201606.xlsx', '201201.xlsx', '201503.xlsx',
'201411.xlsx', '201807.xlsx', '201104.xlsx', '201407.xlsx', '201112.xlsx',
'201811.xlsx', '201309.xlsx', '201108.xlsx', '201109.xlsx', '201308.xlsx',
'201001.xlsx', '201902.xlsx', '201406.xlsx', '201810.xlsx', '201410.xlsx',
'201806.xlsx', '201105.xlsx', '201502.xlsx', '201312.xlsx', '201607.xlsx',
'201304.xlsx', '200903.xlsx', '201611.xlsx', '201703.xlsx', '201901.xlsx',
'201002.xlsx', '201110.xlsx', '201405.xlsx', '201106.xlsx', '201805.xlsx',
'201501.xlsx', '201203.xlsx', '201604.xlsx', '201311.xlsx', '201612.xlsx',
'201307.xlsx', '201409.xlsx', '201809.xlsx', '201608.xlsx', '201609.xlsx',
'201808.xlsx', '201408.xlsx', '201701.xlsx', '200901.xlsx', '201306.xlsx',
'201605.xlsx', '201310.xlsx', '201202.xlsx', '201107.xlsx', '201804.xlsx',
'201412.xlsx', '201812.xlsx', '201111.xlsx', '201404.xlsx', '201003.xlsx',
'201008.xlsx', '201209.xlsx', '201511.xlsx', '201004.xlsx', '201403.xlsx',
'201803.xlsx', '201507.xlsx', '201012.xlsx', '201205.xlsx', '201710.xlsx',
'200910.xlsx', '201602.xlsx', '200906.xlsx', '201301.xlsx', '201706.xlsx',
'201212.xlsx', '201707.xlsx', '200907.xlsx', '200911.xlsx', '201603.xlsx',
'201204.xlsx', '201711.xlsx', '201506.xlsx', '201802.xlsx', '201101.xlsx',
'201402.xlsx', '201510.xlsx', '201005.xlsx', '201208.xlsx', '201009.xlsx']
Step 4 : 读取Excel文件内容,并把每个表格都存成DataFrame结构
dflist = [] for i in range(len(filelist)) : dflist.append(pd.read_excel(
filelist[i], skiprows =1 ))
Step 5 : 将表格合并
data = pd.concat(dflist)
Step 6 : 按照某一列将表格中相同项删除并保留第一次见到时的数值
data.drop_duplicates('ISIN number','first', inplace = True) data
CusipISIN numberDescriptionTickerPar Wtd CouponMaturity DateRatingISO Currency
CodeISO Country CodeIndustry Lvl 1 Desc...Yield to Maturity (s.a.)Total Return
% MTD_LOCExcess Return % MTDEffective DurationMod. Dur to Worst (s.a.)Modified
Duration (s.a.)Effective ConvexityConvexity to Worst (s.a.)Convexity (s.a.)
Subordination Type
034531XAAUS34531XAA28Ford FoundationFRDFND3.8592047-06-01AAAUSDUSCorporate...
3.601.3252.01218.1217.5517.714.494.244.34Senior
1740816AMUS740816AM58President & Fellows of Harvard CollegeHARVRD3.150
2046-07-15AAAUSDUSCorporate...3.43-0.5060.24619.0718.5618.564.764.564.56Senior
2740816ANUS740816AN32President & Fellows of Harvard CollegeHARVRD3.300
2056-07-15AAAUSDUSCorporate...3.53-0.1840.70521.7521.3521.356.746.536.53Senior
3740816ALUS740816AL75President & Fellows of Harvard CollegeHARVRD3.619
2037-10-01AAAUSDUSCorporate...3.38-0.2800.08914.5214.1914.192.662.552.55Senior
4740816AHUS740816AH63President & Fellows of Harvard CollegeHARVRD4.875
2040-10-15AAAUSDUSCorporate...3.43-0.612-0.21015.0014.6714.673.002.882.88Senior
5740816AGUS740816AG80President & Fellows of Harvard CollegeHARVRD6.500
2039-01-15AAAUSDUSCorporate...3.50-0.504-0.24513.5113.2613.262.442.372.37Senior
644266RACUS44266RAC16Howard Hughes Medical InstituteHUGHES3.5002023-09-01AAAUSD
USCorporate...2.480.368-0.0645.455.415.410.340.340.34Senior
7478160BRUS478160BR44Johnson & JohnsonJNJ1.1252019-03-01AAAUSDUSCorporate...
1.470.077-0.1091.551.561.560.030.030.03Senior
8478160BGUS478160BG88Johnson & JohnsonJNJ1.6502018-12-05AAAUSDUSCorporate...
1.430.1830.0191.321.321.320.020.020.02Senior
9478160BSUS478160BS27Johnson & JohnsonJNJ1.6502021-03-01AAAUSDUSCorporate...
1.830.5120.1753.443.443.440.130.140.14Senior
10478160BMUS478160BM56Johnson & JohnsonJNJ1.8752019-12-05AAAUSDUSCorporate...
1.580.046-0.2042.282.282.280.060.060.06Senior
11478160BTUS478160BT00Johnson & JohnsonJNJ2.0502023-03-01AAAUSDUSCorporate...
2.140.6980.2555.215.205.200.290.310.31Senior
12478160CDUS478160CD49Johnson & JohnsonJNJ2.2502022-03-03AAAUSDUSCorporate...
2.010.7010.2934.314.234.300.200.210.21Senior
13478160BNUS478160BN30Johnson & JohnsonJNJ2.4502021-12-05AAAUSDUSCorporate...
1.940.5990.2084.124.104.100.190.190.19Senior
14478160BYUS478160BY94Johnson & JohnsonJNJ2.4502026-03-01AAAUSDUSCorporate...
2.661.0680.6897.667.617.610.640.660.66Senior
15478160AWUS478160AW48Johnson & JohnsonJNJ2.9502020-09-01AAAUSDUSCorporate...
1.700.4850.1892.922.912.910.100.100.10Senior
16478160CEUS478160CE22Johnson & JohnsonJNJ2.9502027-03-03AAAUSDUSCorporate...
2.730.8830.5578.288.048.230.770.750.78Senior
17478160BHUS478160BH61Johnson & JohnsonJNJ3.3752023-12-05AAAUSDUSCorporate...
2.101.0990.6645.755.705.700.380.370.37Senior
18478160AZUS478160AZ78Johnson & JohnsonJNJ3.5502021-05-15AAAUSDUSCorporate...
1.880.4010.0573.543.533.530.150.150.15Senior
19478160BUUS478160BU72Johnson & JohnsonJNJ3.5502036-03-01AAAUSDUSCorporate...
3.350.0270.25513.6613.1613.422.302.162.26Senior
20478160CFUS478160CF96Johnson & JohnsonJNJ3.6252037-03-03AAAUSDUSCorporate...
3.36-0.387-0.07014.1413.6313.882.492.342.44Senior
21478160BVUS478160BV55Johnson & JohnsonJNJ3.7002046-03-01AAAUSDUSCorporate...
3.550.3320.99917.8917.2817.464.324.064.16Senior
22478160CGUS478160CG79Johnson & JohnsonJNJ3.7502047-03-03AAAUSDUSCorporate...
3.540.4251.12218.1817.5917.764.504.254.34Senior
23478160BJUS478160BJ28Johnson & JohnsonJNJ4.3752033-12-05AAAUSDUSCorporate...
3.290.2560.26912.0211.6411.901.771.681.76Senior
24478160AVUS478160AV64Johnson & JohnsonJNJ4.5002040-09-01AAAUSDUSCorporate...
3.580.5500.95515.0214.6814.683.012.882.88Senior
25478160BKUS478160BK90Johnson & JohnsonJNJ4.5002043-12-05AAAUSDUSCorporate...
3.590.0980.62116.4415.9016.083.633.423.51Senior
26478160BAUS478160BA19Johnson & JohnsonJNJ4.8502041-05-15AAAUSDUSCorporate...
3.64-0.863-0.44815.1414.8114.813.072.952.95Senior
27478160ALUS478160AL82Johnson & JohnsonJNJ4.9502033-05-15AAAUSDUSCorporate...
3.30-0.415-0.44411.4911.3111.311.651.611.61Senior
28478160ATUS478160AT19Johnson & JohnsonJNJ5.8502038-07-15AAAUSDUSCorporate...
3.570.8771.13713.5413.2913.292.432.352.35Senior
29478160ANUS478160AN49Johnson & JohnsonJNJ5.9502037-08-15AAAUSDUSCorporate...
3.461.2191.45012.9212.6912.692.232.162.16Senior
..................................................................
4436962GX8US36962GX826General Electric CompanyGE5.7202011-08-22AAAUSDUS
Corporate...6.170.5691.0982.232.342.34-0.250.070.07Senior
4536962GXSUS36962GXS82General Electric CompanyGE5.8752012-02-15AAAUSDUS
Corporate...5.30-0.6940.0282.692.682.680.090.090.09Senior
4636962G3PUS36962G3P70General Electric CompanyGE5.8752038-01-14AAAUSDUS
Corporate...7.64-17.758-7.30912.0112.0912.092.372.382.38Senior
4736962GYYUS36962GYY42General Electric CompanyGE6.0002012-06-15AAAUSDUS
Corporate...5.42-0.3250.5443.013.003.000.110.110.11Senior
4836962GF7US36962GF740General Electric CompanyGE6.0002019-02-13AAAUSDUS
Corporate...6.93-3.269-1.1915.845.587.31-0.650.380.67Senior
4936962GWBUS36962GWB66General Electric CompanyGE6.1252011-02-22AAAUSDUS
Corporate...4.390.3710.7561.881.881.880.050.050.05Senior
5036962G3AUS36962G3A02General Electric CompanyGE6.1502037-08-07AAAUSDUS
Corporate...7.71-17.175-7.00211.4311.5111.512.222.232.23Senior
5136962GXZUS36962GXZ26General Electric CompanyGE6.7502032-03-15AAAUSDUS
Corporate...7.72-14.915-6.25010.7010.6910.691.821.801.80Senior
5236962GVSUS36962GVS01General Electric CompanyGE6.8752010-11-15AAAUSDUS
Corporate...4.71-0.786-0.4811.651.651.650.040.040.04Senior
5336962G4BUS36962G4B75General Electric CompanyGE6.8752039-01-10AAAUSDUS
Corporate...7.87NaNNaN11.5511.6411.642.242.262.26Senior
5436962GLFUS36962GLF99General Electric CompanyGE6.9002015-09-15AAAUSDUS
Corporate...7.45-4.495-2.1625.185.085.080.340.320.32Senior
55898404ABUS898404AB48Trustreet Properties. Inc.GE7.5002015-04-01AAAUSDUS
Corporate...6.45-2.351-1.7163.311.084.81-0.830.020.29Senior
5636959CAAUS36959CAA62General Electric Capital Services Inc.GE7.5002035-08-21
AAAUSDUSCorporate...7.71-17.894-8.49410.8610.9110.911.971.971.97Subordinated
57369622DNUS369622DN23General Electric CompanyGE8.1252012-05-15AAAUSDUS
Corporate...5.691.4862.2832.852.842.840.100.100.10Subordinated
10478160BDUS478160BD57Johnson & JohnsonJNJ0.7002013-05-15AAAUSDUSCorporate...
0.250.0260.0051.361.371.370.030.030.03Senior
9364952WCXUS64952WCX92New York Life Global FundingNYLIFE3.0002028-01-10AAAUSDUS
Corporate...3.78-0.607-0.6158.068.038.030.760.750.75Secured
99914748AAUS914748AA64University of Notre DameUNIVND3.3942048-02-15AAAUSDUS
Corporate...3.720.2500.03018.1117.9717.974.454.434.43Senior
5621685WCJUS21685WCJ45Cooperatieve Rabobank U.A.RABOBK5.2502041-05-24AAAUSDNL
Corporate...5.24-13.990-16.67515.2014.9114.913.453.313.31Senior
9264952WDAUS64952WDA80New York Life Global FundingNYLIFE3.2502021-08-06AAAUSDUS
Corporate...3.360.079-0.0562.612.602.600.080.080.08Senior
9364952WDCUS64952WDC47New York Life Global FundingNYLIFE3.9002028-10-16AAAUSDUS
Corporate...3.91NaNNaN8.208.178.170.790.790.79Secured
0CASHUSD0CASHUSD00000CASHCASH1.7852018-05-31AAAUSDCASHCASH...1.780.0000.0000.00
0.000.000.000.000.00CASH
8230231GAUUS30231GAU67Exxon Mobil CorporationXOM1.4392018-03-01AAAUSDUS
Corporate...1.06NaNNaN1.881.881.880.040.040.04Senior
8330231GAPUS30231GAP72Exxon Mobil CorporationXOM1.7082019-03-01AAAUSDUS
Corporate...1.25NaNNaN2.842.842.840.100.100.10Senior
8630231GAVUS30231GAV41Exxon Mobil CorporationXOM2.2222021-03-01AAAUSDUS
Corporate...1.84NaNNaN4.664.564.640.260.240.25Senior
8930231GARUS30231GAR39Exxon Mobil CorporationXOM2.7262023-03-01AAAUSDUS
Corporate...2.46NaNNaN6.316.136.270.440.430.45Senior
9030231GATUS30231GAT94Exxon Mobil CorporationXOM3.0432026-03-01AAAUSDUS
Corporate...2.77NaNNaN8.628.338.510.830.800.83Senior
9330231GAWUS30231GAW24Exxon Mobil CorporationXOM4.1142046-03-01AAAUSDUS
Corporate...3.78NaNNaN18.0417.3117.464.474.144.23Senior
0084664ARUS084664AR26Berkshire Hathaway Finance CorporationBRK4.1252010-01-15
AAAUSDUSCorporate...3.111.7831.4230.990.990.990.010.010.01Senior
5436962GULUS36962GUL66General Electric CompanyGE7.3752010-01-19AAAUSDUS
Corporate...4.611.2430.8890.980.980.980.010.010.01Senior
39EI457342XS0557640348Cooperatieve Rabobank U.A.RABOBK3.0002012-09-18AAAUSDNL
Corporate...1.02-0.1490.0991.761.761.760.040.040.04Senior
272 rows × 35 columns

Step 7 :将整理好的数据导出至Excel
data.to_excel('excel_output.xls')

技术
©2019-2020 Toolsou All rights reserved,
员工网上晒腾讯年终奖:每人100股公司股票 价值超6万元!C++标准库关于过年王者荣耀背景故事整合中台透彻讲解程序员七夕情人节表白代码java实现抢红包功能单个按键控制多种流水灯状态如何快速的用html和css写静态页面胡润:中国600万资产“富裕家庭”数量首次突破500万户