mkdir -p extracts scripts; cp tool-outputs/juniper_rent_roll_latest.json tool-outputs/juniper_rent_ts_month.json extracts/; cd extracts; python3 -c "
import json,pandas as pd
d=json.load(open('juniper_rent_ts_month.json'))
rows=[r['mf_rent_ts'] for r in d['data']]
df=pd.DataFrame(rows).sort_values('period_start_date')
pd.set_option('display.width',250)
print(df[['period_start_date','asking_rent_latest_avg','asking_rent_sqft_latest_avg','in_place_rent_latest_avg','in_place_rent_sqft_latest_avg','num_vacant_units','num_unleased_units_latest','num_leases_signed','days_on_market_median','tradeout_new_lease_amt_avg','tradeout_new_lease_pct_avg','tradeout_sample_size','unit_count','occupancy_latest']].to_string())
"

cd extracts; python3 -c "
import json,pandas as pd
d=json.load(open('juniper_rent_roll_latest.json'))
df=pd.DataFrame([r['rent_roll_latest'] for r in d['data']])
pd.set_option('display.width',250); pd.set_option('display.max_columns',30)
print(df.period_start_date.value_counts())
print(df.occupancy_status.value_counts(dropna=False)); print(df.availability_status.value_counts(dropna=False))
print(pd.crosstab(df.occupancy_status.fillna('NA'),df.availability_status.fillna('NA')))
print(df.unit_number.nunique())
print(df.unit_number.str[:4].value_counts().head(20))
print(df.head(10).to_string())
print(df.describe().to_string())
"

cd extracts; python3 -c "
import json,pandas as pd,re
d=json.load(open('juniper_rent_roll_latest.json'))
df=pd.DataFrame([r['rent_roll_latest'] for r in d['data']])
def pref(u):
  m=re.match(r'^([A-Z]-)?([A-Z]-)?(.*)$',u); return (m.group(1) or '')+(m.group(2) or '')
df['pref']=df.unit_number.map(pref)
df['num']=df.unit_number.str.replace(r'^([A-Z]-)+','',regex=True)
print(df.pref.value_counts())
print(df.groupby('pref').num.apply(lambda s:s.str.len().value_counts().to_dict()))
# overlaps
sets={p:set(g.num) for p,g in df.groupby('pref')}
for a in sets:
  for b in sets:
    if a<b: print(repr(a),repr(b),len(sets[a]&sets[b]))
print(pd.crosstab(df.pref,df.occupancy_status))
print(pd.crosstab(df.pref,df.availability_status))
print(df.groupby('pref').latest_rent_and_occupancy_as_of.max())
print(df.groupby('pref').leased_date.agg(['min','max','count']))
print(df.groupby('pref').listed_date.agg(['min','max','count']))
"

cd extracts; python3 -c "
import json,pandas as pd,re
d=json.load(open('juniper_rent_roll_latest.json'))
df=pd.DataFrame([r['rent_roll_latest'] for r in d['data']])
df['pref']=df.unit_number.str.extract(r'^((?:[A-Z]-)*)')[0]
df['num']=df.unit_number.str.replace(r'^([A-Z]-)+','',regex=True)
df['tower']=df.pref.str[:1].replace('', 'U')
c=['occupancy_status','availability_status','asking_rent_latest','in_place_rent_latest','listed_date','leased_date','num_bedrooms','unit_size_sqft']
a=df[df.pref.isin(['N-','S-'])].copy(); b=df[df.pref.isin(['N-N-','S-S-'])].copy()
m=a.merge(b,on=['tower','num'],suffixes=('_old','_new'))
print(len(m))
print(pd.crosstab(m.occupancy_status_old+'/'+m.availability_status_old, m.occupancy_status_new+'/'+m.availability_status_new))
print((m.num_bedrooms_old==m.num_bedrooms_new).mean(),(m.unit_size_sqft_old==m.unit_size_sqft_new).mean())
pd.set_option('display.width',300)
print(m[[x+s for x in c for s in ('_old','_new')]].head(25).to_string())
"

cp tool-outputs/juniper_rent_roll_ts_month.json tool-outputs/juniper_comp_candidates.json extracts/; cd extracts; python3 -c "
import json,pandas as pd
d=json.load(open('juniper_rent_roll_ts_month.json'))
df=pd.DataFrame([r['mf_rent_roll_ts'] for r in d['data']])
print(df.period_start_date.value_counts().sort_index())
"

cd extracts; python3 -c "
import json,pandas as pd,numpy as np
d=json.load(open('juniper_comp_candidates.json'))
rows=[]
for r in d['data']:
  s=r['mf_rent_and_occupancy_snapshot']; a=r['mf_property_attributes']
  rows.append(dict(id=r['id'],name=r['name'],addr=r['address'],zip=r['address_zip'],**{'s_'+k:v for k,v in s.items() if 'conf' not in k},**{'a_'+k:a[k] for k in ['latitude','longitude','building_style','num_stories','unit_count','year_built','household_restrictions','unit_size_sqft','improvements_rating']}))
df=pd.DataFrame(rows)
la,lo=33.783873617649164,-84.38146412372589
R=3958.8
p1,p2=np.radians(la),np.radians(df.a_latitude.astype(float))
dl=np.radians(df.a_longitude.astype(float)-lo)
df['dist']=R*np.arccos(np.clip(np.sin(p1)*np.sin(p2)+np.cos(p1)*np.cos(p2)*np.cos(dl),-1,1))
df=df[df.id!='dd7e2721f8b7515a41cc54cfc01e18e6']
print(df.a_building_style.value_counts())
df=df[df.dist<=5].sort_values('dist')
pd.set_option('display.width',300);pd.set_option('display.max_rows',200)
print(df[['name','dist','a_building_style','a_num_stories','a_unit_count','a_year_built','s_asking_rent_latest_avg','s_asking_rent_sqft_latest_avg','s_in_place_rent_latest_avg','s_in_place_rent_sqft_latest_avg','s_occupancy_latest','a_household_restrictions']].to_string())
df.to_csv('comp_candidates_flat.csv',index=False)
"

cd extracts; python3 -c "
import pandas as pd
df=pd.read_csv('comp_candidates_flat.csv')
x=df[(df.a_building_style=='HIGH_RISE')&(df.dist>2)]
pd.set_option('display.width',300)
print(x[['name','zip','dist','a_num_stories','a_unit_count','a_year_built','s_asking_rent_latest_avg','s_asking_rent_sqft_latest_avg','s_in_place_rent_latest_avg','s_in_place_rent_sqft_latest_avg','s_occupancy_latest']].to_string())
"

cd extracts; python3 -c "
import pandas as pd
df=pd.read_csv('comp_candidates_flat.csv')
names=['Modera Parkside','Piedmont House','Hanover Midtown','Sora at Spring Quarter','Society Atlanta','Emmi Midtown','Loria Ansley','The Hadley','Nomia','The Sutton']
x=df[df.name.isin(names)]
print(x[['id','name']].to_string()); print(list(x.id))
"

cd extracts; python3 -c "
import json,pandas as pd
d=json.load(open('juniper_rent_roll_ts_month.json'))
df=pd.DataFrame([r['mf_rent_roll_ts'] for r in d['data']])
df=df[df.unit_number.str.match(r'^[NS]-\d')]
g=df.sort_values('period_start_date').groupby('unit_number')
# units with constant leased_date but in-place change
res=[]
for u,x in g:
  x=x.dropna(subset=['in_place_rent_latest'])
  for ld,y in x.groupby('leased_date'):
    if y.in_place_rent_latest.nunique()>1: res.append((u,ld,list(zip(y.period_start_date.str[:7],y.in_place_rent_latest))))
print(len(res)); [print(r) for r in res[:15]]
"

"""Rent performance analysis for The Juniper & The Reserve at Juniper (1081 Juniper St NE, Atlanta).
Inputs (extracts/): juniper_rent_roll_latest.json, juniper_rent_roll_ts_month.json, juniper_comp_detail.json,
comp_candidates_flat.csv, ../subagents/comp-concessions/concessions.json
Outputs: extracts/out_*.csv and printed summary.
"""
import json, re, pandas as pd, numpy as np
pd.set_option('display.width', 250); pd.set_option('display.max_columns', 40)
E = 'extracts/'

def load_rr(path, key):
    d = json.load(open(E + path))
    df = pd.DataFrame([r[key] for r in d['data']])
    df['pref'] = df.unit_number.str.extract(r'^((?:[A-Z]-)*)')[0]
    df['num'] = df.unit_number.str.replace(r'^([A-Z]-)+', '', regex=True)
    df['tower'] = df.pref.str[:1].replace({'N': 'North (The Juniper)', 'S': 'South (The Reserve)', '': 'Untagged'})
    df['primary'] = df.pref.isin(['N-', 'S-', ''])
    for c in ['leased_date', 'listed_date', 'inferred_move_in_date']:
        df[c] = pd.to_datetime(df[c])
    return df

def dedupe(df, by=('tower', 'num')):
    # one record per physical unit: duplicate feed (N-N-/S-S-) mirrors N-/S-. Keep record with most recent
    # leased_date; ties -> primary feed.
    df = df.copy()
    df['_ld'] = df.leased_date.fillna(pd.Timestamp('1900-01-01'))
    df = df.sort_values(['_ld', 'primary'], ascending=[False, False])
    return df.drop_duplicates(list(by)).drop(columns='_ld')

BED = {0: 'Studio', 1: '1BR', 2: '2BR', 3: '3BR/PH'}
# ---------- latest rent roll ----------
rr = load_rr('juniper_rent_roll_latest.json', 'rent_roll_latest')
raw_n = len(rr)
u = dedupe(rr)
u['bed'] = u.num_bedrooms.map(BED)
print(f'Raw rent-roll records {raw_n}; deduped units {len(u)} (Datamart unit count 487)')
print(u.tower.value_counts())
occ = (u.occupancy_status == 'OCCUPIED')
onm = (u.availability_status == 'ON_MARKET')
vac = ~occ
print('Occupied', occ.sum(), 'Vacant', vac.sum(), 'On-market', onm.sum(), 'vacant&on-mkt', (vac & onm).sum(), 'occupied&on-mkt (notice/pre-lease)', (occ & onm).sum())
print('Physical occupancy (deduped tracked units):', round(occ.mean(), 4), '| vs 487 units:', round(occ.sum() / 487, 4))
leased = occ & ~onm | (vac & ~onm)
print('Leased (occupied off-market + vacant off-market i.e. pre-leased):', leased.sum(), round(leased.sum() / len(u), 4))
print('Occupied-on-notice (on-market):', (occ & onm).sum(), ' exposure (vacant+notice unleased):', onm.sum(), round(onm.sum() / len(u), 4))
# by tower occupancy
t = u.groupby('tower').apply(lambda x: pd.Series({'units': len(x), 'occupied': (x.occupancy_status == 'OCCUPIED').sum(),
                                                   'on_market': (x.availability_status == 'ON_MARKET').sum()}))
t['occ_pct'] = t.occupied / t.units; t['exposure_pct'] = t.on_market / t.units
print(t)
t.to_csv(E + 'out_tower_occupancy.csv')

# never-leased inventory: vacant on-market units whose leased_date is the 2025-03-08 placeholder (initial scrape) or null
never = vac & onm & (u.leased_date.isna() | (u.leased_date == pd.Timestamp('2025-03-08')))
print('Vacant on-market units with no observed lease since tracking began (never leased):', never.sum())
print(u[never].groupby(['tower', 'bed']).size())

# ---------- rents by unit type ----------
CONC = {'North (The Juniper)': 3 / 12, 'South (The Reserve)': 1 / 12}  # months free / 12-mo term
def summarize(x):
    a = x[x.availability_status == 'ON_MARKET']
    i = x[x.in_place_rent_latest.notna()]
    return pd.Series({'units': len(x), 'avg_sf': x.unit_size_sqft.mean(), 'occ_pct': (x.occupancy_status == 'OCCUPIED').mean(),
                      'n_ask': len(a), 'ask_avg': a.asking_rent_latest.mean(), 'ask_psf': a.asking_rent_latest.sum() / a.unit_size_sqft.sum() if len(a) else np.nan,
                      'n_inp': len(i), 'inp_avg': i.in_place_rent_latest.mean(), 'inp_psf': i.in_place_rent_latest.sum() / i.unit_size_sqft.sum() if len(i) else np.nan})
bt = u.groupby(['tower', 'bed']).apply(summarize)
bt['conc_pct'] = [CONC.get(tw, np.nan) for tw, _ in bt.index]
bt['eff_ask_avg'] = bt.ask_avg * (1 - bt.conc_pct)
bt['eff_ask_psf'] = bt.ask_psf * (1 - bt.conc_pct)
bt['gross_LTL_psf_pct'] = 1 - bt.inp_psf / bt.ask_psf       # +ve = in-place below face asking
bt['net_LTL_psf_pct'] = 1 - bt.inp_psf / bt.eff_ask_psf     # -ve = in-place above net-effective asking (gain-to-lease)
print(bt.round(3).to_string())
bt.to_csv(E + 'out_rents_by_tower_bed.csv')
# property-wide by bed (all towers)
pb = u.groupby('bed').apply(summarize)
print(pb.round(3).to_string()); pb.to_csv(E + 'out_rents_by_bed.csv')
tot = summarize(u); print(tot.round(3))
# blended concession-adjusted asking, weighted by on-market units per tower (untagged excluded)
a = u[onm & u.tower.isin(CONC)].copy()
a['eff'] = a.asking_rent_latest * (1 - a.tower.map(CONC))
print('On-market tagged units:', len(a), 'face avg', round(a.asking_rent_latest.mean()), 'net-eff avg', round(a.eff.mean()),
      'face psf', round(a.asking_rent_latest.sum() / a.unit_size_sqft.sum(), 2), 'net psf', round(a.eff.sum() / a.unit_size_sqft.sum(), 2),
      'blended concession %', round(1 - a.eff.sum() / a.asking_rent_latest.sum(), 4))
# concessions applied only to new leases: blended across leased roll if the T90 new-lease cohort got the concession
i = u[u.in_place_rent_latest.notna()].copy()
recent = i.leased_date >= pd.Timestamp('2026-06-28')
print('Leased units with rent:', len(i), ' signed in last 90d:', recent.sum(), ' share', round(recent.mean(), 3))
i['conc'] = i.tower.map(CONC).fillna(0)
i_rec = i[recent]
blend = (i_rec.in_place_rent_latest * i_rec.conc).sum() / i.in_place_rent_latest.sum()
print('If only T90 new leases carry current concession, rent-roll-wide drag:', round(blend, 4))
blend_all = (i.in_place_rent_latest * i.conc).sum() / i.in_place_rent_latest.sum()
print('If every lease on the roll carried current tower concession (upper bound):', round(blend_all, 4))

# ---------- lease trade-outs (new leases) from unit-level monthly history ----------
ts = load_rr('juniper_rent_roll_ts_month.json', 'mf_rent_roll_ts')
ts_all = pd.concat([ts, rr.assign(period_start_date='2026-09-20T00:00:00.000Z')], ignore_index=True)
ev = ts_all[ts_all.tradeout_new_lease_amt.notna()].copy()
ev = ev.sort_values('primary', ascending=False).drop_duplicates(['tower', 'num', 'leased_date'])
ev['bed'] = ev.num_bedrooms.map(BED)
ev['q'] = ev.leased_date.dt.to_period('Q')
print('New-lease trade-out events (deduped):', len(ev))
to = ev.groupby('q').apply(lambda x: pd.Series({'n': len(x), 'avg_amt': x.tradeout_new_lease_amt.mean(), 'avg_pct': x.tradeout_new_lease_pct.mean(),
                                               'wtd_pct': x.tradeout_new_lease_amt.sum() / x.previous_in_place_rent.sum(), 'share_neg': (x.tradeout_new_lease_amt < 0).mean()}))
print(to.round(4)); to.to_csv(E + 'out_tradeouts_by_q.csv')
tob = ev.groupby(['tower', 'bed']).apply(lambda x: pd.Series({'n': len(x), 'avg_amt': x.tradeout_new_lease_amt.mean(), 'wtd_pct': x.tradeout_new_lease_amt.sum() / x.previous_in_place_rent.sum()}))
print(tob.round(4)); tob.to_csv(E + 'out_tradeouts_by_tower_bed.csv')
allto = pd.Series({'n': len(ev), 'avg_amt': ev.tradeout_new_lease_amt.mean(), 'wtd_pct': ev.tradeout_new_lease_amt.sum() / ev.previous_in_place_rent.sum(),
                   'share_neg': (ev.tradeout_new_lease_amt < 0).mean()})
print('All new-lease trade-outs:', allto.round(4).to_dict())
l90 = ev[ev.leased_date >= pd.Timestamp('2026-06-28')]
print('T90 new-lease trade-outs:', len(l90), round(l90.tradeout_new_lease_amt.mean(), 1), round(l90.tradeout_new_lease_amt.sum() / l90.previous_in_place_rent.sum(), 4))

# ---------- renewals (observability) ----------
# first-generation leases from lease-up (signed Apr-Sep 2025) and their status now
first = dedupe(ts[ts.period_start_date.str.startswith('2025-09')])
first = first[(first.occupancy_status == 'OCCUPIED') & first.leased_date.between('2025-04-01', '2025-09-30')]
now = u.set_index(['tower', 'num'])
f = first.set_index(['tower', 'num'])
jj = f[['leased_date', 'in_place_rent_latest']].join(now[['leased_date', 'occupancy_status', 'availability_status', 'in_place_rent_latest']], rsuffix='_now', how='inner')
mature = jj[jj.leased_date <= pd.Timestamp('2025-09-26')]
same_lease = (mature.leased_date == mature.leased_date_now) & (mature.occupancy_status == 'OCCUPIED')
stay_offm = same_lease & (mature.availability_status == 'OFF_MARKET')
print('Lease-up cohort (leased Apr-Sep 2025, occupied Sep-25):', len(mature), ' still same resident & off-market now (renewed/holdover):', stay_offm.sum(),
      round(stay_offm.mean(), 3), ' turned over (new lease since):', ((mature.leased_date_now > mature.leased_date)).sum(),
      ' now vacant:', (mature.occupancy_status == 'VACANT').sum())

# ---------- occupancy trend, deduped ----------
rows = []
for p, x in ts_all.groupby('period_start_date'):
    x = dedupe(x)
    rows.append({'month': p[:10], 'tracked_units': len(x), 'occupied': (x.occupancy_status == 'OCCUPIED').sum(),
                 'on_market': (x.availability_status == 'ON_MARKET').sum()})
oc = pd.DataFrame(rows)
oc['occ_pct_tracked'] = oc.occupied / oc.tracked_units; oc['occ_pct_487'] = oc.occupied / 487
oc['raw_records'] = ts_all.groupby('period_start_date').size().values
print(oc.round(4).to_string()); oc.to_csv(E + 'out_occupancy_trend_deduped.csv', index=False)
# leases signed per month (deduped lease events)
le = ts_all.dropna(subset=['leased_date']).copy()
le = le[le.leased_date > pd.Timestamp('2025-03-08')]
le = le.sort_values('primary', ascending=False).drop_duplicates(['tower', 'num', 'leased_date'])
lm = le.groupby(le.leased_date.dt.to_period('M')).agg(n=('num', 'size'), dom_med=('days_on_market', 'median'))
print(lm.to_string()); lm.to_csv(E + 'out_leases_by_month_deduped.csv')

python3 scripts/juniper_rent_analysis.py 2>&1 | grep -v Warning

cd scripts && python3 - <<'EOF'
s=open('juniper_rent_analysis.py').read()
s=s.replace("""    for c in ['leased_date',""","""    for c in ['num_bedrooms','unit_size_sqft','asking_rent_latest','in_place_rent_latest','days_on_market','previous_in_place_rent','tradeout_new_lease_amt','tradeout_new_lease_pct']:
        df[c] = pd.to_numeric(df[c], errors='coerce')
    for c in ['leased_date',""")
s=s.replace("""print('Physical occupancy (deduped tracked units):'""","""tg = u[u.tower != 'Untagged']
print('TAGGED (N+S) units', len(tg), 'occupied', (tg.occupancy_status=='OCCUPIED').sum(), 'occ', round((tg.occupancy_status=='OCCUPIED').mean(),4),
      'on-market', (tg.availability_status=='ON_MARKET').sum(), 'vacant on-mkt', ((tg.occupancy_status=='VACANT')&(tg.availability_status=='ON_MARKET')).sum(),
      'vacant off-mkt (pre-leased)', ((tg.occupancy_status=='VACANT')&(tg.availability_status=='OFF_MARKET')).sum(),
      'occupied on-mkt (notice)', ((tg.occupancy_status=='OCCUPIED')&(tg.availability_status=='ON_MARKET')).sum())
ut = u[u.tower=='Untagged']; print('Untagged leased_date range', ut.leased_date.min(), ut.leased_date.max(), 'listed', ut.listed_date.min(), ut.listed_date.max())
print('Physical occupancy (deduped tracked units):'""")
open('juniper_rent_analysis.py','w').write(s)
EOF
cd .. && python3 scripts/juniper_rent_analysis.py 2>&1 | grep -v -i warning

"""Comp benchmark for The Juniper & The Reserve vs 10 luxury high-rise comps within 5 miles.
Inputs: extracts/juniper_comp_detail.json, extracts/comp_candidates_flat.csv, subagents/comp-concessions/concessions.json,
extracts/out_rents_by_bed.csv, extracts/out_tower_occupancy.csv (from juniper_rent_analysis.py)
"""
import json, pandas as pd, numpy as np
pd.set_option('display.width', 260); pd.set_option('display.max_columns', 40)
E = 'extracts/'
d = json.load(open(E + 'juniper_comp_detail.json'))
flat = pd.read_csv(E + 'comp_candidates_flat.csv').set_index('id')
conc = {c['name']: c for c in json.load(open('subagents/comp-concessions/concessions.json'))}
# lease term used to amortize free weeks (Loria requires 14+ mo leases; others 12 assumed)
TERM_WEEKS = {'Loria Ansley': 14 * 52 / 12}
rows = []
for r in d['data']:
    s = r['mf_rent_and_occupancy_detail']; f = lambda k: pd.to_numeric(s.get(k), errors='coerce')
    a = flat.loc[r['id']]
    c = conc.get(r['name'], {}); wk = c.get('weeks_free_equivalent') or 0
    cp = wk / TERM_WEEKS.get(r['name'], 52)
    rows.append({'name': r['name'], 'dist_mi': a.dist, 'year_built': a.a_year_built, 'units': a.a_unit_count, 'stories': a.a_num_stories,
                 'ask_avg': f('asking_rent_latest_avg'), 'ask_psf': f('asking_rent_sqft_latest_avg'),
                 'inp_avg': f('in_place_rent_latest_avg'), 'inp_psf': f('in_place_rent_sqft_latest_avg'),
                 'ask_0': f('asking_rent_latest_0_bed'), 'ask_1': f('asking_rent_latest_1_bed'), 'ask_2': f('asking_rent_latest_2_bed'),
                 'askpsf_0': f('asking_rent_sqft_latest_0_bed'), 'askpsf_1': f('asking_rent_sqft_latest_1_bed'), 'askpsf_2': f('asking_rent_sqft_latest_2_bed'),
                 'inp_0': f('in_place_rent_latest_0_bed'), 'inp_1': f('in_place_rent_latest_1_bed'), 'inp_2': f('in_place_rent_latest_2_bed'),
                 'occ': f('occupancy_latest'), 'occ_12mo_ago': f('occupancy_12mo_ago'), 'leases_30d': f('num_leases_signed_past_30d'),
                 'dom_med': f('days_on_market_leases_signed_past_30d_median'), 'tradeout_pct': f('tradeout_new_lease_pct'),
                 'tradeout_amt': f('tradeout_new_lease_amt'), 'retention': f('retention_rate'), 'ask_t12': f('asking_rent_t12_pct_chg_median'),
                 'conc_weeks_max': wk, 'conc_pct_max': cp, 'net_eff_ask_psf_max_conc': f('asking_rent_sqft_latest_avg') * (1 - cp)})
cp = pd.DataFrame(rows).sort_values('dist_mi')
cp.to_csv(E + 'out_comp_table.csv', index=False)
print(cp.round(3).to_string())
med = cp.drop(columns='name').median(numeric_only=True)
print('\nCOMP MEDIANS'); print(med.round(3).to_string())
# Subject (deduped) for comparison
sb = pd.read_csv(E + 'out_rents_by_bed.csv').set_index('bed')
print('\nSUBJECT by bed'); print(sb.round(3))
for b, k in [('Studio', '0'), ('1BR', '1'), ('2BR', '2')]:
    print(b, 'subject ask', round(sb.loc[b, 'ask_avg']), 'comp med ask', round(med['ask_' + k]), 'prem', round(sb.loc[b, 'ask_avg'] / med['ask_' + k] - 1, 3),
          '| subject in-place', round(sb.loc[b, 'inp_avg']), 'comp med inp', round(med['inp_' + k]), 'prem', round(sb.loc[b, 'inp_avg'] / med['inp_' + k] - 1, 3),
          '| subj ask psf', round(sb.loc[b, 'ask_psf'], 2), 'comp med ask psf', round(med['askpsf_' + k], 2))
print('Comp occupancy median', round(med.occ, 4), 'mean', round(cp.occ.mean(), 4), 'range', cp.occ.min(), cp.occ.max())
nb = cp[cp.year_built >= 2022]
print('New-build (2022+) comps n=', len(nb), 'occ median', round(nb.occ.median(), 4), 'ask psf median', round(nb.ask_psf.median(), 2),
      'in-place psf median', round(nb.inp_psf.median(), 2), 'net-eff psf median (max conc)', round(nb.net_eff_ask_psf_max_conc.median(), 2),
      'conc weeks median', nb.conc_weeks_max.median(), 'tradeout median', round(nb.tradeout_pct.median(), 4), 'dom median', nb.dom_med.median())
print('All comps: in-place psf median', round(cp.inp_psf.median(), 2), 'ask psf median', round(cp.ask_psf.median(), 2), 'net-eff psf median', round(cp.net_eff_ask_psf_max_conc.median(), 2),
      'tradeout median', round(cp.tradeout_pct.median(), 4), 'dom median', cp.dom_med.median(), 'retention median', round(cp.retention.median(), 3))
# subject premium psf vs comps
SUBJ = {'inp_psf': 3.834, 'net_eff_psf_tagged': 3.91, 'ask_psf_tagged': 4.52}
print('Subject in-place psf premium vs comp median', round(SUBJ['inp_psf'] / cp.inp_psf.median() - 1, 3),
      ' vs new-build median', round(SUBJ['inp_psf'] / nb.inp_psf.median() - 1, 3))
print('Subject net-eff ask psf premium vs comp net-eff median', round(SUBJ['net_eff_psf_tagged'] / cp.net_eff_ask_psf_max_conc.median() - 1, 3),
      ' vs new-build', round(SUBJ['net_eff_psf_tagged'] / nb.net_eff_ask_psf_max_conc.median() - 1, 3))
print('Rank of subject in-place psf among comps+subject:', int((cp.inp_psf > SUBJ['inp_psf']).sum()) + 1, 'of', len(cp) + 1)

python3 scripts/juniper_comp_benchmark.py 2>&1 | grep -v -i warning

cp tool-outputs/juniper_comp_detail.json extracts/ && python3 scripts/juniper_comp_benchmark.py 2>&1 | grep -v -i warning

"""Lease-up pace, vacancy loss and renewal-exposure math for The Juniper & The Reserve.
Inputs: extracts/out_occupancy_trend_deduped.csv, extracts/out_rents_by_tower_bed.csv, extracts/juniper_rent_roll_latest.json"""
import json, pandas as pd, numpy as np
E = 'extracts/'
oc = pd.read_csv(E + 'out_occupancy_trend_deduped.csv')
a, b = oc.iloc[1], oc.iloc[-1]   # Jul-2025 vs Sep-2026 (both 468 tracked units)
months = 14
net = b.occupied - a.occupied
print(f"Occupied units {a.month}: {a.occupied} ({a.occ_pct_tracked:.1%}) -> {b.month}: {b.occupied} ({b.occ_pct_tracked:.1%}); net +{net} in {months} mo = {net/months:.1f}/mo")
target = round(0.93 * b.tracked_units)
print(f"Units needed to reach 93% of {b.tracked_units}: {target - b.occupied}; months at trailing pace: {(target - b.occupied)/(net/months):.1f}")
# T3 pace (Jun->Sep 2026 is distorted by tracked-count drops; use May-26 468 -> Sep-26 468)
m = oc[oc.month == '2026-05-01'].iloc[0]
print(f"May-26 -> Sep-26 net: {b.occupied - m.occupied} in 4.7 mo = {(b.occupied - m.occupied)/4.7:.1f}/mo; months to 93% at that pace: {(target-b.occupied)/((b.occupied - m.occupied)/4.7):.1f}")
# vacancy loss on tagged towers at net-effective asking
bt = pd.read_csv(E + 'out_rents_by_tower_bed.csv')
d = json.load(open(E + 'juniper_rent_roll_latest.json'))
rr = pd.DataFrame([r['rent_roll_latest'] for r in d['data']])
for c in ['asking_rent_latest', 'num_bedrooms', 'unit_size_sqft']: rr[c] = pd.to_numeric(rr[c], errors='coerce')
rr['tower'] = rr.unit_number.str[:1]; rr['num'] = rr.unit_number.str.replace(r'^([A-Z]-)+', '', regex=True)
rr = rr[rr.tower.isin(['N', 'S']) & rr.unit_number.str.match(r'^[NS]-')]
v = rr.drop_duplicates(['tower', 'num'])
v = v[(v.occupancy_status == 'VACANT') & (v.availability_status == 'ON_MARKET')]
CONC = {'N': 0.25, 'S': 1/12}
v['net'] = v.asking_rent_latest * (1 - v.tower.map(CONC))
g = v.groupby('tower').agg(vacant_on_mkt=('num', 'size'), face_month=('asking_rent_latest', 'sum'), net_month=('net', 'sum'))
print(g); print('Total monthly vacancy loss at net-effective asking: $', round(g.net_month.sum()), ' annualized $', round(g.net_month.sum() * 12))
# South 2BR price gap vs comp net-effective
s2 = bt[(bt.tower == 'South (The Reserve)') & (bt.bed == '2BR')].iloc[0]
n1 = bt[(bt.tower == 'North (The Juniper)') & (bt.bed == '1BR')].iloc[0]
print('South 2BR: face psf', round(s2.ask_psf, 2), 'net psf', round(s2.eff_ask_psf, 2), 'in-place psf', round(s2.inp_psf, 2), 'avg sf', round(s2.avg_sf))
print('South 2BR net-eff avg $', round(s2.eff_ask_avg), '; at $3.85 psf would be $', round(3.85 * s2.avg_sf), ' (', round(3.85 / s2.eff_ask_psf - 1, 3), ')')
# North renewal exposure: in-place face vs net-effective new-lease asking
for b_ in ['Studio', '1BR', '2BR']:
    r = bt[(bt.tower == 'North (The Juniper)') & (bt.bed == b_)].iloc[0]
    print(f"North {b_}: in-place ${r.inp_avg:,.0f} ({r.inp_psf:.2f}/sf) vs net-eff new-lease ask ${r.eff_ask_avg:,.0f} ({r.eff_ask_psf:.2f}/sf); in-place above net by {r.inp_psf/r.eff_ask_psf-1:.1%}")

python3 scripts/juniper_leaseup_math.py

"""Builds Juniper rent performance workbook: deduped rent roll + formula-driven summary + comps.
Run from repo root after juniper_rent_analysis.py and juniper_comp_benchmark.py."""
import json, re, pandas as pd, numpy as np
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter as L
E = 'extracts/'
F = 'Arial'
HDR = PatternFill('solid', fgColor='1F3864'); HF = Font(name=F, bold=True, color='FFFFFF', size=10)
BODY = Font(name=F, size=10); BLUE = Font(name=F, size=10, color='0000FF'); BOLD = Font(name=F, size=10, bold=True)
TOP = Border(top=Side(style='thin'))

# ---- deduped rent roll (same rule as juniper_rent_analysis.dedupe) ----
d = json.load(open(E + 'juniper_rent_roll_latest.json'))
rr = pd.DataFrame([r['rent_roll_latest'] for r in d['data']])
for c in ['num_bedrooms', 'num_full_baths', 'unit_size_sqft', 'asking_rent_latest', 'in_place_rent_latest', 'days_on_market', 'previous_in_place_rent', 'tradeout_new_lease_amt', 'tradeout_new_lease_pct']:
    rr[c] = pd.to_numeric(rr[c], errors='coerce')
rr['pref'] = rr.unit_number.str.extract(r'^((?:[A-Z]-)*)')[0]
rr['num'] = rr.unit_number.str.replace(r'^([A-Z]-)+', '', regex=True)
rr['tower'] = rr.pref.str[:1].replace({'N': 'North', 'S': 'South', '': 'Untagged'})
rr['primary'] = rr.pref.isin(['N-', 'S-', ''])
for c in ['leased_date', 'listed_date', 'inferred_move_in_date']:
    rr[c] = pd.to_datetime(rr[c], utc=True).dt.tz_localize(None)
rr['_ld'] = rr.leased_date.fillna(pd.Timestamp('1900-01-01'))
u = rr.sort_values(['_ld', 'primary'], ascending=[False, False]).drop_duplicates(['tower', 'num'])
u['bed'] = u.num_bedrooms.map({0: 'Studio', 1: '1BR', 2: '2BR', 3: '3BR/PH'})
u = u.sort_values(['tower', 'num'])
dup = rr.groupby(['tower', 'num']).size()
u['feed_records'] = [dup[(t, n)] for t, n in zip(u.tower, u.num)]

wb = Workbook()
# ---------------- Assumptions ----------------
A = wb.active; A.title = 'Assumptions'
A['A1'] = 'The Juniper & The Reserve at Juniper - rent performance inputs'; A['A1'].font = Font(name=F, bold=True, size=12)
rows = [('Lease term for concession amortization (months)', 12, 'Assumed standard term; edit to test 13-15 mo'),
        ('North tower (The Juniper) concession - months free', 3, 'Website: "three months complimentary on select residences" (new leases)'),
        ('South tower (The Reserve) concession - months free', 1, 'Website: "one month free + complimentary monthly massage" (new leases)'),
        ('Total units (Datamart)', 487, 'property attributes unit_count'),
        ('Stabilized occupancy target', 0.93, 'Analyst assumption for months-to-stabilize')]
for i, (k, v, n) in enumerate(rows, start=3):
    A.cell(i, 1, k).font = BODY; c = A.cell(i, 2, v); c.font = BLUE; A.cell(i, 3, n).font = BODY
    c.number_format = '0.0%' if isinstance(v, float) else '0'
A.cell(8, 1, 'North concession % of face rent').font = BODY; A['B8'] = '=B4/B3'; A['B8'].number_format = '0.0%'; A['B8'].font = BODY
A.cell(9, 1, 'South concession % of face rent').font = BODY; A['B9'] = '=B5/B3'; A['B9'].number_format = '0.0%'; A['B9'].font = BODY
A.column_dimensions['A'].width = 52; A.column_dimensions['B'].width = 12; A.column_dimensions['C'].width = 70

# ---------------- Rent Roll ----------------
R = wb.create_sheet('Rent Roll')
cols = [('Tower', 'tower'), ('Unit', 'num'), ('Feed unit ID', 'unit_number'), ('Bed', 'bed'), ('Baths', 'num_full_baths'), ('Sq Ft', 'unit_size_sqft'),
        ('Occupancy', 'occupancy_status'), ('Availability', 'availability_status'), ('Asking Rent (monthly)', 'asking_rent_latest'),
        ('In-Place Rent (monthly)', 'in_place_rent_latest'), ('Listed Date', 'listed_date'), ('Leased Date', 'leased_date'),
        ('Days on Market', 'days_on_market'), ('Prior Rent (monthly)', 'previous_in_place_rent'), ('New-Lease Trade-out ($)', 'tradeout_new_lease_amt'),
        ('Move-in Date', 'inferred_move_in_date'), ('Feed Records', 'feed_records')]
for j, (h, _) in enumerate(cols, 1):
    c = R.cell(1, j, h); c.fill = HDR; c.font = HF; c.alignment = Alignment(wrap_text=True, vertical='center')
for i, rec in enumerate(u.itertuples(), start=2):
    for j, (_, k) in enumerate(cols, 1):
        v = getattr(rec, k)
        if isinstance(v, float) and np.isnan(v): v = None
        if isinstance(v, pd.Timestamp): v = None if pd.isna(v) else v.to_pydatetime()
        if v is pd.NaT: v = None
        c = R.cell(i, j, v); c.font = BODY
        if k in ('asking_rent_latest', 'in_place_rent_latest', 'previous_in_place_rent', 'tradeout_new_lease_amt'): c.number_format = '$#,##0;($#,##0);-'
        elif k in ('listed_date', 'leased_date', 'inferred_move_in_date'): c.number_format = 'yyyy-mm-dd'
        elif k in ('unit_size_sqft', 'days_on_market'): c.number_format = '#,##0'
    # derived per-row columns (formulas)
    R.cell(i, 18, f'=IF(I{i}="","",I{i}/F{i})').number_format = '$0.00'
    R.cell(i, 19, f'=IF(J{i}="","",J{i}/F{i})').number_format = '$0.00'
    R.cell(i, 20, f'=IF(I{i}="","",I{i}*(1-IF(A{i}="North",Assumptions!$B$8,IF(A{i}="South",Assumptions!$B$9,0))))').number_format = '$#,##0;($#,##0);-'
    R.cell(i, 21, f'=IF(OR(N{i}="",O{i}=""),"",O{i}/N{i})').number_format = '0.0%'
    for j in range(18, 22): R.cell(i, j).font = BODY
N = len(u) + 1
for j, h in enumerate(['Asking $/SF', 'In-Place $/SF', 'Net-Effective Asking (monthly)', 'Trade-out %'], 18):
    c = R.cell(1, j, h); c.fill = HDR; c.font = HF; c.alignment = Alignment(wrap_text=True, vertical='center')
widths = [10, 8, 12, 8, 7, 8, 11, 12, 12, 12, 11, 11, 9, 11, 12, 11, 9, 10, 10, 13, 10]
for j, w in enumerate(widths, 1): R.column_dimensions[L(j)].width = w
R.row_dimensions[1].height = 42; R.freeze_panes = 'C2'; R.auto_filter.ref = f'A1:U{N}'

# ---------------- Summary by tower & bed (formulas over Rent Roll) ----------------
S = wb.create_sheet('Summary')
S['A1'] = 'Rents, concessions and loss-to-lease by tower and unit type (deduped rent roll, week of 2026-09-20)'; S['A1'].font = Font(name=F, bold=True, size=12)
hd = ['Tower', 'Bed', 'Units', 'Occupied', 'Occupancy', 'On-Market Units', 'Avg Sq Ft', 'Avg Asking (face)', 'Asking $/SF (face)', 'Concession %',
      'Net-Effective Asking', 'Net-Effective $/SF', 'Leased Units w/ Rent', 'Avg In-Place', 'In-Place $/SF', 'Loss-to-Lease vs Face (psf)', 'Loss-to-Lease vs Net-Effective (psf)']
for j, h in enumerate(hd, 1):
    c = S.cell(3, j, h); c.fill = HDR; c.font = HF; c.alignment = Alignment(wrap_text=True, vertical='center')
S.row_dimensions[3].height = 45
RRng = lambda col: f"'Rent Roll'!${col}$2:${col}${N}"
combos = [('North', 'Studio'), ('North', '1BR'), ('North', '2BR'), ('South', '1BR'), ('South', '2BR'), ('South', '3BR/PH'),
          ('Untagged', 'Studio'), ('Untagged', '1BR'), ('Untagged', '2BR')]
r0 = 4
for k, (t, b) in enumerate(combos):
    r = r0 + k
    S.cell(r, 1, t); S.cell(r, 2, b)
    cr = f'{RRng("A")},$A{r},{RRng("D")},$B{r}'
    S.cell(r, 3, f'=COUNTIFS({cr})')
    S.cell(r, 4, f'=COUNTIFS({cr},{RRng("G")},"OCCUPIED")')
    S.cell(r, 5, f'=IF(C{r}=0,"",D{r}/C{r})')
    S.cell(r, 6, f'=COUNTIFS({cr},{RRng("H")},"ON_MARKET")')
    S.cell(r, 7, f'=AVERAGEIFS({RRng("F")},{cr})')
    S.cell(r, 8, f'=IFERROR(AVERAGEIFS({RRng("I")},{cr},{RRng("H")},"ON_MARKET"),"")')
    S.cell(r, 9, f'=IFERROR(SUMIFS({RRng("I")},{cr},{RRng("H")},"ON_MARKET")/SUMIFS({RRng("F")},{cr},{RRng("H")},"ON_MARKET",{RRng("I")},">0"),"")')
    S.cell(r, 10, f'=IF(A{r}="North",Assumptions!$B$8,IF(A{r}="South",Assumptions!$B$9,""))')
    S.cell(r, 11, f'=IF(OR(H{r}="",J{r}=""),"",H{r}*(1-J{r}))')
    S.cell(r, 12, f'=IF(OR(I{r}="",J{r}=""),"",I{r}*(1-J{r}))')
    S.cell(r, 13, f'=COUNTIFS({cr},{RRng("J")},">0")')
    S.cell(r, 14, f'=IFERROR(AVERAGEIFS({RRng("J")},{cr},{RRng("J")},">0"),"")')
    S.cell(r, 15, f'=IFERROR(SUMIFS({RRng("J")},{cr})/SUMIFS({RRng("F")},{cr},{RRng("J")},">0"),"")')
    S.cell(r, 16, f'=IF(OR(I{r}="",O{r}=""),"",1-O{r}/I{r})')
    S.cell(r, 17, f'=IF(OR(L{r}="",O{r}=""),"",1-O{r}/L{r})')
rt = r0 + len(combos)
S.cell(rt, 1, 'Total (all tracked)'); S.cell(rt, 2, '')
S.cell(rt, 3, f'=SUM(C{r0}:C{rt-1})'); S.cell(rt, 4, f'=SUM(D{r0}:D{rt-1})'); S.cell(rt, 5, f'=D{rt}/C{rt}'); S.cell(rt, 6, f'=SUM(F{r0}:F{rt-1})')
S.cell(rt, 7, f'=AVERAGE({RRng("F")})')
S.cell(rt, 8, f'=AVERAGEIFS({RRng("I")},{RRng("H")},"ON_MARKET")')
S.cell(rt, 9, f'=SUMIFS({RRng("I")},{RRng("H")},"ON_MARKET")/SUMIFS({RRng("F")},{RRng("H")},"ON_MARKET",{RRng("I")},">0")')
S.cell(rt, 13, f'=SUM(M{r0}:M{rt-1})'); S.cell(rt, 14, f'=AVERAGEIFS({RRng("J")},{RRng("J")},">0")')
S.cell(rt, 15, f'=SUM({RRng("J")})/SUMIFS({RRng("F")},{RRng("J")},">0")'); S.cell(rt, 16, f'=1-O{rt}/I{rt}')
# Tower-level occupancy & concession-weighted figures
S.cell(rt + 2, 1, 'Tower roll-up').font = BOLD
th = ['Tower', 'Units', 'Occupied', 'Occupancy', 'On-Market (exposure)', 'Exposure %', 'Vacant & On-Market', 'Face Asking (sum, monthly)', 'Net-Effective Asking (sum, monthly)']
for j, h in enumerate(th, 1):
    c = S.cell(rt + 3, j, h); c.fill = HDR; c.font = HF; c.alignment = Alignment(wrap_text=True, vertical='center')
S.row_dimensions[rt + 3].height = 45
for k, t in enumerate(['North', 'South', 'Untagged']):
    r = rt + 4 + k; S.cell(r, 1, t)
    S.cell(r, 2, f'=COUNTIFS({RRng("A")},$A{r})'); S.cell(r, 3, f'=COUNTIFS({RRng("A")},$A{r},{RRng("G")},"OCCUPIED")'); S.cell(r, 4, f'=C{r}/B{r}')
    S.cell(r, 5, f'=COUNTIFS({RRng("A")},$A{r},{RRng("H")},"ON_MARKET")'); S.cell(r, 6, f'=E{r}/B{r}')
    S.cell(r, 7, f'=COUNTIFS({RRng("A")},$A{r},{RRng("G")},"VACANT",{RRng("H")},"ON_MARKET")')
    S.cell(r, 8, f'=SUMIFS({RRng("I")},{RRng("A")},$A{r},{RRng("G")},"VACANT",{RRng("H")},"ON_MARKET")')
    S.cell(r, 9, f'=SUMIFS({RRng("T")},{RRng("A")},$A{r},{RRng("G")},"VACANT",{RRng("H")},"ON_MARKET")')
rT = rt + 7
S.cell(rT, 1, 'North + South')
for j, col in [(2, 'B'), (3, 'C'), (5, 'E'), (7, 'G'), (8, 'H'), (9, 'I')]: S.cell(rT, j, f'={col}{rt+4}+{col}{rt+5}')
S.cell(rT, 4, f'=C{rT}/B{rT}'); S.cell(rT, 6, f'=E{rT}/B{rT}')
S.cell(rT + 1, 1, 'Annualized vacancy loss at net-effective asking (vacant on-market, N+S)'); S.cell(rT + 1, 9, f'=I{rT}*12')
S.cell(rT + 2, 1, 'Note: Untagged = 51 records from the original listing feed with no tower prefix; asking rents date to the March 2025 pre-leasing scrape and are not reliable.')
# formats
pct_cols = {5, 10, 16, 17}; dol = {8, 11, 14}; psf = {9, 12, 15}
for r in range(r0, rt + 1):
    for j in range(1, 18):
        c = S.cell(r, j); c.font = BOLD if r == rt else BODY
        if r == rt: c.border = TOP
        if j in pct_cols: c.number_format = '0.0%;(0.0%);-'
        elif j in dol: c.number_format = '$#,##0;($#,##0);-'
        elif j in psf: c.number_format = '$0.00'
        elif j in (3, 4, 6, 7, 13): c.number_format = '#,##0'
for r in range(rt + 4, rT + 2):
    for j in range(1, 10):
        c = S.cell(r, j); c.font = BOLD if r >= rT else BODY
        if r == rT: c.border = TOP
        c.number_format = '0.0%' if j in (4, 6) else ('$#,##0' if j in (8, 9) else '#,##0')
S.cell(rT + 2, 1).font = Font(name=F, size=9, italic=True)
S.column_dimensions['A'].width = 20
for j in range(2, 18): S.column_dimensions[L(j)].width = 13

# ---------------- Trends (deduped, imported from analysis outputs) ----------------
T = wb.create_sheet('Occupancy Trend')
oc = pd.read_csv(E + 'out_occupancy_trend_deduped.csv'); lm = pd.read_csv(E + 'out_leases_by_month_deduped.csv')
for j, h in enumerate(['Month', 'Tracked Units (deduped)', 'Occupied Units', 'Physical Occupancy', 'Raw Feed Records'], 1):
    c = T.cell(1, j, h); c.fill = HDR; c.font = HF; c.alignment = Alignment(wrap_text=True)
for i, x in enumerate(oc.itertuples(), 2):
    T.cell(i, 1, x.month[:7]); T.cell(i, 2, int(x.tracked_units)); T.cell(i, 3, int(x.occupied)); T.cell(i, 4, f'=C{i}/B{i}'); T.cell(i, 5, int(x.raw_records))
    T.cell(i, 4).number_format = '0.0%'
    for j in range(1, 6): T.cell(i, j).font = BODY
for j, h in enumerate(['Lease Month', 'Leases Signed (deduped)', 'Median Days on Market'], 7):
    c = T.cell(1, j, h); c.fill = HDR; c.font = HF; c.alignment = Alignment(wrap_text=True)
for i, x in enumerate(lm.itertuples(), 2):
    T.cell(i, 7, str(x.leased_date)); T.cell(i, 8, int(x.n)); T.cell(i, 9, float(x.dom_med))
    for j in (7, 8, 9): T.cell(i, j).font = BODY
for j in range(1, 10): T.column_dimensions[L(j)].width = 14
T.row_dimensions[1].height = 32

# ---------------- Trade-outs ----------------
X = wb.create_sheet('Trade-outs')
to = pd.read_csv(E + 'out_tradeouts_by_q.csv'); tb = pd.read_csv(E + 'out_tradeouts_by_tower_bed.csv')
for j, h in enumerate(['Quarter Signed', 'New Leases w/ Prior Rent', 'Avg Trade-out ($)', 'Avg Trade-out (%)', 'Rent-Weighted Trade-out (%)', 'Share Negative'], 1):
    c = X.cell(1, j, h); c.fill = HDR; c.font = HF; c.alignment = Alignment(wrap_text=True)
for i, x in enumerate(to.itertuples(), 2):
    vals = [str(x.q), int(x.n), x.avg_amt, x.avg_pct, x.wtd_pct, x.share_neg]
    for j, v in enumerate(vals, 1):
        c = X.cell(i, j, v); c.font = BODY
        c.number_format = ['@', '0', '$#,##0;($#,##0);-', '0.0%;(0.0%);-', '0.0%;(0.0%);-', '0.0%'][j - 1]
r = len(to) + 3
X.cell(r, 1, 'Renewal trade-outs: not observable in listing-based data (renewal rent changes are not published). Request the PMS lease trade-out report.').font = Font(name=F, size=9, italic=True)
for j, h in enumerate(['Tower', 'Bed', 'New Leases w/ Prior Rent', 'Avg Trade-out ($)', 'Rent-Weighted Trade-out (%)'], 1):
    c = X.cell(r + 2, j, h); c.fill = HDR; c.font = HF; c.alignment = Alignment(wrap_text=True)
for i, x in enumerate(tb.itertuples(), r + 3):
    for j, v in enumerate([x.tower, x.bed, int(x.n), x.avg_amt, x.wtd_pct], 1):
        c = X.cell(i, j, v); c.font = BODY
        c.number_format = ['@', '@', '0', '$#,##0;($#,##0);-', '0.0%;(0.0%);-'][j - 1]
for j in range(1, 7): X.column_dimensions[L(j)].width = 18
X.column_dimensions['A'].width = 22

# ---------------- Comps ----------------
C = wb.create_sheet('Comps')
cp = pd.read_csv(E + 'out_comp_table.csv')
conc = {c['name']: c for c in json.load(open('subagents/comp-concessions/concessions.json'))}
ch = ['Property', 'Distance (mi)', 'Year Built', 'Units', 'Stories', 'Avg Asking (face)', 'Asking $/SF', 'Avg In-Place', 'In-Place $/SF',
      'Studio Asking', '1BR Asking', '2BR Asking', 'Studio In-Place', '1BR In-Place', '2BR In-Place', 'Occupancy', 'Occupancy 12 Mo Ago',
      'New-Lease Trade-out %', 'Median DOM (30d)', 'Advertised Max Weeks Free', 'Concession Term (mo)', 'Max Concession %', 'Net-Effective Asking $/SF (max conc.)', 'Concession Source']
for j, h in enumerate(ch, 1):
    c = C.cell(1, j, h); c.fill = HDR; c.font = HF; c.alignment = Alignment(wrap_text=True, vertical='center')
C.row_dimensions[1].height = 45
keys = ['name', 'dist_mi', 'year_built', 'units', 'stories', 'ask_avg', 'ask_psf', 'inp_avg', 'inp_psf', 'ask_0', 'ask_1', 'ask_2', 'inp_0', 'inp_1', 'inp_2', 'occ', 'occ_12mo_ago', 'tradeout_pct', 'dom_med', 'conc_weeks_max']
fm = ['@', '0.00', '0', '#,##0', '0', '$#,##0', '$0.00', '$#,##0', '$0.00', '$#,##0', '$#,##0', '$#,##0', '$#,##0', '$#,##0', '$#,##0', '0.0%', '0.0%', '0.0%;(0.0%);-', '0', '0.0']
for i, x in enumerate(cp.to_dict('records'), 2):
    for j, k in enumerate(keys, 1):
        v = x[k]; v = None if (isinstance(v, float) and np.isnan(v)) else v
        c = C.cell(i, j, v); c.font = BLUE if k == 'conc_weeks_max' else BODY; c.number_format = fm[j - 1]
    term = 14 if x['name'] == 'Loria Ansley' else 12
    c = C.cell(i, 21, term); c.font = BLUE
    C.cell(i, 22, f'=T{i}/(U{i}*52/12)').number_format = '0.0%'
    C.cell(i, 23, f'=G{i}*(1-V{i})').number_format = '$0.00'
    C.cell(i, 24, conc.get(x['name'], {}).get('source_url', ''))
    for j in (22, 23, 24): C.cell(i, j).font = BODY
n2 = len(cp) + 1; rm = n2 + 1
C.cell(rm, 1, 'Comp median').font = BOLD
for j in list(range(2, 24)):
    if j in (21,): continue
    col = L(j); c = C.cell(rm, j, f'=MEDIAN({col}2:{col}{n2})'); c.font = BOLD; c.border = TOP; c.number_format = fm[j - 1] if j <= 20 else ('0.0%' if j == 22 else '$0.00')
rs = rm + 1
C.cell(rs, 1, 'Subject (deduped, N+S+untagged)').font = BOLD
C.cell(rs, 6, "=Summary!H13"); C.cell(rs, 7, "=Summary!I13"); C.cell(rs, 8, "=Summary!N13"); C.cell(rs, 9, "=Summary!O13")
C.cell(rs, 10, "=AVERAGEIFS('Rent Roll'!$I$2:$I$%d,'Rent Roll'!$D$2:$D$%d,\"Studio\",'Rent Roll'!$H$2:$H$%d,\"ON_MARKET\")" % (N, N, N))
C.cell(rs, 11, "=AVERAGEIFS('Rent Roll'!$I$2:$I$%d,'Rent Roll'!$D$2:$D$%d,\"1BR\",'Rent Roll'!$H$2:$H$%d,\"ON_MARKET\")" % (N, N, N))
C.cell(rs, 12, "=AVERAGEIFS('Rent Roll'!$I$2:$I$%d,'Rent Roll'!$D$2:$D$%d,\"2BR\",'Rent Roll'!$H$2:$H$%d,\"ON_MARKET\")" % (N, N, N))
C.cell(rs, 13, "=AVERAGEIFS('Rent Roll'!$J$2:$J$%d,'Rent Roll'!$D$2:$D$%d,\"Studio\",'Rent Roll'!$J$2:$J$%d,\">0\")" % (N, N, N))
C.cell(rs, 14, "=AVERAGEIFS('Rent Roll'!$J$2:$J$%d,'Rent Roll'!$D$2:$D$%d,\"1BR\",'Rent Roll'!$J$2:$J$%d,\">0\")" % (N, N, N))
C.cell(rs, 15, "=AVERAGEIFS('Rent Roll'!$J$2:$J$%d,'Rent Roll'!$D$2:$D$%d,\"2BR\",'Rent Roll'!$J$2:$J$%d,\">0\")" % (N, N, N))
C.cell(rs, 16, "=Summary!E13")
C.cell(rs, 23, "=Summary!I23/SUMIFS('Rent Roll'!$F$2:$F$%d,'Rent Roll'!$A$2:$A$%d,\"<>Untagged\",'Rent Roll'!$G$2:$G$%d,\"VACANT\",'Rent Roll'!$H$2:$H$%d,\"ON_MARKET\")" % (N, N, N, N))
fmts = {6: '$#,##0', 7: '$0.00', 8: '$#,##0', 9: '$0.00', 10: '$#,##0', 11: '$#,##0', 12: '$#,##0', 13: '$#,##0', 14: '$#,##0', 15: '$#,##0', 16: '0.0%', 23: '$0.00'}
for j, f_ in fmts.items():
    c = C.cell(rs, j); c.number_format = f_; c.font = Font(name=F, size=10, bold=True, color='008000')
rp = rs + 1
C.cell(rp, 1, 'Subject premium vs comp median').font = BOLD
for j in (6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 23):
    col = L(j); c = C.cell(rp, j, f'={col}{rs}/{col}{rm}-1'); c.number_format = '0.0%;(0.0%);-'; c.font = BOLD
C.cell(rp, 16, f'=P{rs}-P{rm}').number_format = '0.0%;(0.0%);-'; C.cell(rp, 16).font = BOLD
C.cell(rp + 2, 1, 'Concessions are advertised "up to" maxima from official sites/ILS, early Oct 2026; net-effective comp figures are therefore floors. Subject net-effective $/SF covers vacant on-market N+S units at the website concession.').font = Font(name=F, size=9, italic=True)
C.column_dimensions['A'].width = 24
for j in range(2, 24): C.column_dimensions[L(j)].width = 11
C.column_dimensions['X'].width = 50
C.freeze_panes = 'B2'
wb.save('output/Juniper_Rent_Performance_Review.xlsx')
print('rows', N, 'summary total row', rt, 'tower NS row', rT)

mkdir -p output && sed -i 's/=Summary!I23\//=Summary!I20\//' scripts/build_juniper_workbook.py && python3 scripts/build_juniper_workbook.py && python skills/xlsx/scripts/workbook_integrity_scan.py output/Juniper_Rent_Performance_Review.xlsx --json scratch/integ.json; echo EXIT $?; python3 -c "
import json;d=json.load(open('scratch/integ.json'));
fs=d.get('findings',d)
from collections import Counter
print(Counter((f['severity'],f['category']) for f in fs))
for f in fs:
  if f['severity']=='high': print(f)
" | head -30

cd scripts && python3 - <<'EOF'
s=open('build_juniper_workbook.py').read()
s=s.replace("'Rent Roll'!","RentRoll!").replace("create_sheet('Rent Roll')","create_sheet('RentRoll')").replace('''f"'Rent Roll'!${col}$2:${col}${N}"''','''f"RentRoll!${col}$2:${col}${N}"''')
s=s.replace("R.freeze_panes = 'C2'","R.freeze_panes = 'B2'")
s=s.replace("""f'=IF(I{i}="","",I{i}/F{i})'""","""f'=IF($I{i}="","",$I{i}/$F{i})'""")
s=s.replace("""f'=IF(J{i}="","",J{i}/F{i})'""","""f'=IF($J{i}="","",$J{i}/$F{i})'""")
s=s.replace("""f'=IF(I{i}="","",I{i}*(1-IF(A{i}="North",Assumptions!$B$8,IF(A{i}="South",Assumptions!$B$9,0))))'""","""f'=IF($I{i}="","",$I{i}*(1-IF($A{i}="North",Assumptions!$B$8,IF($A{i}="South",Assumptions!$B$9,0))))'""")
s=s.replace("""f'=IF(OR(N{i}="",O{i}=""),"",O{i}/N{i})'""","""f'=IF(OR($N{i}="",$O{i}=""),"",$O{i}/$N{i})'""")
s=s.replace("S.cell(rt, 1, 'Total (all tracked)')","S.cell(rt, 1, 'Subject total (all tracked)')")
s=s.replace("C.cell(rs, 1, 'Subject (deduped, N+S+untagged)')","C.cell(rs, 1, 'Subject total (all tracked)')")
# trade-out section: replace with event sheet + formulas
start=s.index("# ---------------- Trade-outs ----------------"); end=s.index("# ---------------- Comps ----------------")
new='''# ---------------- Trade-outs (event-level + formula summaries) ----------------
ev = pd.read_csv(E + 'out_tradeout_events.csv', parse_dates=['leased_date'])
V = wb.create_sheet('TradeoutEvents')
eh = ['Tower', 'Unit', 'Bed', 'Leased Date', 'Quarter', 'Prior Rent (monthly)', 'New-Lease Rent (monthly)', 'Trade-out ($)', 'Trade-out (%)']
for j, h in enumerate(eh, 1):
    c = V.cell(1, j, h); c.fill = HDR; c.font = HF; c.alignment = Alignment(wrap_text=True)
for i, x in enumerate(ev.itertuples(), 2):
    vals = [x.tower, x.num, x.bed, x.leased_date.to_pydatetime(), x.q, x.previous_in_place_rent, x.new_rent]
    for j, v in enumerate(vals, 1):
        c = V.cell(i, j, v); c.font = BODY
    V.cell(i, 4).number_format = 'yyyy-mm-dd'; V.cell(i, 6).number_format = '$#,##0'; V.cell(i, 7).number_format = '$#,##0'
    V.cell(i, 8, f'=$G{i}-$F{i}').number_format = '$#,##0;($#,##0);-'; V.cell(i, 9, f'=$H{i}/$F{i}').number_format = '0.0%;(0.0%);-'
    V.cell(i, 8).font = BODY; V.cell(i, 9).font = BODY
NE = len(ev) + 1
for j in range(1, 10): V.column_dimensions[L(j)].width = 13
V.row_dimensions[1].height = 32
X = wb.create_sheet('Trade-outs')
X['A1'] = 'New-lease trade-outs (new resident vs prior resident, same unit); renewals not observable in listing data'; X['A1'].font = Font(name=F, bold=True, size=12)
ER = lambda col: f"TradeoutEvents!${col}$2:${col}${NE}"
for j, h in enumerate(['Quarter Signed', 'New Leases w/ Prior Rent', 'Avg Trade-out ($)', 'Rent-Weighted Trade-out (%)', 'Share Negative'], 1):
    c = X.cell(3, j, h); c.fill = HDR; c.font = HF; c.alignment = Alignment(wrap_text=True)
qs = sorted(ev.q.unique())
for k, q in enumerate(qs):
    r = 4 + k; X.cell(r, 1, q)
    X.cell(r, 2, f'=COUNTIFS({ER("E")},$A{r})')
    X.cell(r, 3, f'=AVERAGEIFS({ER("H")},{ER("E")},$A{r})')
    X.cell(r, 4, f'=SUMIFS({ER("H")},{ER("E")},$A{r})/SUMIFS({ER("F")},{ER("E")},$A{r})')
    X.cell(r, 5, f'=COUNTIFS({ER("E")},$A{r},{ER("H")},"<0")/B{r}')
rq = 4 + len(qs)
X.cell(rq, 1, 'All new leases'); X.cell(rq, 2, f'=SUM(B4:B{rq-1})'); X.cell(rq, 3, f'=AVERAGE({ER("H")})')
X.cell(rq, 4, f'=SUM({ER("H")})/SUM({ER("F")})'); X.cell(rq, 5, f'=COUNTIFS({ER("H")},"<0")/B{rq}')
for r in range(4, rq + 1):
    for j, f_ in enumerate(['@', '0', '$#,##0;($#,##0);-', '0.0%;(0.0%);-', '0.0%'], 1):
        c = X.cell(r, j); c.number_format = f_; c.font = BOLD if r == rq else BODY
        if r == rq: c.border = TOP
rb = rq + 2
for j, h in enumerate(['Tower', 'Bed', 'New Leases w/ Prior Rent', 'Avg Trade-out ($)', 'Rent-Weighted Trade-out (%)'], 1):
    c = X.cell(rb, j, h); c.fill = HDR; c.font = HF; c.alignment = Alignment(wrap_text=True)
combos2 = ev.groupby(['tower', 'bed']).size().index.tolist()
for k, (t, b) in enumerate(combos2):
    r = rb + 1 + k; X.cell(r, 1, t); X.cell(r, 2, b)
    cr = f'{ER("A")},$A{r},{ER("C")},$B{r}'
    X.cell(r, 3, f'=COUNTIFS({cr})'); X.cell(r, 4, f'=AVERAGEIFS({ER("H")},{cr})'); X.cell(r, 5, f'=SUMIFS({ER("H")},{cr})/SUMIFS({ER("F")},{cr})')
    for j, f_ in enumerate(['@', '@', '0', '$#,##0;($#,##0);-', '0.0%;(0.0%);-'], 1):
        X.cell(r, j).number_format = f_; X.cell(r, j).font = BODY
X.cell(rb + len(combos2) + 2, 1, 'Renewal trade-outs: renewal rent changes are not published to listing feeds; request the PMS lease trade-out report.').font = Font(name=F, size=9, italic=True)
for j in range(1, 6): X.column_dimensions[L(j)].width = 18
X.column_dimensions['A'].width = 22
'''
s=s[:start]+new+s[end:]
open('build_juniper_workbook.py','w').write(s)
# analysis: export trade-out events
a=open('juniper_rent_analysis.py').read()
a=a.replace("print('New-lease trade-out events (deduped):', len(ev))","""print('New-lease trade-out events (deduped):', len(ev))
evx = ev.assign(tower=ev.tower.str.split(' ').str[0], new_rent=ev.previous_in_place_rent + ev.tradeout_new_lease_amt, q=ev.q.astype(str))
evx[['tower', 'num', 'bed', 'leased_date', 'q', 'previous_in_place_rent', 'new_rent', 'tradeout_new_lease_amt']].sort_values('leased_date').to_csv(E + 'out_tradeout_events.csv', index=False)""")
open('juniper_rent_analysis.py','w').write(a)
EOF
cd .. && python3 scripts/juniper_rent_analysis.py > /dev/null 2>&1; head -3 extracts/out_tradeout_events.csv; python3 scripts/build_juniper_workbook.py && python skills/xlsx/scripts/workbook_integrity_scan.py output/Juniper_Rent_Performance_Review.xlsx --json scratch/integ.json >/dev/null; echo EXIT $?; python3 -c "
import json;d=json.load(open('scratch/integ.json'))
from collections import Counter
print(Counter((f['severity'],f['category']) for f in d['findings']))
for f in d['findings']:
  if f['severity']=='high': print(f)
"

cd scripts && python3 - <<'EOF'
import re
s=open('build_juniper_workbook.py').read()
s=s.replace('''f"RentRoll!${col}$2:${col}${N}"''','''f"RentRoll!${col}:${col}"''')
s=s.replace('''f"TradeoutEvents!${col}$2:${col}${NE}"''','''f"TradeoutEvents!${col}:${col}"''')
# literal ranges in Comps subject row using % (N,N,..)
s=re.sub(r"RentRoll!\$([A-Z])\$2:\$\1\$%d", r"RentRoll!$\1:$\1", s)
s=s.replace('% (N, N, N, N))','% ())').replace('% (N, N, N))','% ())')
open('build_juniper_workbook.py','w').write(s)
EOF
grep -n "% ()" scripts/build_juniper_workbook.py | head -3; cd /vercel/sandbox && python3 scripts/build_juniper_workbook.py && python skills/xlsx/scripts/workbook_integrity_scan.py output/Juniper_Rent_Performance_Review.xlsx --json scratch/integ.json >/dev/null; echo EXIT $?; python3 -c "
import json;d=json.load(open('scratch/integ.json'))
from collections import Counter
print(Counter((f['severity'],f['category']) for f in d['findings']))
for f in d['findings']:
  if f['severity']=='high': print(f)
"

python skills/xlsx/scripts/recalc.py output/Juniper_Rent_Performance_Review.xlsx | tail -5; python3 -c "
from openpyxl import load_workbook
wb=load_workbook('output/Juniper_Rent_Performance_Review.xlsx',data_only=True)
for sh,rng in [('Summary','A3:Q21'),('Trade-outs','A3:E16'),('Comps','A12:W14'),('Assumptions','A8:B9')]:
  ws=wb[sh]; print('==',sh)
  for row in ws[rng]:
    print([ (c.coordinate, round(c.value,3) if isinstance(c.value,float) else c.value) for c in row if c.value is not None])
"

sed -i 's/C.cell(rs, 16, "=Summary!E13")/C.cell(rs, 16, "=Summary!D20")/' scripts/build_juniper_workbook.py && sed -i "s/Subject net-effective \$\/SF covers vacant on-market N+S units at the website concession./Subject net-effective \$\/SF covers vacant on-market N+S units at the website concession; subject occupancy is N+S towers only (Summary!D20) because the 51 untagged records are stale./" scripts/build_juniper_workbook.py && python3 scripts/build_juniper_workbook.py && python skills/xlsx/scripts/workbook_integrity_scan.py output/Juniper_Rent_Performance_Review.xlsx --json scratch/integ.json >/dev/null; echo EXIT $? && python skills/xlsx/scripts/recalc.py output/Juniper_Rent_Performance_Review.xlsx | grep -E "total_errors|status" && python3 -c "
from openpyxl import load_workbook
ws=load_workbook('output/Juniper_Rent_Performance_Review.xlsx',data_only=True)['Comps']; print(ws['P13'].value, ws['P14'].value, ws['A16'].value)"