Computing diffs within groups of a dataframe

Question

Say I have a dataframe with 3 columns: Date, Ticker, Value (no index, at least to start with). I have many dates and many tickers, but each (ticker, date) tuple is unique. (But obviously the same date will show up in many rows since it will be there for multiple tickers, and the same ticker will show up in multiple rows since it will be there for many dates.)

Initially, my rows in a specific order, but not sorted by any of the columns.

I would like to compute first differences (daily changes) of each ticker (ordered by date) and put these in a new column in my dataframe. Given this context, I cannot simply do

df['diffs'] = df['value'].diff() 

because adjacent rows do not come from the same ticker. Sorting like this:

df = df.sort(['ticker', 'date']) df['diffs'] = df['value'].diff() 

doesn't solve the problem because there will be "borders". I.e. after that sort, the last value for one ticker will be above the first value for the next ticker. And computing differences then would take a difference between two tickers. I don't want this. I want the earliest date for each ticker to wind up with an NaN in its diff column.

This seems like an obvious time to use groupby but for whatever reason, I can't seem to get it to work properly. To be clear, I would like to perform the following process:

1. Group rows based on their ticker
2. Within each group, sort rows by their date
3. Within each sorted group, compute differences of the value column
4. Put these differences into the original dataframe in a new diffs column (ideally leaving the original dataframe order in tact.)

I have to imagine this is a one-liner. But what am I missing?

---

Edit at 9:00pm 2013-12-17

Ok...some progress. I can do the following to get a new dataframe:

result = df.set_index(['ticker', 'date'])\     .groupby(level='ticker')\     .transform(lambda x: x.sort_index().diff())\     .reset_index() 

But if I understand the mechanics of groupby, my rows will now be sorted first by ticker and then by date. Is that correct? If so, would I need to do a merge to append the differences column (currently in result['current'] to the original dataframe df?

Answer 1

wouldn't be just easier to do what yourself describe, namely

df.sort(['ticker', 'date'], inplace=True) df['diffs'] = df['value'].diff() 

and then correct for borders:

mask = df.ticker != df.ticker.shift(1) df['diffs'][mask] = np.nan 

to maintain the original index you may do idx = df.index in the beginning, and then at the end you can do df.reindex(idx), or if it is a huge dataframe, perform the operations on

df.filter(['ticker', 'date', 'value']) 

and then join the two dataframes at the end.

edit: alternatively, ( though still not using groupby )

df.set_index(['ticker','date'], inplace=True) df.sort_index(inplace=True) df['diffs'] = np.nan   for idx in df.index.levels[0]:     df.diffs[idx] = df.value[idx].diff() 

for

   date ticker  value 0    63      C   1.65 1    88      C  -1.93 2    22      C  -1.29 3    76      A  -0.79 4    72      B  -1.24 5    34      A  -0.23 6    92      B   2.43 7    22      A   0.55 8    32      A  -2.50 9    59      B  -1.01 

this will produce:

             value  diffs ticker date               A      22     0.55    NaN        32    -2.50  -3.05        34    -0.23   2.27        76    -0.79  -0.56 B      59    -1.01    NaN        72    -1.24  -0.23        92     2.43   3.67 C      22    -1.29    NaN        63     1.65   2.94        88    -1.93  -3.58 

Answer 2

Ok. Lots of thinking about this, and I think this is my favorite combination of the solutions above and a bit of playing around. Original data lives in df:

df.sort(['ticker', 'date'], inplace=True)  # for this example, with diff, I think this syntax is a bit clunky # but for more general examples, this should be good.  But can we do better? df['diffs'] = df.groupby(['ticker'])['value'].transform(lambda x: x.diff())   df.sort_index(inplace=True) 

This will accomplish everything I want. And what I really like is that it can be generalized to cases where you want to apply a function more intricate than diff. In particular, you could do things like lambda x: pd.rolling_mean(x, 20, 20) to make a column of rolling means where you don't need to worry about each ticker's data being corrupted by that of any other ticker (groupby takes care of that for you...).

So here's the question I'm left with...why doesn't the following work for the line that starts df['diffs']:

df['diffs'] = df.groupby[('ticker')]['value'].transform(np.diff) 

when I do that, I get a diffs column full of 0's. Any thoughts on that?