Reading Data with Groups of Columns using Pandas

This is possible, but a little tricky as a result of the non-rectangularity of your data. Pandas allows data to be read with multiple levels of columns, but your issue is that your first level of header has cells missing (i.e. 'baseline' does not appear in cell C3.) Pandas will fill these missing cells with an 'Unnamed' column, but won't recognize that these unnamed columns should be 'baseline'.

In order to mitigate this, we'll have to rename the columns levels. Then we'll set the index to be year, and drop this column from all the scenarios:

First, we read the file without setting the index, and without skipping columns:

df = pd.read_excel("sample_data.xlsx", header=[2, 3])

  Unnamed: 0_level_0 baseline Unnamed: 2_level_0 Unnamed: 3_level_0 Unnamed: 4_level_0 scenario1 Unnamed: 6_level_0 Unnamed: 7_level_0
                Year  A value      Another value               Etc.               Year   A value      Another value               Etc.
0               1900     4.74               7.08                0.0               1900      4.74               7.08             293.72

Then, we build lists of the two levels of the columns so that we can assign them later:

scenarios = [col for col in df.columns.get_level_values(0) if not col.startswith('Unnamed')]
values = []
for col in df.columns.get_level_values(1):
    if col not in values:
        values.append(col)

This gives us multiindex column levels of:

scenarios
['baseline', 'scenario1']

values                                                                                                                                                                                              

['Year', 'A value', 'Another value', 'Etc.']

Now we assign the new multiindex columns using these level lists:

df.columns = df.columns.from_product([scenarios, values], names=['scenario','value'])

scenario baseline                            scenario1                              
value        Year A value Another value Etc.      Year A value Another value    Etc.
0            1900    4.74          7.08  0.0      1900    4.74          7.08  293.72

Finally, we assign the baseline year as the index and drop year from all the scenarios:

df = df.set_index(('baseline', 'Year'), drop=False)
df = df.drop('Year', axis=1, level=1)

scenario         baseline                    scenario1                      
value             A value Another value Etc.   A value Another value    Etc.
(baseline, Year)                                                            
1900                 4.74          7.08  0.0      4.74          7.08  293.72

One final note: as a general rule, keep your data rectangular! If you do that, automated processing will be a lot easier.

EDIT: The issue you're having is that you have two columns which are supposed to be the same, but are spelled slightly differently:

Column 1:

Global CO2 emissions
(GtonsCO2/year)

Column 16:

Global CO2 Emissions
(GtonsCO2/year)

To handle cases like this, you'll want to select the second level of the columns in a different way:

df = pd.read_excel("sample_data.xlsx", header=[0, 1])

scenarios = [col for col in df.columns.get_level_values(0) if not col.startswith('Unnamed')]

# Instead of taking one of each unique column names, we take the first n columns,
# where n is the total number of columns divided by the number of scenarios
values = df.columns.get_level_values(1)[:int(len(df.columns) / len(scenarios))]

df.columns = df.columns.from_product([scenarios, values], names=['scenario','value'])
df = df.set_index(('baseline', 'Time'), drop=False)
df = df.drop('Time', axis=1, level=1)

scenario                                      baseline                                                   ...                         scenario1                                                  
value            Global CO2 emissions\n(GtonsCO2/year) Global CO2 Equivalent Emissions\n(GtonsCO2/year)  ... Global PFC emissions\n(tons/year) Global CO2eq emissions from HFC\n(GtonsCO2e/year)
(baseline, Time)                                                                                         ...                                                                                    
1900                                               NaN                                              NaN  ...                               NaN                                               NaN
1901                                               NaN                                              NaN  ...                               NaN                                               NaN