user8270077
user8270077

Reputation: 5091

Indexing a Pandas Dataframe using the index of a Series

I have a TimeSeries and I want to extract the three first three elements and with them create a row of a Pandas Dataframe with three columns. I can do this easily using a Dictionary for example. The problem is that I would like the index of this one row DataFrame to be the Datetime index of the first element of the Series. Here I fail.

For a reproducible example:

              CRM
Date    
2018-08-30  0.000442
2018-08-29  0.005923
2018-08-28  0.004782
2018-08-27  0.003243

pd.DataFrame({'Reg_Coef_5_1' : ts1.iloc[0][0], 'Reg_Coef_5_2' : ts1.shift(-5).iloc[0][0], \
              'Reg_Coef_5_3' : ts1.shift(-10).iloc[0][0]}, index = ts1.iloc[0].index )

I get:

     Reg_Coef_5_1   Reg_Coef_5_2    Reg_Coef_5_3
CRM   0.000442      0.001041        -0.00035

Instead I would like the index to be '2018-08-30' a datetime object.

Upvotes: 0

Views: 73

Answers (2)

doppler
doppler

Reputation: 997

If I understand you correctly, you would like the index to be a date object instead of "CRM" as it is in your example. Just set the index accordingly: index = [ts1.index[0]] instead of index = ts1.iloc[0].index.

df = pd.DataFrame({'Reg_Coef_5_1' : ts1.iloc[0][0], 'Reg_Coef_5_2' : ts1.shift(-5).iloc[0][0], \
              'Reg_Coef_5_3' : ts1.shift(-10).iloc[0][0]}, index = [ts1.index[0]] )

But as user10300706 has said, there might be a better way to do what you want, ultimately.

Upvotes: 1

Will_E
Will_E

Reputation: 78

If you're simply trying to recover the index position then do:

index = ts1.index[0]

I would note that if you are shifting your dataframe up incrementally (5/10 respectively) the indexes won't aline. I assume, however, you're trying to build out some lagging indicator.

Upvotes: 0

Related Questions