converting from reading csv to excel - pandas/python

Question

I am new to this, so be easy on me!

I have some code that works fine with CSV files, but I want to convert it to read excel files so I can have multiple tabs per file.

Here is the working original

import numpy as np
import pandas as pd
import csv 
pd.options.mode.chained_assignment = None

#the whole portfolio
num_of_contracts = 1418
num_of_simul = 10000

contract_info_df = pd.read_csv('contract_info..csv', encoding='latin-1')
contract_info_df = contract_info_df.set_index(['Contract Identifier'])
toy_contract = contract_info_df.iloc[:num_of_contracts, :]
toy_contracts_list = contract_info_df.iloc[:num_of_contracts, :].index.tolist()
toy_contracts_list = (list(map(str, toy_contracts_list)))
toy_contract.index = toy_contracts_list



loss_df = pd.read_csv('losstable.csv')
loss_table = loss_df.loc[:,toy_contracts_list][:num_of_simul]
loss_table['row_sum'] = loss_table.sum(axis = 1)

Here is my attempt

import numpy as np
import pandas as pd
pd.options.mode.chained_assignment = None

#the whole portfolio
num_of_contracts = 162
num_of_simul = 10000

xls = pd.ExcelFile('contract_info.xlsx')
df1 = pd.read_excel(xls, 'contract_info') #worksheetname

contract_info_df = df1.set_index(['Contract Identifier'])
toy_contract = contract_info_df.iloc[:num_of_contracts, :]
toy_contracts_list = contract_info_df.iloc[:num_of_contracts, :].index.tolist()
toy_contracts_list = (list(map(str, toy_contracts_list)))
toy_contract.index = toy_contracts_list


xls = pd.ExcelFile('losstable.xlsx') 
loss_df = pd.read_excel(xls, 'losstable') #woorksheet name
loss_table = loss_df.loc[:,toy_contracts_list][:num_of_simul]

The error comes on this last line, it can't find the 'toy_contracts_list' values in the [columns]

What is my problem?

Thanks

I started with an Excel file, 'losstable.xlsx', that contained rows of integers (including the columns' row). I saved it as csv and xlsx. This, and your code, produced the KeyError you described. — Eme Eme
– Eme Eme, Commented Jun 27, 2018 at 0:00

Eme Eme · Accepted Answer · 2018-06-26 23:59:06Z

1

It appears the pd.read_csv('losstable.csv') loads the csv with 'str' for columns. While df1 = pd.read_excel(xls, 'losstable') tries to infer the dtype of the columns. In this case, it leaves the dtype of the columns as int.

Remove this line from your code:

toy_contracts_list = (list(map(str, toy_contracts_list)))

answered Jun 26, 2018 at 23:59

Eme Eme

9108 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

converting from reading csv to excel - pandas/python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related