python - KeyError when reading from Excel data into dataframe -

-

i have excel file 2 sheets , trying read both of them dataframe in code below. however, error 

keyerror: "['months_to_maturity' 'asset_id' 'orig_iss_dt' 'maturity_dt' 'pay_freq_cd'\n 'coupon' 'closing_price'] not in index"

in line 

return df[['months_to_maturity', 'asset_id', 'orig_iss_dt', 'maturity_dt' , 'pay_freq_cd', 'coupon', 'closing_price']]

in secondexcelfilereader() function. however, both sheets have headers 

asset_id    orig_iss_dt maturity_dt  pay_freq_cd    coupon  closing_price   months_to_maturity

i return df follows order in want columns.

def excelfilereader():     xls = pd.excelfile('d:/usdatarecently.xls')     df = xls.parse(xls.sheet_names[0])     return df[['months_to_maturity', 'asset_id', 'orig_iss_dt', 'maturity_dt' , 'pay_freq_cd', 'coupon', 'closing_price']]   def secondexcelfilereader():     xls = pd.excelfile('d:/usdatarecently.xls')     df = xls.parse(xls.sheet_names[1])     return df[['months_to_maturity', 'asset_id', 'orig_iss_dt', 'maturity_dt' , 'pay_freq_cd', 'coupon', 'closing_price']]  def mergingdataframes():     df1 = excelfilereader()     df2 = secondexcelfilereader()     return pd.concat([df1, df2])

edit: excel file exported sybase oracle sql developer , hence first sheet came titles. copied , pasted second sheet same titles. also, having issue second sheet.

sheet 1: sheet 1

sheet 2: sheet 2

def excelfilereader():     xls = pd.excelfile('d:/usdatarecently.xls')     sheet_num = xls.sheet_names.index(xls.sheet_names[0])     df = pd.read_excel('d:/usdatarecently.xls',sheetname=sheet_num)     return df[['months_to_maturity', 'asset_id', 'orig_iss_dt', 'maturity_dt' ,'pay_freq_cd', 'coupon', 'closing_price']]

alternatively in case instead of sheetname = xls.sheet_names[0] use sheetname=0

looks issue second sheetname "sheet1" , based on excelparser documentation "sheet1" means first sheet, in case it's second sheet. http://pandas.pydata.org/pandas-docs/stable/generated/pandas.excelfile.parse.html

a better implementation be:

def mergingdataframes():     mergedf= pd.concat(pd.read_excel('d:/usdatarecently.xls', sheetname=[0,1]))     mergedf.index = mergedf.index.droplevel(0)# need drop dict keys     return mergedf

Comments

Post a Comment

Popular posts from this blog

c# - Store DBContext Log in other EF table -

-
i want store logs in db table (imported ef code). my code looks this: public pmcontext() : base("myentities") { configuration.proxycreationenabled = false; configuration.lazyloadingenabled = false; this.database.log = s => logstore(s); } private void logstore(string message) { tlog log = new tlog(); log.description = message; log.insertdate = datetime.now; logs.add(log); savechanges(); } public virtual dbset<tlog> logs { get; set; } i cannot use savechanges() (get error "sqlconnection not support parallel transactions") , if remove line nothing stored in db table. how can fix this? i think happens during savechanges call anywhere in app, because sql generated call logged, triggers nested savechanges call tries start own transaction. and there thing: savechanges call in logstore method trigger logging itself, you'd end starting infin...
Read more

javascript - Karma not able to start PhantomJS on Windows - Error: spawn UNKNOWN -

-
i lost "unknown" error: karma.conf.js: frameworks: ['mocha', 'should'], plugins: ['karma-mocha', 'karma-should', 'karma-phantomjs-launcher'], ... browsers: ['phantomjs'], error: 23 07 2015 14:35:37.691:warn [karma]: no captured browser, open http://localhost:9876/ 23 07 2015 14:35:37.701:info [karma]: karma v0.13.3 server started @ http://localhost:9876/ 23 07 2015 14:35:37.707:info [launcher]: starting browser phantomjs 23 07 2015 14:35:37.738:error [karma]: { [error: spawn unknown] code: 'unknown', errno: 'unknown', syscall: 'spawn' } error: spawn unknown @ exports._errnoexception (util.js:749:11) @ childprocess.spawn (child_process.js:1093:11) @ exports.spawn (child_process.js:933:9) @ object._execcommand (c:\users\jinga4x\git\mdl-sample-project\node_modules\karma\lib\launchers\process.js:63:21) @ object._start (c:\users\jinga4x\git\md...
Read more

Nuget pack csproj using nuspec -

-
i want create nuget package contains specified in nuspec file, still version csproj. in order use token have pack like: nuget pack myproj.csproj but when adds dependencies , creates unwanted folder in nuget package. nuspec file is: <?xml version="1.0" encoding="utf-8"?> <package xmlns="http://schemas.microsoft.com/packaging/2011/08/nuspec.xsd"> <metadata> <id>test</id> <version>$version$</version> <title>test</title> <authors>test</authors> <owners>test</owners> <requirelicenseacceptance>false</requirelicenseacceptance> <description>test</description> <summary>test</summary> <releasenotes>test</releasenotes> <copyright>test</copyright> </metadata> <files> <file src="bin\debug\*.dll" target="lib\net45" /> <file s...
Read more