Getting the last value for each week, with the matching date
<p>So I start out with a <code>pd.Series</code> called <code>jpm</code>, and I would like to group it into weeks and take the last value from each week. This works with the code below, it does get the last value. But it changes corresponding index to the Sunday of the week, and I would like it to <strong>leave it unchaged</strong>.</p>

<pre><code>import pandas_datareader.data as web
import pandas as pd

start = pd.datetime(2015, 11, 1)
end = pd.datetime(2015, 11, 17)

raw_jpm = web.DataReader("JPM", 'yahoo', start, end)["Adj Close"]
jpm = raw_jpm.ix[raw_jpm.index[::2]]
</code></pre>

<p><code>jpm</code> is now</p>

<pre><code>Date
2015-11-02    64.125610
2015-11-04    64.428918
2015-11-06    66.982593
2015-11-10    66.219427
2015-11-12    64.575682
2015-11-16    65.074678
Name: Adj Close, dtype: float64
</code></pre>

<p>I want to do some operations to it, such as</p>

<pre><code>weekly = jpm.groupby(pd.TimeGrouper('W')).last()
</code></pre>

<p><code>weekly</code> is now</p>

<pre><code>Date
2015-11-08    66.982593
2015-11-15    64.575682
2015-11-22    65.074678
Freq: W-SUN, Name: Adj Close, dtype: float64
</code></pre>

<p>which is great, except all my dates got changed. The output I want, is:</p>

<pre><code>Date
2015-11-06    66.982593
2015-11-12    64.575682
2015-11-16    65.074678
</code></pre>

<p>You could provide a <code>DateOffset</code> by specifying the class name <code>Week</code> and indicating the weekly frequency <code>W-FRI</code>, by setting the <code>dayofweek</code> property as 4 [Monday : 0 â Sunday : 6]</p>

<pre><code>jpm.groupby(pd.TimeGrouper(freq=pd.offsets.Week(weekday=4))).last().tail(5)

Date
2016-08-19    65.860001
2016-08-26    66.220001
2016-09-02    67.489998
2016-09-09    66.650002
2016-09-16    65.820000
Freq: W-FRI, Name: Adj Close, dtype: float64
</code></pre>

<hr>

<p>If you want the starting date as the next monday from <code>start</code> date and the previous sunday from the <code>end</code> date, you could do this way:</p>

<pre><code>from datetime import datetime, timedelta

start = datetime(2015, 11, 1)
monday = start + timedelta(days=(7 - start.weekday())) 

end = datetime(2016, 9, 30)
sunday = end - timedelta(days=end.weekday() + 1)

print (monday)
2015-11-02 00:00:00
print (sunday)
2016-09-25 00:00:00
</code></pre>

<p>Then, use it as:</p>

<pre><code>jpm = web.DataReader('JPM', 'yahoo', monday, sunday)["Adj Close"]
jpm.groupby(pd.TimeGrouper(freq='7D')).last()
</code></pre>

<p>To get it all on a Sunday, as you specified the range Monday â Sunday and Sunday being the last day for the date to be considered, you could do a small hack:</p>

<pre><code>monday_new = monday - timedelta(days=3)

jpm = web.DataReader('JPM', 'yahoo', monday_new, sunday)["Adj Close"]
jpm.groupby(pd.TimeGrouper(freq='W')).last().head()

Date
2015-11-01    62.863448
2015-11-08    66.982593
2015-11-15    64.145175
2015-11-22    66.082449
2015-11-29    65.720431
Freq: W-SUN, Name: Adj Close, dtype: float64
</code></pre>

<hr>

<p>Now that you've posted the desired output, you can arrive at the result using <code>transform</code> method instead of taking the aggregated <code>last</code>, so that it returns an object that is indexed the same size as the one being grouped.</p>

<pre><code>df = jpm.groupby(pd.TimeGrouper(freq='W')).transform('last').reset_index(name='Last')

df
</code></pre>

<p><a href="http://i.stack.imgur.com/EIDce.png" rel="nofollow"><img src="http://i.stack.imgur.com/EIDce.png" alt="Image"></a></p>

<pre><code>df['counter'] = (df['Last'].shift() != df['Last']).astype(int).cumsum()
</code></pre>

<p><a href="http://i.stack.imgur.com/uTl0L.png" rel="nofollow"><img src="http://i.stack.imgur.com/uTl0L.png" alt="Image"></a></p>

<pre><code>df.groupby(['Last','counter'])['Date'].apply(lambda x: np.array(x)[-1])   \
  .reset_index().set_index('Date').sort_index()['Last']

Date
2015-11-06    66.982593
2015-11-12    64.575682
2015-11-16    65.074678
Name: Last, dtype: float64
</code></pre>

<p>Note: This is capable of handling repeated entries that occur in two separate dates due to the inclusion of the <code>counter</code> column which bins them separately into two buckets.</p>

python pandas