How to turn header inside rows into columns?

Using a slightly simpler example, this works, but it sure ain't pretty:

df = pd.DataFrame({
    'date': ['James', 'Sales', 'City', 'Kim', 'Sales', 'City',],
    '2020': ['', '3', 'NY', '', '4', 'SF'],
    '2021': ['', '4', 'NY', '', '5', 'SF'],
})

def rows_to_columns(group):
    for value in group.date.values:
        if value != group.person.values[0] and value != 'Sales':
            temp_column = '_'+value
            group.loc[group['date']==value, temp_column] = group['2020']
            group[value.lower()] = (
               group[temp_column]
               .fillna(method='ffill')
               .fillna(method='bfill')
            )
            group.drop([temp_column], axis=1, inplace=True)
            pass
        pass
    return group


df.loc[df['2020']=='', 'person'] = df.date
df.person = df.person.fillna(method='ffill')
new_df = (df
      .groupby('person')
      .apply(lambda x:rows_to_columns(x))
      .drop(['date'], axis=1)
      .loc[df.date=='Sales']
)

The basic idea is to

1. Copy the name into a separate column and fill that column using .fillna(method='ffill'). This works if the assumption holds that every person's block begins with the person's name. Otherwise it wreaks havoc.

2. All other values, such as 'div' and 'city' will be converted by row_to_columns(group). The function iterates over all rows in a group that are neither the person's name nor 'Sales', copies the value from the row into a temp column, creates a new column for that row and uses ffill and bfill to fill it out. It then deletes the temp column and returns the group.

3. The resulting data frame is the intended format once the column 'Sales' is dropped.

Note: This solution probably does not work well on larger datasets.

You gave more details, and I see you are not working with multi-level indexes. The best way for you would be to create the DataFrame already in the format you need in this case. The way you are creating the first DataFrame is not well structured and the information is not indexed by name (James/KIM) as they are columns with empty values, no link with the other values. The stacking you did use blank spaces on a string. Take a look at multi-indexing and generate a data frame you can work with, or create the data frame in the format you need in the end.

-- Answer considering multi-level indexes --

Using the few information provided, I see your Dataframe is stacked, it means, you have multiple indexes. The first level is person (James/KIM) and the second level is Sales/City/DIV. So your Dataframe should be created like this:

import pandas

multi_index = pandas.MultiIndex.from_tuples([
    ('James', 'Sales'), ('James', 'City'), ('James', 'DIV'),
    ('KIM', 'Sales'), ('KIM', 'City'), ('KIM', 'DIV')])

year_2020 = pandas.Series([3, 'NY', 'a', 4, 'SF', 'b'], index=multi_index)
year_2021 = pandas.Series([4, 'NY', 'a', 5, 'SF', 'b'], index=multi_index)
year_2022 = pandas.Series([5, 'NY', 'a', 6, 'SF', 'b'], index=multi_index)

frame = { '2020': year_2020, '2021': year_2021, '2022': year_2022} 

df = pandas.DataFrame(frame)

print(df)

            2020 2021 2022
James Sales    3    4    5
      City    NY   NY   NY
      DIV      a    a    a
KIM   Sales    4    5    6
      City    SF   SF   SF
      DIV      b    b    b

Now that you have the multi_level DataFrame, you have many ways to transform it. This is what we will do to make it one level:

sales_df = df.xs('Sales', axis=0, level=1).copy()
div_df = df.xs('DIV', axis=0, level=1).copy()
city_df = df.xs('City', axis=0, level=1).copy()

The results will be:

print(sales)

      2020 2021 2022
James    3    4    5
KIM      4    5    6

print(div_df)

      2020 2021 2022
James    a    a    a
KIM      b    b    b

print(city_df)

      2020 2021 2022
James   NY   NY   NY
KIM     SF   SF   SF

You are discarding any information regarding DIV or City changes from years, so we can reduce the City and DIV dataframe to a Series, taking the first one as reference:

div_series = div_df.iloc[:,0]
city_series = city_df.iloc[:,0]

Take the sales DF as reference, and add the City and DIV series:

sales_df['DIV'] = div_series
sales_df['City'] = city_series
sales_df['Account'] = 'Sales'

Now reorder the columns as you wish:

sales_df = sales_df[['City', 'DIV', 'Account', '2020', '2021', '2022']]

print(sales_df)

      City DIV Account 2020 2021 2022
James   NY   a   Sales    3    4    5
KIM     SF   b   Sales    4    5    6