Как работает параметр header в функции read_csv()?

Функция pd.read_csv здесь не причём - вы просто передали параметру header конструкцию с синтаксической ошибкой:

print([0:2])
------------------------------------------------------
  File "<ipython-input-63-1c502b3dc513>", line 1
    [0:2]
      ^
SyntaxError: invalid syntax

Срезы можно применять к соответствующим объектам, например к спискам или кортежам. У вас же он сам по себе и Python не понимает такой синтаксической конструкции.

я ещё хочу знать что именно такое параметр header и для чего он нужен

Параметр header используется для явного указания - какие строки считать заголовками (наименованиями столбцов). Например в том случае если в файле отсутствуют наименования столбцов - нужно использовать header=None - в этом случае столбцы будут пронумерованы: 0, 1, 2, ....

По умолчанию pd.read_csv(...) считает что первая строка CSV файла содержит наименования столбцов. Другими словами - значения параметра header по умолчанию - 1.

Также Pandas поддерживает многоуровневые наименования столбцов / multi-level columns - в этом случае параметру header нужно передать список с индексами строк файла, в которых содержатся наименования столбцов (индексация начинается с нуля).

Это просто список индексов строк, из которых нужно взять заголовок. Если много строк указать - будет multyindex, а не просто индекс в заголовке. Вы пытаетесь сделать питоновский срез, но срез делается от коллекции, просто так ниотчего его нельзя сделать, поэтому на последнем скрине у вас ошибка. В принципе, того, чего вы хотели добиться таким параметром, скорее всего можно сделать, если указать header=range(3), это будет эквивалентно header=[0, 1, 2].