Return to Revisions

1. Manually selecting the date columns is error-prone:

   .iloc[0:1, 10:]
   

   Case in point, your original data apparently required 10:, but here your sample requires 7:. Use DataFrame.filter to select the date columns instead of manually indexing them.

2. As a rule of thumb, avoid looping in Pandas:

   for i in range(len(bvmf)):
   

   Note that "transferring columns into rows" is called transposing, so use the Pandas built-in DataFrame.transpose or its alias .T.

So instead of manually crafting another dataframe:

bvmf = pd.read_excel('Market.xlsx')
df = pd.DataFrame()
...

We can just manipulate the original bvmf dataframe and transpose it:

bvmf = (bvmf.set_index('ticker')                 # index will become column header after transpose
            .filter(regex=r'\d{4}-\d{2}-\d{2}')  # select only date columns (named YYYY-MM-DD)
            .T                                   # transpose
            .rename_axis('date')                 # name the new index
)

Output (index named "date" and header named "ticker"):

ticker      CompA  CompB  CompC  CompD
date
1999-07-31  12.30  32.10  32.10  32.10
1999-10-31  23.33  34.44  34.44  34.44
2000-01-31  33.10  23.10  23.10  23.10

Optionally:

• Use DataFrame.reset_index if you want the dates as a regular column like your original code/output.
• Use pd.to_datetime to convert your dates into real datetime objects.