I have the following dataframe (imported from CSV) that needs to be morphed to make it suitable for analysis. The challenge I am facing is that I only have to partially transpose the columns but keep the first few columns fixed.
+--------+-----------+----------+-------------------+------------+-----------------+-----------+-----------+-----------+-----------+-----------+
| number | City | Category | Name | Total bids | Winning Company | Company A | Company B | Company C | Company D | Company E |
+--------+-----------+----------+-------------------+------------+-----------------+-----------+-----------+-----------+-----------+-----------+
| 1 | Bangalore | Open | John Doe | 14,915 | A | 7901 | 1220 | 1,241 | 1231 | 123 |
| 2 | Mysore | Closed | John Doe's Uncle | 16,888 | B | 1234 | 8902 | 341 | 33 | 0 |
| 3 | Mumbai | Open | John Doe's Mother | 13,090 | D | 676 | 831 | 12,341 | 8798 | 12431 |
+--------+-----------+----------+-------------------+------------+-----------------+-----------+-----------+-----------+-----------+-----------+
that needs to be transitioned to the following which I will then use for my regression analysis.
+-----------+----------+-------------------+------------+-----------------+--------+
| City | Category | Name | Total bids | Company | Bid |
+-----------+----------+-------------------+------------+-----------------+--------+
| Bangalore | Open | John Doe | 14,915 | A | 7,901 |
| Bangalore | Open | John Doe | 14,915 | B | 1,220 |
| Bangalore | Open | John Doe | 14,915 | C | 1,241 |
| Bangalore | Open | John Doe | 14,915 | D | 1,231 |
| Bangalore | Open | John Doe | 14,915 | E | 123 |
| Mysore | Closed | John Doe's Uncle | 16,888 | A | 1234 |
| Mysore | Closed | John Doe's Uncle | 16,888 | B | 8902 |
| Mysore | Closed | John Doe's Uncle | 16,888 | C | 341 |
| Mysore | Closed | John Doe's Uncle | 16,888 | D | 33 |
| Mysore | Closed | John Doe's Uncle | 16,888 | E | 0 |
| Mumbai | Open | John Doe's Mother | 13,090 | A | 676 |
| Mumbai | Open | John Doe's Mother | 13,090 | B | 831 |
| Mumbai | Open | John Doe's Mother | 13,090 | C | 12,341 |
| Mumbai | Open | John Doe's Mother | 13,090 | D | 8798 |
| Mumbai | Open | John Doe's Mother | 13,090 | E | 12431 |
+-----------+----------+-------------------+------------+-----------------+--------+
My attempts at converting the table did not work, and doing this manually is nearly impossible as it is a really large table. This is qualitatively different from the reshaping questions asked previously because the reshape here has to keep the first few columns fixed but transpose only the ones after the column titled "name".
