python - Flattening the hierarchy structure in the pandas dataframe

Question

I have an excel file with 3 hierarchical levels of data captured in 2 columns.

  A          B          C
  X          nan       nan
  Y          value     nan
  nan        Z         SOMETHING 

   

In the above example, hierarchy is managed within the columns A & B. X is the first level of hierarchy, Y is the second and Z is the third. I need to create a dataframe with these three levels in a single row(flattening) and with the value of column 3 as shown below

A   B   C  D
X   Y   Z  SOMTHING

question from:https://stackoverflow.com/questions/65836808/flattening-the-hierarchy-structure-in-the-pandas-dataframe

Answer 1

For the sample you have provided this would give the desired output. Basically transposing, backfilling empty values, then slicing and concatenating to get the final result.

import pandas as pd
import numpy as np
df = pd.DataFrame(
{'A': {0: 'X', 1: 'Y', 2: np.nan},
 'B': {0: np.nan, 1: 'value', 2: 'Z'},
 'C': {0: np.nan, 1: np.nan, 2: 'SOMETHING'}})

df = df.T.bfill()

df = pd.concat([df.iloc[:1,:3].reset_index(drop=True),
           df.iloc[-1:,-1:].reset_index(drop=True)],
          axis=1)
df.columns = ['A','B','C','D']

Output

   A  B  C          D
0  X  Y  Z  SOMETHING