2

How to resample the dataframe without changing it's core?

import pandas as pd
import sys
if sys.version_info[0] < 3: 
    from StringIO import StringIO
else:
    from io import StringIO

csvdata = StringIO("""date,LASTA,LASTB,LASTC
1999-03-15,2.5597,8.20145,16.900
1999-03-16,2.6349,8.03439,17.150
1999-03-17,2.6375,8.12431,17.125
1999-03-18,2.6375,8.27908,16.950
1999-03-19,2.6634,8.54914,17.325
1999-03-22,2.6721,8.32183,17.195
1999-03-23,2.6998,8.21218,16.725
1999-03-24,2.6773,8.15284,16.350
1999-03-25,2.6807,8.08378,17.030
1999-03-26,2.7802,8.14038,16.725
1999-03-29,2.8139,8.07832,16.800
1999-03-30,2.8105,8.10124,16.775
1999-03-31,2.7724,7.73057,16.955
1999-04-01,2.8321,7.63714,17.500
1999-04-06,2.8537,7.63703,17.750""")

df = pd.read_csv(csvdata, sep=",", index_col="date", parse_dates=True, infer_datetime_format=True)

This is my code...

# Join 3 stock DataFrame together
full_df = pd.concat([AAAA, BBBB, CCCC], axis=1).dropna()

# Resample the full DataFrame to monthly timeframe
monthly_df = full_df.resample('BMS').first()

# Calculate daily returns of stocks
returns_daily = full_df.pct_change()

# Calculate monthly returns of the stocks
returns_monthly = monthly_df.pct_change().dropna()
print(returns_monthly.tail())

this is the error that I get...

TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'Index'

I've already tried the pd.Dataframe then DataTimeIndex even pd.to_datetime, but somehow I only make things worse

Rich Andrews
  • 1,590
  • 8
  • 12
Thiago AV
  • 55
  • 4
  • Welcome to stackoverflow Thiago. Are you able to provide a small sample of your data? – Chris Adams Apr 05 '19 at 20:43
  • please check https://stackoverflow.com/help/mcve for how to supply a minimal, complete and verifiable example – Chris Adams Apr 05 '19 at 20:48
  • 1
    You should [edit] your question to include the data requested, you'll have no luck trying to do it in comments. – roganjosh Apr 05 '19 at 20:51
  • 3
    @ChrisA pro-tip after searching for links to that basically every day. Just type `[mcve]` (but without the backticks) to auto-generate the hyperlink with its name. Same with the `[edit]` I used that auto-generates the link for the OP to edit their post – roganjosh Apr 05 '19 at 20:57
  • 1
    @roganjosh mind... blown..! thanks a lot for that tip buddy. – Chris Adams Apr 05 '19 at 20:58
  • 3
    So you've tried `df.index = pd.to_datetime(df.index)` and it still has an issue? Seems that you just have normal strings as your index and not a `DatetimeIndex`. What is `type(df.index)`? – ALollz Apr 05 '19 at 21:02
  • thank you gentlemen! It finally worked. – Thiago AV Apr 08 '19 at 14:11

2 Answers2

1

The issue is that the indexes of the concatenated dataframes are not a resample-able type. Here is a working version of your MCVE that shows how to convert the index of the data to resample-able type.

import pandas as pd
import sys
if sys.version_info[0] < 3: 
    from StringIO import StringIO
else:
    from io import StringIO

csvdata = StringIO("""date,LASTA,LASTB,LASTC
1999-03-15,2.5597,8.20145,16.900
1999-03-16,2.6349,8.03439,17.150
1999-03-17,2.6375,8.12431,17.125
1999-03-18,2.6375,8.27908,16.950
1999-03-19,2.6634,8.54914,17.325
1999-03-22,2.6721,8.32183,17.195
1999-03-23,2.6998,8.21218,16.725
1999-03-24,2.6773,8.15284,16.350
1999-03-25,2.6807,8.08378,17.030
1999-03-26,2.7802,8.14038,16.725
1999-03-29,2.8139,8.07832,16.800
1999-03-30,2.8105,8.10124,16.775
1999-03-31,2.7724,7.73057,16.955
1999-04-01,2.8321,7.63714,17.500
1999-04-06,2.8537,7.63703,17.750""")

#df = pd.read_csv(csvdata, sep=",", index_col="date", parse_dates=True, infer_datetime_format=True)
#print(type(df.index))

df = pd.read_csv(csvdata, sep=",")
df.set_index(['date'], inplace=True)
print(type(df.index))
df.index = pd.to_datetime(df.index)
print(type(df.index))

# Join 3 stock DataFrame together
full_df = pd.concat([df, df, df], axis=1).dropna()
#print(full_df)

# Resample the full DataFrame to monthly timeframe
monthly_df = full_df.resample('BMS').first()
#print(monthly_df)

# Calculate daily returns of stocks
returns_daily = full_df.pct_change()
#print(returns_daily)

# Calculate monthly returns of the stocks
returns_monthly = monthly_df.pct_change().dropna()
#print(returns_monthly.tail())
Rich Andrews
  • 1,590
  • 8
  • 12
1

Please convert index to Datetime index:

full_df.index = pd.to_datetime(full_df.index)
Hubert Dudek
  • 1,666
  • 1
  • 13
  • 21