Date is not working even when date column is set to index

Question

I have a multiple dataframe dictionary where the index is set to 'Date' but am having a trouble to capture the specific day of a search.

Dictionary created as per link:

Call a report from a dictionary of dataframes

Then I tried to add the following column to create specific days for each row:

df_dict[k]['Day'] = pd.DatetimeIndex(df['Date']).day

It´s not working. The idea is to separate the day of the month only (from 1 to 31) for each row. When I call the report, it will give me the day of month of that occurrence.

More details if needed.

Regards and thanks!

Answer 1

• In the case of your code, there is no 'Date' column, because it's set as the index.
  • df_dict = {f.stem: pd.read_csv(f, parse_dates=['Date'], index_col='Date') for f in files}
• To extract the day from the index use the following code.
  • df_dict[k]['Day'] = df.index.day
• Pulling the code from this question

# here you can see the Date column is set as the index
df_dict = {f.stem: pd.read_csv(f, parse_dates=['Date'], index_col='Date') for f in files}

data_dict = dict()  # create an empty dict here
for k, df in df_dict.items():
    df_dict[k]['Return %'] = df.iloc[:, 0].pct_change(-1)*100

    # create a day column; this may not be needed
    df_dict[k]['Day'] = df.index.day 

    # aggregate the max and min of Return
    mm = df_dict[k]['Return %'].agg(['max', 'min']) 

    # get the min and max day of the month
    date_max = df.Day[df['Return %'] == mm.max()].values[0]
    date_min = df.Day[df['Return %'] == mm.min()].values[0]
    
    # add it to the dict, with ticker as the key
    data_dict[k] = {'max': mm.max(), 'min': mm.min(), 'max_day': date_max, 'min_day': date_min}

# print(data_dict)
[out]:
{'aapl': {'max': 8.702843218147871,
          'max_day': 2,
          'min': -4.900700398891522,
          'min_day': 20},
 'msft': {'max': 6.603769278967109,
          'max_day': 2,
          'min': -4.084428935702855,
          'min_day': 8}}