Home > Mobile >  How to implement a pandas dataframe calendar with varying inputs for each cell
How to implement a pandas dataframe calendar with varying inputs for each cell

Time:01-19

So, I've a problem that I managed to get a solution but it doesn't feel right nor it's efficient. There's a list of assets assets = ['1', '2', '3', ..., 'n'], and for each asset, I've a unique input for each date in a date range as date_range = ['2023-01-01', '2023-01-02', ..., '202x-xx-xx'].

To build the calendar I've implemented as:

#list of assets
assets = ['A1','A2','A3','A4']

#Dataframe with data that will be used to build the calendar and be the reference for inputs
data = {'PRODUCT': ['A1', 'B1', 'C1', 'D1'], 'DATE': [2023-01-02, 2023-07-15, 2023-12-21]}   
df_data = pd.DataFrame(data)
  
#Building the columns of the calendar
today = pd.Timestamp(2023, 1, 1)
today_str = str(today)

columns = list()
columns.append('ASSETS')

date_max = df_data['DATE'].max()
delta = (data_max - today).days

for i in range(0, delta 1):
    columns.append(str((today timedelta(days=i)).date()))

#Building the calendar and using the assets as index
df_calendar = pd.DataFrame(columns = columns)
df_calendar['ASSETS'] = assets
df_calendar.index = list(df_calendar['ASSETS'])
df_calendar= df_calendar.drop('ASSETS', axis=1)
df_calendar= df_calendar.fillna(0)

The final result is:

index 2023-01-01 2023-01-02 .......... 2023-12-21
A1 0 0 . 0
A2 0 0 . 0
A3 0 0 . 0
A4 0 0 . 0

Any help or ideas are welcomed, thank you all!

I started experimenting with pd.date_range and will try another solution.

CodePudding user response:

IIUC, you can use:

import pandas as pd

assets = [f'A{i}' for i in range(1, 10)]
date_range = pd.date_range('2023-01-14', '2023-01-19', freq='D')
df = pd.DataFrame(0, index=assets, columns=date_range)

Output:

>>> df
    2023-01-14  2023-01-15  2023-01-16  2023-01-17  2023-01-18  2023-01-19
A1           0           0           0           0           0           0
A2           0           0           0           0           0           0
A3           0           0           0           0           0           0
A4           0           0           0           0           0           0
A5           0           0           0           0           0           0
A6           0           0           0           0           0           0
A7           0           0           0           0           0           0
A8           0           0           0           0           0           0
A9           0           0           0           0           0           0
  • Related