Create new dataframe column equal to the value above row = 'Name'

Question

I combined several excel worksheets into a new workbook using pandas that looks like the following:

Example Excel Workbook

I am trying to now clean up the workbook/dataframe using python (for practice) to by creating a new column where the equal to the table name which is listed in col[0] above 'Name'. I know how to do it in excel, but am trying to learn how to transform the data using python. There are 7051 rows currently in the dataset if that help.

The final outcome would look something like this:

Example Solution

Please let me know if you have any ideas on how to further clean it up using python. I have the excel solution but am really hoping to learn how to do it with python.

Example of code used to combine worksheets:

import pandas as pd
import numpy as np
import os, collections, csv
from os.path import basename

df = []
f = 'ex_DATA.xlsx'
numberOfSheets = 22 #Modify this. 

for i in range(1,numberOfSheets+1):
    data = pd.read_excel(f, sheetname = 'TAB_'+str(i), header=None)
    df.append(data)
final = "ex_DATA2.xlsx" #Path to the file in which new sheet will be saved.
df = pd.concat(df)
df = df.dropna(axis=0, how='all')
df.to_excel(final, header=None, index=None)