[FIXED] Pandas Python - How to read data from excel with grouped/collapsable columns file using pandas

Issue

I'm working on a webapp which has a feature to allow users to upload their excel files, then the app extract data from those files using pandas library. Now i'm having a problem that it's that some users could upload some files with excel grouping columns feature. just like in the image:

The problem is that pandas doesn't seem to properly read those columns and returns an empty dataframe.

I've checked this answer which is somewhat similar to my issue but the accepted answer doesn't apply to my case because my app should process user files as uploaded without modifications before throw them into pandas.

I've tried playing around with the options offered in the other answers, but they all involve modifying the file or knowing exactly which columns or rows will be grouped which is not my case.

So, is there any way to do this with pandas? I have the option to instruct users to make sure that their files do not contain this kind of grouping columns/rows but i'd like to minimize the amount of effort users have to put into their files before uploading them to my app.

Solution

Just to avoid confusion for other users, Pandas works fine whether columns are grouped/collapsed or not:

df = pd.read_excel('data.xlsx')
print(df)

# Output
  NUTS0 NUTS1 NUTS2  Value
0    FR   FR0  FR01     10
1    FR   FR0  FR02     20
2    FR   FR1  FR10     30

Answered By - Corralien