How to unmerge cells and create a standard dataframe when reading excel file?-CodePudding

I would like to convert this dataframe

into this dataframe

So far reading excel the standard way gives me the following result.

df = pd.read_excel(folder   'abcd.xlsx', sheet_name="Sheet1")

    Unnamed: 0                   Unnamed: 1 T12006      T22006      T32006  \
0   Casablanca                      Global     100    97.27252   93.464538   
1          NaN                  Résidentiel    100   95.883979   92.414063   
2          NaN                  Appartement    100   95.425152   91.674379   
3          NaN                      Maison     100  101.463607  104.039383   
4          NaN                       Villa     100   102.45132  101.996932

Thank you

CodePudding user response：

You can try method .fillna() with parameter method='ffill'. According to the pandas documentation for the ffill method: ffill: propagate last valid observation forward to next valid backfill.
So, your code would be like:

df.fillna(method='ffill', inplace=True)

And change name of 0 and 1 columns with this lines:

df.columns.values[0] = "City"
df.columns.values[1] = "Type"