Return to Answer

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

   .iloc[0:1, 10:]
   

   Case in point, your original data apparently required 10:, but the sample you posted here actually 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. Use DataFrame.transpose or its alias DataFrame.T to swap the rows and columns without looping.

So instead of manually crafting a separate df, 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 YYYY-MM-DD columns
            .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.

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

   for i in range(len(bvmf)):
   

   Note that "transferring columns into rows" is called transposing. Use DataFrame.transpose or its alias DataFrame.T to swap the rows and columns without looping.

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

   .iloc[0:1, 10:]
   

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

So instead of manually crafting a separate df, 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 YYYY-MM-DD columns
            .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.

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.

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

   .iloc[0:1, 10:]
   

   Case in point, your original data apparently required 10:, but the sample you posted here actually 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. Use DataFrame.transpose or its alias DataFrame.T to swap the rows and columns without looping.

So instead of manually crafting a separate df, 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 YYYY-MM-DD columns
            .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.

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 columns 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.

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.