Home > Back-end >  Mix of columns in excel to one colum with Pandas
Mix of columns in excel to one colum with Pandas

Time:04-09

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.

Excel table

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

Code tried

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
  • Related