pandas apply individual logic to group
<p>If i have a pandas data frame that looks like:</p>

<pre><code>day id  val
1-Jan   A   -5
2-Jan   A   -4
3-Jan   A   3
1-Jan   B   2
2-Jan   B   1
3-Jan   B   -5
</code></pre>

<p>how can i add a new column where, for all rows with the same id, if val was negative on 1-Jan, all rows are "Y" and "N" if not? something like this:</p>

<pre><code>day id  val neg_on_jan_1
1-Jan   A   -5  y
2-Jan   A   -4  y
3-Jan   A   3   y
1-Jan   B   2   n
2-Jan   B   1   n
3-Jan   B   -5  n
</code></pre>

<p>I've looked at group by and apply-lambda functions but still feel like i'm missing something. I'm just starting out with pandas, coming from a background in SQL, so please forgive me if my brain still thinks in rows and Oracle analytic functions :)</p>

<p>Included <code>map</code> per @Ami Tavory's suggestion</p>

<pre><code>gb = df.set_index(['day', 'id']).groupby(level='id')
s = gb.val.transform(lambda s: s.loc['1-Jan'].lt(0)).map({1: 'y', 0:'n'})
s

day    id
1-Jan  A     y
2-Jan  A     y
3-Jan  A     y
1-Jan  B     n
2-Jan  B     n
3-Jan  B     n
Name: val, dtype: object
</code></pre>

<hr>

<pre><code>df.merge(s.to_frame('neg_on_jan_1').reset_index())
</code></pre>

<p><a href="http://i.stack.imgur.com/wubU6.png" rel="nofollow"><img src="http://i.stack.imgur.com/wubU6.png" alt="enter image description here"></a></p>

python pandas