pct_change with freq on groupby broken

[bobobo1618]

>>> pd.__version__
u'0.17.1'
# Column 1 is a date column, column 0 is a text column representing a stock symbol, column 12 is a price
>>> rawdat = pd.read_csv('./data.csv', parse_dates=[1], index_col=[0, 1], usecols=[0, 1, 12])
# Get monthly change in value for each stock symbol
>>> rawdat.groupby(level=0).pct_change(freq='M')
<Empty>
>>> rawdat.groupby(level=0).pct_change()
<Lots of data>
>>> rawdat.loc[['AAPL']].reset_index(level=0, drop=True).pct_change(freq='M')
<Lots of data>

[bobobo1618]

Seems resample with a custom how is broken too?

>>> rawdat.groupby(['Symbol']).resample('1M', label='right', how='sum')
<Lots of data>
>>> rawdat.groupby(['Symbol']).resample('1M', label='right', how=lambda period: (period[-1]/period[0]) - 1.0)
<Empty>

[sinhrks]

Thanks for the report. Can you attach the content of data.csv as small copy-pastable format?

[bobobo1618]

I'm unable to provide the data itself because it's licensed (and several GB) unfortunately.

When trying to give you a subset though, I discovered that the bug was only reproducible when I read more than 1032393 (an odd but specific number) rows from the CSV. If I read less, the functions above worked perfectly. If I read more, they all broke completely:

>>> rawdat = pd.read_csv('./data.csv', parse_dates=[1], index_col=[1], usecols=[0, 1, 12], nrows=1032393)
>>> rawdat.groupby(['Symbol']).resample('1M', label='right', how=lambda period: (period[-1]/period[0]) - 1.0)
<Data>
>>> rawdat = pd.read_csv('./data.csv', parse_dates=[1], index_col=[1], usecols=[0, 1, 12], nrows=1032394)
>>> rawdat.groupby(['Symbol']).resample('1M', label='right', how=lambda period: (period[-1]/period[0]) - 1.0)
<Empty>

Since that number was so specific, I looked into the data and saw that at that row, there was a gap of a few months in that symbol's data:

2014-06-27  AMBCW   17.490000
2014-09-24  AMBCW   13.500000

I skipped over that with skiprows and found a similar break at:

2004-12-16  AMBI    0.3000
2005-02-28  AMBI    0.3000

So I suspect that the problem is really the gap in the data.

Also though, I found that grouping by multiindex levels didn't work:

# Column 0 is a symbol. 
>>> rawdat = pd.read_csv('./data.csv', parse_dates=[1], index_col=[0, 1], usecols=[0, 1, 12], nrows=1000)
>>> rawdat.reset_index(level=0).groupby(['Symbol']).resample('1M', label='right', how=lambda period: (period[-1]/period[0]) - 1.0)
<Lots of data>
>>> rawdat.groupby(level=0).resample('1M', label='right', how=lambda period: (period[-1]/period[0]) - 1.0)
<Empty>

I can share 1000 rows with you so here's all you need to reproduce the multiindex problem.

[jreback]

@bobobo1618 the last might be a bug. Can you put together a copy-pastable example that is reproducible.

[bobobo1618]

Replacing my function with this caused it to work:

def month_change_resample(arraylike):
    if len(arraylike) == 0:
        return 0
    return (arraylike[-1]/arraylike[0]) - 1.0

It seems some of the arraylike objects were of length zero, which would have caused an IndexError during the execution. Is outputting nothing expected behaviour in that situation?

[bobobo1618]

Ah sorry, no it didn't.

Copy pastable example:

curl http://ix.io/mJE > data.csv
cat << EOF > test.py
import pandas as pd
rawdat = pd.read_csv('./data.csv', parse_dates=[1], index_col=[0, 1], usecols=[0, 1, 12], nrows=1000)
def month_change_resample(arraylike):
    if len(arraylike) == 0:
        return 0
    return (arraylike[-1]/arraylike[0]) - 1.0
print("Level result: ")
print(rawdat.groupby(level=0).resample('1M', label='right', how=month_change_resample))
print("No level result:")
print(rawdat.reset_index(level=0).groupby(['Symbol']).resample('1M', label='right', how=month_change_resample))
EOF
python test.py

[jreback]

@bobobo1618 a copy-pastable example is one that I can actually copy and past.

IOW, no files are involved. The dataframe should be much shorter.

[jreback]

not reproducible and likely same issue as in #21200