I have to import this Excel in code and I would like to unify the multi-index in a single column. I would like to delete the unnamed columns and unify everything into one. I don't know if it's possible.
I have tried the following and it imports, but the output is not as expected. I add the code here too
import pandas as pd
import numpy as np
macro = pd.read_excel(nameExcel, sheet_name=nameSheet, skiprows=3, header=[1,3,4])
macro = macro[macro.columns[1:]]
macro
CodePudding user response:
The best option i have found is to save manually the name of the columns in a array, then equal the name of the columns of the previuos array with the columns created manually(I have created a dictionary to make it more elegant):
DICT_TP_PD_NAMES_CON = {
'Clave de Agregación': {TP_DATIO_NAME: 'aggregate_pd_id',
ORDER_ID: 2,
FLAG_ORDER: True,
COLUMN_INDEX: 1},
'Número de Cuotas Impagadas Desde': {TP_DATIO_NAME: 'outstanding_payments_from_number',
ORDER_ID: 3,
FLAG_ORDER: True,
COLUMN_INDEX: 2},
...
And then take the name of the subtract the name of the columns:
import pandas as pd
import numpy as np
columns = []
for var, var_dict in DICT_TP_MODEL_PD.items():
columns.append(var_dict.get('TP_DATIO_NAME'))
macro = pd.read_excel(nameExcel, sheet_name=nameSheet, skiprows=4,header=[0,1,2,3])
macro = macro[macro.columns[1:]]
if len(macro.columns) == len(columns):
macro.columns = columns
else:
print("error")
macro