Pandas: analizar columnas de encabezado combinadas desde Excel

Los datos en hojas de Excel se almacenan de la siguiente manera:

Area | Product1 | Product2 | Product3 | sales|sales.Value| sales |sales.Value | sales |sales.Value Location1 | 20 | 20000 | 25 | 10000 | 200 | 100 Location2 | 30 | 30000 | 3 | 12300 | 213 | 10 

el nombre del producto es una combinación de 2 celdas de dos filas “no de ventas” y “valor de ventas” para cada una de las 1000 o más áreas para un mes determinado. Del mismo modo, hay archivos separados para cada mes durante los últimos 5 años. Además, los nuevos productos se han agregado y eliminado en diferentes meses. Así que un archivo de mes diferente podría verse como:

  Area | Product1 | Product4 | Product3 

¿Puede el foro sugerir la mejor manera de leer estos datos usando pandas? No puedo usar el índice ya que las columnas del producto son diferentes cada mes

Idealmente, me gustaría convertir el formato inicial anterior a:

  Area | Product1.sales|Product1.sales.Value| Product2.sales |Product2.sales.Value | Location1 | 20 | 20000 | 25 | 10000 | Location2 | 30 | 30000 | 3 | 12300 | 

 import pandas as pd xl_file = read_excel("file path", skiprow=2, sheetname=0) /* since the first two rows are always blank */ 0 1 2 3 4 0 NaN NaN NaN Auto loan NaN 1 Branch Code Branch Name Region No of accounts Portfolio Outstanding 2 3000 Name1 Central 0 0 3 3001 Name2 Central 0 0 

Quiero convertirlo en Auto loan.No of account , Auto loan.Portfolio Outstanding as the headers.

Supongamos que su DataFrame es df :

 import numpy as np import pandas as pd nan = np.nan df = pd.DataFrame([ (nan, nan, nan, 'Auto loan', nan) , ('Branch Code', 'Branch Name', 'Region', 'No of accounts' , 'Portfolio Outstanding') , (3000, 'Name1', 'Central', 0, 0) , (3001, 'Name2', 'Central', 0, 0) ]) 

para que se vea así:

  0 1 2 3 4 0 NaN NaN NaN Auto loan NaN 1 Branch Code Branch Name Region No of accounts Portfolio Outstanding 2 3000 Name1 Central 0 0 3 3001 Name2 Central 0 0 

Luego, primero envíe los NaN en las primeras dos filas (propagando así ‘Préstamo de automóvil’, por ejemplo).

 df.iloc[0:2] = df.iloc[0:2].fillna(method='ffill', axis=1) 

A continuación, rellene los NaN restantes con cadenas vacías:

 df.iloc[0:2] = df.iloc[0:2].fillna('') 

Ahora une las dos filas juntas con . y asigna eso como valores de nivel de columna:

 df.columns = df.iloc[0:2].apply(lambda x: '.'.join([y for y in x if y]), axis=0) 

Y, finalmente, eliminar las dos primeras filas:

 df = df.iloc[2:] 

Esto produce

  Branch Code Branch Name Region Auto loan.No of accounts \ 2 3000 Name1 Central 0 3 3001 Name2 Central 0 Auto loan.Portfolio Outstanding 2 0 3 0 

Alternativamente, puede crear una columna MultiIndex en lugar de crear un índice de columna plana:

 import numpy as np import pandas as pd nan = np.nan df = pd.DataFrame([ (nan, nan, nan, 'Auto loan', nan) , ('Branch Code', 'Branch Name', 'Region', 'No of accounts' , 'Portfolio Outstanding') , (3000, 'Name1', 'Central', 0, 0) , (3001, 'Name2', 'Central', 0, 0) ]) df.iloc[0:2] = df.iloc[0:2].fillna(method='ffill', axis=1) df.iloc[0:2] = df.iloc[0:2].fillna('Area') df.columns = pd.MultiIndex.from_tuples( zip(*df.iloc[0:2].to_records(index=False).tolist())) df = df.iloc[2:] 

Ahora df ve así:

  Area Auto loan Branch Code Branch Name Region No of accounts Portfolio Outstanding 2 3000 Name1 Central 0 0 3 3001 Name2 Central 0 0 

La columna es un MultiIndex:

 In [275]: df.columns Out[275]: MultiIndex(levels=[[u'Area', u'Auto loan'], [u'Branch Code', u'Branch Name', u'No of accounts', u'Portfolio Outstanding', u'Region']], labels=[[0, 0, 0, 1, 1], [0, 1, 4, 2, 3]]) 

La columna tiene dos niveles. El primer nivel tiene valores [u'Area', u'Auto loan'] , el segundo tiene valores [u'Branch Code', u'Branch Name', u'No of accounts', u'Portfolio Outstanding', u'Region'] .

Luego puede acceder a una columna especificando el valor de ambos niveles:

 print(df.loc[:, ('Area', 'Branch Name')]) # 2 Name1 # 3 Name2 # Name: (Area, Branch Name), dtype: object print(df.loc[:, ('Auto loan', 'No of accounts')]) # 2 0 # 3 0 # Name: (Auto loan, No of accounts), dtype: object 

Una de las ventajas de usar un MultiIndex es que puede seleccionar fácilmente todas las columnas que tienen un cierto valor de nivel. Por ejemplo, para seleccionar el sub-DataFrame que tiene que ver con los Auto loans , podría usar:

 In [279]: df.loc[:, 'Auto loan'] Out[279]: No of accounts Portfolio Outstanding 2 0 0 3 0 0 

Para obtener más información sobre la selección de filas y columnas de un MultiIndex, consulte MultiIndexing mediante el uso de segmentaciones .