index 5 is out of Bounds for Axis 0 with size 1

Question

Well, I have this code, I want it to take specific columns of 3 sheets and join in one, ams is giving the following error: index 5 is out of Bounds for Axis 0 with size 1

import pandas as pd


Corp = pd.read_excel('C:/Users/t718787/Desktop/fotos/dbm.xlsx', 'Corp', usecols =['Num_CNPJ','Segmento','Cliente','Grupo','Regional','Gestor_Senior','Gerente','Lista_Distribuicao'])

Scib = pd.read_excel('C:/Users/t718787/Desktop/fotos/dbm.xlsx', 'SCIB', usecols =['Num_CNPJ','Segmento','Cliente','Grupo','Regional','Gestor_Senior','Gerente','Lista_Distribuicao'])

Gi = pd.read_excel('C:/Users/t718787/Desktop/fotos/dbm.xlsx', 'GI', usecols =['Num_CNPJ','Segmento','Cliente','Grupo','Regional','Gestor_Senior','Gerente','Lista_Distribuicao'])

Univ = pd.read_excel('C:/Users/t718787/Desktop/fotos/dbm.xlsx', 'Univ', usecols =['Num_CNPJ','Segmento','Cliente','Grupo','Regional','Gestor_Senior','Gerente','Lista_Distribuicao'])


df = pd.DataFrame()

Corp ['Num_CNPJ'] = Corp['Num_CNPJ'].astype(str)

Corp ['Num_CNPJ'] = Corp ['Num_CNPJ'].apply(lambda x: '{0:0>14}'.format(x))

Corp = df.insert(loc=0,column='Agencia_conta', value='--')
Corp = df.insert(loc=5,column='Email_cliente', value='--')
Corp = df.insert(loc=7,column='Telefone_gerente', value='--')



#config SCIB
Scib ['Num_CNPJ'] = Scib['CNPJ'].astype(str)

Scib ['Num_CNPJ'] = Scib['Num_CNPJ'].apply(lambda x: '{0:0>14}'.format(x))

Scib = df.insert(loc=0,column='Agencia_conta', value='--')
Scib = df.insert(loc=5,column='Email_cliente', value='--')
Scib = df.insert(loc=7,column='Telefone_gerente', value='--')



#config GI

Gi['Num_CNPJ'] = Gi ['Num_CNPJ'].astype(str)

Gi['Num_CNPJ'] = Gi ['Num_CNPJ'].apply(lambda x: '{0:0>14}'.format(x))

Gi = df.insert(loc=0,column='Agencia_conta', value='--')
Gi = df.insert(loc=5,column='Email_cliente', value='--')
Gi = df.insert(loc=7,column='Telefone_gerente', value='--')



#config UNIV

Univ ['Num_CNPJ'] = Univ ['Num_CNPJ'].astype(str)

Univ ['Num_CNPJ'] = Univ ['Num_CNPJ'].apply(lambda x: '{0:0>14}'.format(x))

Univ = df.insert(loc=0,column='Agencia_conta', value='--')
Univ = df.insert(loc=5,column='Email_cliente', value='--')
Univ = df.insert(loc=7,column='Telefone_gerente', value='--')


df = df.append (Corp)
df = df.append (Scib)
df = df.append (Gi)
df = df.append (Univ)

df = df.iloc [:,[0,3,4,5,6,12,13,9,14,23,2]]


df.to_excel ('C:/Users/t718787/Desktop/fotos/DBM_prt.xlsx', index = False)
print(df.count())

Answer 1

I’d build it this way:

1º I would build a list with the names of the worksheets and would read these dataframes and save in another list:

sheets = ['Corp', 'SCIB', 'GI', 'Univ']
dfs = list()

for name in sheets:
    dfs.append(pd.read_excel('Untitled 1.xlsx', name, usecols =['Num_CNPJ','Segmento','Cliente','Grupo','Regional','Gestor_Senior','Gerente','Lista_Distribuicao']))

2º Would concathens in a dataframe only:

df = pd.concat(dfs)

3º Would perform the transformations and creations of new columns:

df['Num_CNPJ'] = df['Num_CNPJ'].astype(str)
df['Num_CNPJ'] = df['Num_CNPJ'].apply(lambda x: '{0:0>14}'.format(x))

df.insert(loc=0,column='Agencia_conta', value='--')
df.insert(loc=5,column='Email_cliente', value='--')
df.insert(loc=7,column='Telefone_gerente', value='--')

Answer 2

The error is here:

Corp = df.insert(loc=5,column='Email_cliente', value='--')

You have created a new dataframe called df that doesn’t have any columns yet, so it will accept that you have created columns within the limit of it, i.e., first you create 0 after 1 after 2 and so on. When you use the loc=5 without having a 1-2-3-4 column created you exceed the limit.

The theoretically correct way would be this one:

Corp = df.insert(loc=1,column='Email_cliente', value='--')