I am using Python/Pandas to extract data from an excel file and do some processes but I am stuck dealing with this:

I have this data (is not formatted as a table) in my data frame:

And I would it to look this:

I tried to get it to work since Wednesday until Friday but I just could not figure out the logic for it.

It can be that I also have several car brands and all of them would be above the string “SUV” and the car types will always be SUV, SEDAN,TRUCK and the corresponding values under them.

I tried to go about by creating a nested dictionary, where I would save the string right above SUV as the “key” then SUV,SEDAN,TRUCK as the nested keys, and their values as the numbers that are under each of them respectively, it should have looked something like this:

brand_1 : { Tesla cars: {SUV: [10,20], SEDAN:[20,30], TRUCK:[5,10]}

brand_2: { Toyota_cars: {SUV:[15,25], SEDAN:[25,35], TRUCK:[10,15]}

Then unpack the main key(Tesla Cars, Toyota Cars) in a new column and the values of the nested keys( [10,20],[20,25], [5,10], [15,25], SEDAN[25,35], [10,15] in their respective columns. But of course it did not work.

I am sure I am overcomplicating things and that there is a much easier method and logic to achieve this, can you please guide me to the correct way to deal with such data?

I tried creating a nested dictionary with the car brand as the main key and the different type of cars as their nested keys with their values and then unpack but it did not work. My logic cannot create the correct dictionary, I guess I am not relating the keys and values correctly.