asfreq() returns an empty dataframe

Question

I have a dataframe with a DateTimeIndex:

import pandas as pd
from pandas.tseries.offsets import *

data = pd.read_excel('P:\\Simon\\govt_bond_yields.xlsx')
data.head()

              USA   Italy   UK   EURO ZONE  GREECE  GERMANY
2018-06-25  2.8748  2.782   1.299   0.327   4.102   0.327
2018-06-22  2.8949  2.694   1.319   0.335   4.114   0.335
2018-06-21  2.8967  2.732   1.277   0.333   4.279   0.333
2018-06-20  2.9389  2.549   1.297   0.375   4.332   0.375
2018-06-19  2.8967  2.557   1.283   0.370   4.344   0.370

Currently my index has no frequency

data.index

DatetimeIndex(['2018-06-25', '2018-06-22', '2018-06-21', '2018-06-20',
               '2018-06-19', '2018-06-18', '2018-06-15', '2018-06-14',
               '2018-06-13', '2018-06-12',
               ...
               '2015-01-27', '2015-01-26', '2015-01-23', '2015-01-22',
               '2015-01-21', '2015-01-20', '2015-01-16', '2015-01-15',
               '2015-01-14', '2015-01-13'],
              dtype='datetime64[ns]', length=862, freq=None)

I'm trying to set the index frequency, but after I do that I get an empty dataframe

data.asfreq(freq='D')

USA Italy UK EURO ZONE  GREECE  GERMANY

What am I doing wrong here?

Answer 1

This should work if you sort the index first, as asfreq has a hard time knowing what you want to do otherwise. For example:

# Unsorted data with a datetime index:
>>> data
               USA  Italy     UK  EURO ZONE  GREECE  GERMANY
2018-06-25  2.8748  2.782  1.299      0.327   4.102    0.327
2018-06-22  2.8949  2.694  1.319      0.335   4.114    0.335
2018-06-21  2.8967  2.732  1.277      0.333   4.279    0.333
2018-06-20  2.9389  2.549  1.297      0.375   4.332    0.375
2018-06-19  2.8967  2.557  1.283      0.370   4.344    0.370

>>> data.sort_index().asfreq(freq='D')
               USA  Italy     UK  EURO ZONE  GREECE  GERMANY
2018-06-19  2.8967  2.557  1.283      0.370   4.344    0.370
2018-06-20  2.9389  2.549  1.297      0.375   4.332    0.375
2018-06-21  2.8967  2.732  1.277      0.333   4.279    0.333
2018-06-22  2.8949  2.694  1.319      0.335   4.114    0.335
2018-06-23     NaN    NaN    NaN        NaN     NaN      NaN
2018-06-24     NaN    NaN    NaN        NaN     NaN      NaN
2018-06-25  2.8748  2.782  1.299      0.327   4.102    0.327

You can check the index to make sure it worked:

# Check the index:
>>> data.sort_index().asfreq(freq='D').index
DatetimeIndex(['2018-06-19', '2018-06-20', '2018-06-21', '2018-06-22',
               '2018-06-23', '2018-06-24', '2018-06-25'],
              dtype='datetime64[ns]', freq='D')