property_tax_rate_analysis

E. Quinn 1/1/2022

This notebook analyzes data from the RI Division of Municipal Finance and Rhode Island Department of Education websites

Import standard python datascience packages

import sys
import math
import re
import copy
import numpy as np
import scipy as sc
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import pickle
%matplotlib inline
from pathlib import Path
import os

from datetime import datetime, timedelta, date
from datascience import *
import uuid
import random

Show the directory we are running in

!pwd

/home/gquinn/EG/tax_rate_studies/notebooks

pd.set_option("display.max_rows",1000)
pd.set_option("display.max_columns",100)
pd.get_option("display.max_rows")
pd.get_option("display.max_columns")

100

Read assessed values

p = Path('.')
txt_folder = p.rglob('../division_of_municipal_finance/assessed_value_by_town/Statewide-Net-Assessed-Value-FY*.csv')
files = [x for x in txt_folder]

assessed_value = None

for file in files:
    fpath = file.as_posix()
    df = pd.read_csv(fpath)
    df['Municipality'] = df['Municipality'].str.upper().str.strip()
    df.rename(columns = {'Total':'Total Assessed'}, inplace = True)
    if assessed_value is None:
        assessed_value = df
    else:
        frames = [assessed_value, df]
        assessed_value = pd.concat(frames, sort=False)

print(assessed_value.shape)
assessed_value.head(39)
assessed_value.sort_values(['fyear','Municipality'],ascending=[False,True]).head(39)

(468, 7)

	Municipality	Residential	Industrial	Tangible	Vehicle	Total Assessed	fyear
0	BARRINGTON	3200189345	150387019	45980777	87258115	3483815256	2022
1	BRISTOL	2787240049	296033364	50048887	91665937	3224988237	2022
2	BURRILLVILLE	1379931278	282880476	180839889	72441728	1916093371	2022
3	CENTRAL FALLS	442255997	86157836	23887539	21953761	574255133	2022
4	CHARLESTOWN	2694376966	84851535	21811256	46754658	2847794415	2022
5	COVENTRY	3270176297	481136320	99449120	171001393	4021763130	2022
6	CRANSTON	6866409914	1649757156	367758127	293311890	9177237087	2022
7	CUMBERLAND	3518317657	634418199	203717338	181457461	4537910655	2022
8	EAST GREENWICH	2201170064	390591860	91916642	80564563	2764243129	2022
9	EAST PROVIDENCE	2792844817	1179499750	288728740	194804957	4455878264	2022
10	EXETER	870934255	81371150	25730960	40340211	1018376576	2022
11	FOSTER	578830988	44348300	10475354	24514943	658169585	2022
12	GLOCESTER	1035186494	64322906	21348829	54847060	1175705289	2022
13	HOPKINTON	883892640	100927741	48281770	40939086	1074041237	2022
14	JAMESTOWN	2547705324	79106146	14955997	34750812	2676518279	2022
15	JOHNSTON	1892160236	563727427	193512074	147376137	2796775874	2022
16	LINCOLN	1563030120	664419139	187194015	121641256	2536284530	2022
17	LITTLE COMPTON	2161884261	42555500	11244248	20187370	2235871379	2022
18	MIDDLETOWN	2757975650	719597500	100218397	79640236	3657431783	2022
19	NARRAGANSETT	5536820247	367807312	109982141	76850067	6091459767	2022
20	NEW SHOREHAM	1512219063	161964692	15288959	8271583	1697744297	2022
21	NEWPORT	6253671908	1409477896	152220873	74501090	7889871767	2022
22	NORTH KINGSTOWN	3660590770	805344286	183538760	180559202	4830033018	2022
23	NORTH PROVIDENCE	1996593720	506759429	91286433	109182239	2703821821	2022
24	NORTH SMITHFIELD	1284610669	335214762	147397137	67072254	1834294822	2022
25	PAWTUCKET	4013154906	949048713	153718310	164737056	5280658985	2022
26	PORTSMOUTH	3341309327	303880755	127859056	67166377	3840215515	2022
27	PROVIDENCE	6695723334	3469276368	1117783000	307901312	11590684014	2022
28	RICHMOND	850001231	96033340	26235273	35957056	1008226900	2022
29	SCITUATE	1251853517	353921441	26970497	54799296	1687544751	2022
30	SMITHFIELD	2076774871	719365166	148073299	117981343	3062194679	2022
31	SOUTH KINGSTOWN	4464036880	554765455	107007815	136900889	5262711039	2022
32	TIVERTON	2382716127	295266002	70126333	70281877	2818390339	2022
33	WARREN	1136266337	200000982	37340751	43706299	1417314369	2022
34	WARWICK	7247275009	2391529993	572025589	445330825	10656161416	2022
35	WEST GREENWICH	596066133	222585643	51343215	42003177	911998168	2022
36	WEST WARWICK	1759612795	505959933	159047620	107997356	2532617704	2022
37	WESTERLY	5643209917	677003900	133872207	114923126	6569009150	2022
38	WOONSOCKET	1130855404	547028703	123239200	89200938	1890324245	2022

Read levy values

p = Path('.')
txt_folder = p.rglob('../division_of_municipal_finance/levy_by_town/Municipal_Finance_Levy_20*.csv')
files = [x for x in txt_folder]

levy = None

for file in files:
    fpath = file.as_posix()
    fyear = int(fpath[69:73])
    
    df = pd.read_csv(fpath)
    df['fyear'] = fyear
    df['population'] = round(df['Municipal Total']/df['Levy Per Capita'])
    df.rename(columns = {'Town':'Municipality','Commercial/Industrial':'Industrial',
                        'Motor Vehicles':'Vehicle','Municipal Total':'Total Levy'}, inplace = True)
    df['Municipality'] = df['Municipality'].str.upper().str.strip()
    if levy is None:
        levy = df
    else:
        frames = [levy, df]
        levy = pd.concat(frames, sort=False)

print(levy.shape)
levy.sort_values(['fyear','Municipality'],ascending=[False,True]).head(39)

(468, 9)

	Municipality	Residential	Industrial	Tangible	Vehicle	Total Levy	Levy Per Capita	fyear	population
0	BARRINGTON	61283626	2879911	880485	2617376	67661399	4194	2022	16133.0
1	BRISTOL	40080512	4256960	719703	1590404	46647579	2106	2022	22150.0
2	BURRILLVILLE	22658732	4644916	2969593	2172854	32446095	1956	2022	16588.0
3	CENTRAL FALLS	9607275	3647064	1349032	658127	15261498	786	2022	19417.0
4	CHARLESTOWN	22040073	694086	178465	611166	23523789	3016	2022	7800.0
5	COVENTRY	61080079	11254260	1929644	3204895	77468878	2237	2022	34631.0
6	CRANSTON	123594743	44543443	9929469	8799357	186867012	2300	2022	81247.0
7	CUMBERLAND	51860429	9351324	5775169	3432140	70419063	2021	2022	34844.0
8	EAST GREENWICH	46246583	9081261	2778640	1843317	59949801	4583	2022	13081.0
9	EAST PROVIDENCE	60046147	31197772	15998448	6816698	114059066	2402	2022	47485.0
10	EXETER	11949348	1116286	353056	1210210	14628901	2195	2022	6665.0
11	FOSTER	11658088	940878	307556	735331	13641853	2895	2022	4712.0
12	GLOCESTER	19102206	1423466	787420	1336378	22649470	2233	2022	10143.0
13	HOPKINTON	16378516	1870191	894600	866813	20010120	2471	2022	8098.0
14	JAMESTOWN	21095000	654999	123945	500714	22374657	4073	2022	5493.0
15	JOHNSTON	43973823	15976035	12450579	4420417	76820854	2621	2022	29310.0
16	LINCOLN	31736099	16750031	5992081	3648669	58126879	2675	2022	21730.0
17	LITTLE COMPTON	12800734	257035	141651	284780	13484200	3865	2022	3489.0
18	MIDDLETOWN	33150865	12398665	1726760	1277791	48554082	3031	2022	16019.0
19	NARRAGANSETT	49056227	4398974	1315386	1264928	56035516	3615	2022	15501.0
20	NEW SHOREHAM	10131869	1085163	102520	80599	11400152	12446	2022	916.0
21	NEWPORT	58346765	19718596	2129751	1746476	81941588	3322	2022	24666.0
22	NORTH KINGSTOWN	64060339	14093525	3419262	3470760	85043886	3242	2022	26232.0
23	NORTH PROVIDENCE	45542307	14974743	5913535	3274461	69705046	2141	2022	32557.0
24	NORTH SMITHFIELD	20981050	6516910	6439852	2011093	35948906	2896	2022	12413.0
25	PAWTUCKET	66538110	27541394	8007187	4933972	107020663	1490	2022	71826.0
26	PORTSMOUTH	51138749	4650895	1957129	1511133	59257906	3413	2022	17362.0
27	PROVIDENCE	167013329	127322520	62372321	9258888	365967057	2039	2022	179484.0
28	RICHMOND	17527028	1980207	540986	813781	20862003	2726	2022	7653.0
29	SCITUATE	21843937	8207438	1073700	1643803	32768878	3082	2022	10632.0
30	SMITHFIELD	34208110	13452127	8845889	3538967	60045093	2768	2022	21693.0
31	SOUTH KINGSTOWN	64505314	8016362	1546259	2560570	76628505	2500	2022	30651.0
32	TIVERTON	34001362	4213446	1000703	1345168	40560680	2571	2022	15776.0
33	WARREN	20134631	3544017	661634	1136094	25476376	2429	2022	10488.0
34	WARWICK	135741461	67201993	21428079	13359925	237731457	2935	2022	80999.0
35	WEST GREENWICH	12961156	5426647	1751849	798853	20938506	3364	2022	6224.0
36	WEST WARWICK	41815001	16775720	7271969	3074686	68937375	2382	2022	28941.0
37	WESTERLY	65009851	7799085	1574260	3210367	77593563	3442	2022	22543.0
38	WOONSOCKET	26857836	19009252	5740485	2674739	54282311	1305	2022	41596.0

Combine assessed values and levy

rimf1 = pd.merge(assessed_value,levy, on=['Municipality','fyear'], how='inner',suffixes=('_av','_lv'))
print(rimf1.columns.to_numpy())
rimf1.sort_values(['fyear','Municipality'],ascending=[False,True]).head(39)

['Municipality' 'Residential_av' 'Industrial_av' 'Tangible_av'
 'Vehicle_av' 'Total Assessed' 'fyear' 'Residential_lv' 'Industrial_lv'
 'Tangible_lv' 'Vehicle_lv' 'Total Levy' 'Levy Per Capita' 'population']

	Municipality	Residential_av	Industrial_av	Tangible_av	Vehicle_av	Total Assessed	fyear	Residential_lv	Industrial_lv	Tangible_lv	Vehicle_lv	Total Levy	Levy Per Capita	population
429	BARRINGTON	3200189345	150387019	45980777	87258115	3483815256	2022	61283626	2879911	880485	2617376	67661399	4194	16133.0
430	BRISTOL	2787240049	296033364	50048887	91665937	3224988237	2022	40080512	4256960	719703	1590404	46647579	2106	22150.0
431	BURRILLVILLE	1379931278	282880476	180839889	72441728	1916093371	2022	22658732	4644916	2969593	2172854	32446095	1956	16588.0
432	CENTRAL FALLS	442255997	86157836	23887539	21953761	574255133	2022	9607275	3647064	1349032	658127	15261498	786	19417.0
433	CHARLESTOWN	2694376966	84851535	21811256	46754658	2847794415	2022	22040073	694086	178465	611166	23523789	3016	7800.0
434	COVENTRY	3270176297	481136320	99449120	171001393	4021763130	2022	61080079	11254260	1929644	3204895	77468878	2237	34631.0
435	CRANSTON	6866409914	1649757156	367758127	293311890	9177237087	2022	123594743	44543443	9929469	8799357	186867012	2300	81247.0
436	CUMBERLAND	3518317657	634418199	203717338	181457461	4537910655	2022	51860429	9351324	5775169	3432140	70419063	2021	34844.0
437	EAST GREENWICH	2201170064	390591860	91916642	80564563	2764243129	2022	46246583	9081261	2778640	1843317	59949801	4583	13081.0
438	EAST PROVIDENCE	2792844817	1179499750	288728740	194804957	4455878264	2022	60046147	31197772	15998448	6816698	114059066	2402	47485.0
439	EXETER	870934255	81371150	25730960	40340211	1018376576	2022	11949348	1116286	353056	1210210	14628901	2195	6665.0
440	FOSTER	578830988	44348300	10475354	24514943	658169585	2022	11658088	940878	307556	735331	13641853	2895	4712.0
441	GLOCESTER	1035186494	64322906	21348829	54847060	1175705289	2022	19102206	1423466	787420	1336378	22649470	2233	10143.0
442	HOPKINTON	883892640	100927741	48281770	40939086	1074041237	2022	16378516	1870191	894600	866813	20010120	2471	8098.0
443	JAMESTOWN	2547705324	79106146	14955997	34750812	2676518279	2022	21095000	654999	123945	500714	22374657	4073	5493.0
444	JOHNSTON	1892160236	563727427	193512074	147376137	2796775874	2022	43973823	15976035	12450579	4420417	76820854	2621	29310.0
445	LINCOLN	1563030120	664419139	187194015	121641256	2536284530	2022	31736099	16750031	5992081	3648669	58126879	2675	21730.0
446	LITTLE COMPTON	2161884261	42555500	11244248	20187370	2235871379	2022	12800734	257035	141651	284780	13484200	3865	3489.0
447	MIDDLETOWN	2757975650	719597500	100218397	79640236	3657431783	2022	33150865	12398665	1726760	1277791	48554082	3031	16019.0
448	NARRAGANSETT	5536820247	367807312	109982141	76850067	6091459767	2022	49056227	4398974	1315386	1264928	56035516	3615	15501.0
449	NEW SHOREHAM	1512219063	161964692	15288959	8271583	1697744297	2022	10131869	1085163	102520	80599	11400152	12446	916.0
450	NEWPORT	6253671908	1409477896	152220873	74501090	7889871767	2022	58346765	19718596	2129751	1746476	81941588	3322	24666.0
451	NORTH KINGSTOWN	3660590770	805344286	183538760	180559202	4830033018	2022	64060339	14093525	3419262	3470760	85043886	3242	26232.0
452	NORTH PROVIDENCE	1996593720	506759429	91286433	109182239	2703821821	2022	45542307	14974743	5913535	3274461	69705046	2141	32557.0
453	NORTH SMITHFIELD	1284610669	335214762	147397137	67072254	1834294822	2022	20981050	6516910	6439852	2011093	35948906	2896	12413.0
454	PAWTUCKET	4013154906	949048713	153718310	164737056	5280658985	2022	66538110	27541394	8007187	4933972	107020663	1490	71826.0
455	PORTSMOUTH	3341309327	303880755	127859056	67166377	3840215515	2022	51138749	4650895	1957129	1511133	59257906	3413	17362.0
456	PROVIDENCE	6695723334	3469276368	1117783000	307901312	11590684014	2022	167013329	127322520	62372321	9258888	365967057	2039	179484.0
457	RICHMOND	850001231	96033340	26235273	35957056	1008226900	2022	17527028	1980207	540986	813781	20862003	2726	7653.0
458	SCITUATE	1251853517	353921441	26970497	54799296	1687544751	2022	21843937	8207438	1073700	1643803	32768878	3082	10632.0
459	SMITHFIELD	2076774871	719365166	148073299	117981343	3062194679	2022	34208110	13452127	8845889	3538967	60045093	2768	21693.0
460	SOUTH KINGSTOWN	4464036880	554765455	107007815	136900889	5262711039	2022	64505314	8016362	1546259	2560570	76628505	2500	30651.0
461	TIVERTON	2382716127	295266002	70126333	70281877	2818390339	2022	34001362	4213446	1000703	1345168	40560680	2571	15776.0
462	WARREN	1136266337	200000982	37340751	43706299	1417314369	2022	20134631	3544017	661634	1136094	25476376	2429	10488.0
463	WARWICK	7247275009	2391529993	572025589	445330825	10656161416	2022	135741461	67201993	21428079	13359925	237731457	2935	80999.0
464	WEST GREENWICH	596066133	222585643	51343215	42003177	911998168	2022	12961156	5426647	1751849	798853	20938506	3364	6224.0
465	WEST WARWICK	1759612795	505959933	159047620	107997356	2532617704	2022	41815001	16775720	7271969	3074686	68937375	2382	28941.0
466	WESTERLY	5643209917	677003900	133872207	114923126	6569009150	2022	65009851	7799085	1574260	3210367	77593563	3442	22543.0
467	WOONSOCKET	1130855404	547028703	123239200	89200938	1890324245	2022	26857836	19009252	5740485	2674739	54282311	1305	41596.0

Read tax rates

p = Path('.')
txt_folder = p.rglob('../division_of_municipal_finance/tax_rates_by_town/property_tax_by_town_20*.csv')
files = [x for x in txt_folder]

tax_rates = None

for file in files:
    fpath = file.as_posix()
    fyear = int(file.name[21:25])
    
    df = pd.read_csv(fpath)
    df['fyear'] = fyear
    df.rename(columns = {'Town':'Municipality'}, inplace = True)
    df['Municipality'] = df['Municipality'].str.upper().str.strip()
    if tax_rates is None:
        tax_rates = df
    else:
        frames = [tax_rates, df]
        tax_rates = pd.concat(frames, sort=False)

print(tax_rates.shape)
print(tax_rates.columns.to_numpy())
tax_rates.sort_values(['fyear','Municipality'],ascending=[False,True]).head(39)

(892, 7)
['Municipality' 'RRE' 'COMM' 'PP' 'MV' 'INVTY' 'fyear']

	Municipality	RRE	COMM	PP	MV	INVTY	fyear
0	BARRINGTON	19.15	19.15	19.15	30.00	NaN	2022
1	BRISTOL	14.38	14.38	14.38	17.35	NaN	2022
2	BURRILLVILLE	16.42	16.42	16.42	30.00	NaN	2022
3	CENTRAL FALLS	23.76	42.33	56.47	30.00	NaN	2022
4	CHARLESTOWN	8.18	8.18	8.18	13.08	NaN	2022
5	COVENTRY	19.40	23.39	19.4	18.75	NaN	2022
6	CRANSTON	18.00	27.00	27.0	30.00	NaN	2022
7	CUMBERLAND	14.74	14.74	29.45	19.87	NaN	2022
8	EAST GREENWICH	21.01	23.25	30.23	22.88	NaN	2022
9	EAST PROVIDENCE	21.50	26.45	55.41	35.00	NaN	2022
10	EXETER	13.72	13.72	13.72	30.00	NaN	2022
11	FOSTER	21.34	21.34	29.36	30.00	NaN	2022
12	GLOCESTER	18.44	22.13	36.88	24.37	NaN	2022
13	HOPKINTON	18.53	18.53	18.53	21.18	NaN	2022
14	JAMESTOWN	8.28	8.28	8.28	14.42	NaN	2022
15	JOHNSTON	23.24	28.34	64.34	30.00	NaN	2022
16	LINCOLN	20.29	25.21	32.01	30.00	NaN	2022
17	LITTLE COMPTON	6.04	6.04	12.08	13.90	NaN	2022
18	MIDDLETOWN	12.02	17.23	17.23	16.05	NaN	2022
19	NARRAGANSETT	8.86	11.96	11.96	16.46	NaN	2022
20	NEW SHOREHAM	6.70	6.70	6.7	9.75	NaN	2022
21	NEWPORT	9.33	13.99	13.99	23.45	NaN	2022
22	NORTH KINGSTOWN	17.50	17.50	17.5	22.04	NaN	2022
23	NORTH PROVIDENCE	22.81	29.55	64.78	30.00	NaN	2022
24	NORTH SMITHFIELD	16.35	19.44	43.69	30.00	NaN	2022
25	PAWTUCKET	16.58	29.02	52.09	30.00	NaN	2022
26	PORTSMOUTH	15.31	15.31	15.31	22.50	NaN	2022
27	PROVIDENCE	24.56	36.70	55.8	30.00	NaN	2022
28	RICHMOND	20.62	20.62	20.62	22.64	NaN	2022
29	SCITUATE	18.69	23.19	39.81	30.00	NaN	2022
30	SMITHFIELD	17.13	18.70	59.74	30.00	NaN	2022
31	SOUTH KINGSTOWN	14.45	14.45	14.45	18.71	NaN	2022
32	TIVERTON	14.27	14.27	14.27	19.14	NaN	2022
33	WARREN	17.72	17.72	17.72	26.00	NaN	2022
34	WARWICK	18.73	28.10	37.46	30.00	NaN	2022
35	WEST GREENWICH	24.03	24.03	34.12	19.02	NaN	2022
36	WEST WARWICK	23.00	32.43	45.72	28.47	NaN	2022
37	WESTERLY	11.52	11.52	11.52	29.67	NaN	2022
38	WOONSOCKET	23.75	34.75	46.58	30.00	NaN	2022

Add tax rates to assessed values and levy

rimf2 = pd.merge(rimf1,tax_rates, on=['Municipality','fyear'], how='inner',suffixes=('_mf1','_tr'))
print(rimf2.columns.to_numpy())
rimf2.sort_values(['fyear','Municipality'],ascending=[False,True]).head(39)

['Municipality' 'Residential_av' 'Industrial_av' 'Tangible_av'
 'Vehicle_av' 'Total Assessed' 'fyear' 'Residential_lv' 'Industrial_lv'
 'Tangible_lv' 'Vehicle_lv' 'Total Levy' 'Levy Per Capita' 'population'
 'RRE' 'COMM' 'PP' 'MV' 'INVTY']

	Municipality	Residential_av	Industrial_av	Tangible_av	Vehicle_av	Total Assessed	fyear	Residential_lv	Industrial_lv	Tangible_lv	Vehicle_lv	Total Levy	Levy Per Capita	population	RRE	COMM	PP	MV	INVTY
428	BARRINGTON	3200189345	150387019	45980777	87258115	3483815256	2022	61283626	2879911	880485	2617376	67661399	4194	16133.0	19.15	19.15	19.15	30.00	NaN
429	BRISTOL	2787240049	296033364	50048887	91665937	3224988237	2022	40080512	4256960	719703	1590404	46647579	2106	22150.0	14.38	14.38	14.38	17.35	NaN
430	BURRILLVILLE	1379931278	282880476	180839889	72441728	1916093371	2022	22658732	4644916	2969593	2172854	32446095	1956	16588.0	16.42	16.42	16.42	30.00	NaN
431	CENTRAL FALLS	442255997	86157836	23887539	21953761	574255133	2022	9607275	3647064	1349032	658127	15261498	786	19417.0	23.76	42.33	56.47	30.00	NaN
432	CHARLESTOWN	2694376966	84851535	21811256	46754658	2847794415	2022	22040073	694086	178465	611166	23523789	3016	7800.0	8.18	8.18	8.18	13.08	NaN
433	COVENTRY	3270176297	481136320	99449120	171001393	4021763130	2022	61080079	11254260	1929644	3204895	77468878	2237	34631.0	19.40	23.39	19.4	18.75	NaN
434	CRANSTON	6866409914	1649757156	367758127	293311890	9177237087	2022	123594743	44543443	9929469	8799357	186867012	2300	81247.0	18.00	27.00	27.0	30.00	NaN
435	CUMBERLAND	3518317657	634418199	203717338	181457461	4537910655	2022	51860429	9351324	5775169	3432140	70419063	2021	34844.0	14.74	14.74	29.45	19.87	NaN
436	EAST GREENWICH	2201170064	390591860	91916642	80564563	2764243129	2022	46246583	9081261	2778640	1843317	59949801	4583	13081.0	21.01	23.25	30.23	22.88	NaN
437	EAST PROVIDENCE	2792844817	1179499750	288728740	194804957	4455878264	2022	60046147	31197772	15998448	6816698	114059066	2402	47485.0	21.50	26.45	55.41	35.00	NaN
438	EXETER	870934255	81371150	25730960	40340211	1018376576	2022	11949348	1116286	353056	1210210	14628901	2195	6665.0	13.72	13.72	13.72	30.00	NaN
439	FOSTER	578830988	44348300	10475354	24514943	658169585	2022	11658088	940878	307556	735331	13641853	2895	4712.0	21.34	21.34	29.36	30.00	NaN
440	GLOCESTER	1035186494	64322906	21348829	54847060	1175705289	2022	19102206	1423466	787420	1336378	22649470	2233	10143.0	18.44	22.13	36.88	24.37	NaN
441	HOPKINTON	883892640	100927741	48281770	40939086	1074041237	2022	16378516	1870191	894600	866813	20010120	2471	8098.0	18.53	18.53	18.53	21.18	NaN
442	JAMESTOWN	2547705324	79106146	14955997	34750812	2676518279	2022	21095000	654999	123945	500714	22374657	4073	5493.0	8.28	8.28	8.28	14.42	NaN
443	JOHNSTON	1892160236	563727427	193512074	147376137	2796775874	2022	43973823	15976035	12450579	4420417	76820854	2621	29310.0	23.24	28.34	64.34	30.00	NaN
444	LINCOLN	1563030120	664419139	187194015	121641256	2536284530	2022	31736099	16750031	5992081	3648669	58126879	2675	21730.0	20.29	25.21	32.01	30.00	NaN
445	LITTLE COMPTON	2161884261	42555500	11244248	20187370	2235871379	2022	12800734	257035	141651	284780	13484200	3865	3489.0	6.04	6.04	12.08	13.90	NaN
446	MIDDLETOWN	2757975650	719597500	100218397	79640236	3657431783	2022	33150865	12398665	1726760	1277791	48554082	3031	16019.0	12.02	17.23	17.23	16.05	NaN
447	NARRAGANSETT	5536820247	367807312	109982141	76850067	6091459767	2022	49056227	4398974	1315386	1264928	56035516	3615	15501.0	8.86	11.96	11.96	16.46	NaN
448	NEW SHOREHAM	1512219063	161964692	15288959	8271583	1697744297	2022	10131869	1085163	102520	80599	11400152	12446	916.0	6.70	6.70	6.7	9.75	NaN
449	NEWPORT	6253671908	1409477896	152220873	74501090	7889871767	2022	58346765	19718596	2129751	1746476	81941588	3322	24666.0	9.33	13.99	13.99	23.45	NaN
450	NORTH KINGSTOWN	3660590770	805344286	183538760	180559202	4830033018	2022	64060339	14093525	3419262	3470760	85043886	3242	26232.0	17.50	17.50	17.5	22.04	NaN
451	NORTH PROVIDENCE	1996593720	506759429	91286433	109182239	2703821821	2022	45542307	14974743	5913535	3274461	69705046	2141	32557.0	22.81	29.55	64.78	30.00	NaN
452	NORTH SMITHFIELD	1284610669	335214762	147397137	67072254	1834294822	2022	20981050	6516910	6439852	2011093	35948906	2896	12413.0	16.35	19.44	43.69	30.00	NaN
453	PAWTUCKET	4013154906	949048713	153718310	164737056	5280658985	2022	66538110	27541394	8007187	4933972	107020663	1490	71826.0	16.58	29.02	52.09	30.00	NaN
454	PORTSMOUTH	3341309327	303880755	127859056	67166377	3840215515	2022	51138749	4650895	1957129	1511133	59257906	3413	17362.0	15.31	15.31	15.31	22.50	NaN
455	PROVIDENCE	6695723334	3469276368	1117783000	307901312	11590684014	2022	167013329	127322520	62372321	9258888	365967057	2039	179484.0	24.56	36.70	55.8	30.00	NaN
456	RICHMOND	850001231	96033340	26235273	35957056	1008226900	2022	17527028	1980207	540986	813781	20862003	2726	7653.0	20.62	20.62	20.62	22.64	NaN
457	SCITUATE	1251853517	353921441	26970497	54799296	1687544751	2022	21843937	8207438	1073700	1643803	32768878	3082	10632.0	18.69	23.19	39.81	30.00	NaN
458	SMITHFIELD	2076774871	719365166	148073299	117981343	3062194679	2022	34208110	13452127	8845889	3538967	60045093	2768	21693.0	17.13	18.70	59.74	30.00	NaN
459	SOUTH KINGSTOWN	4464036880	554765455	107007815	136900889	5262711039	2022	64505314	8016362	1546259	2560570	76628505	2500	30651.0	14.45	14.45	14.45	18.71	NaN
460	TIVERTON	2382716127	295266002	70126333	70281877	2818390339	2022	34001362	4213446	1000703	1345168	40560680	2571	15776.0	14.27	14.27	14.27	19.14	NaN
461	WARREN	1136266337	200000982	37340751	43706299	1417314369	2022	20134631	3544017	661634	1136094	25476376	2429	10488.0	17.72	17.72	17.72	26.00	NaN
462	WARWICK	7247275009	2391529993	572025589	445330825	10656161416	2022	135741461	67201993	21428079	13359925	237731457	2935	80999.0	18.73	28.10	37.46	30.00	NaN
463	WEST GREENWICH	596066133	222585643	51343215	42003177	911998168	2022	12961156	5426647	1751849	798853	20938506	3364	6224.0	24.03	24.03	34.12	19.02	NaN
464	WEST WARWICK	1759612795	505959933	159047620	107997356	2532617704	2022	41815001	16775720	7271969	3074686	68937375	2382	28941.0	23.00	32.43	45.72	28.47	NaN
465	WESTERLY	5643209917	677003900	133872207	114923126	6569009150	2022	65009851	7799085	1574260	3210367	77593563	3442	22543.0	11.52	11.52	11.52	29.67	NaN
466	WOONSOCKET	1130855404	547028703	123239200	89200938	1890324245	2022	26857836	19009252	5740485	2674739	54282311	1305	41596.0	23.75	34.75	46.58	30.00	NaN

Read State Education Aid

education_aid = pd.read_csv("../division_of_municipal_finance/State_Education_Aid_FY2022.csv")
education_aid['Municipality'] = education_aid['Municipality'].str.upper().str.strip()
print(education_aid.shape)
education_aid

(39, 8)

	Municipality	fyear	Education Aid	RADM	FRL	SSRC	FRPL	QM
0	BARRINGTON	2022	7924118	3416	145	0.051	0.298	0.214
1	BRISTOL	2022	4852340	1923	485	0.230	0.199	0.215
2	BURRILLVILLE	2022	13780456	2226	782	0.389	0.607	0.510
3	CENTRAL FALLS	2022	38557253	2733	2666	0.933	0.975	0.954
4	CHARLESTOWN	2022	1291300	740	156	0.214	0.000	0.151
5	COVENTRY	2022	24066104	4502	1207	0.303	0.565	0.453
6	CRANSTON	2022	68482484	10166	4098	0.438	0.635	0.545
7	CUMBERLAND	2022	20401578	4593	910	0.227	0.520	0.401
8	EAST GREENWICH	2022	4305850	2572	173	0.086	0.198	0.153
9	EAST PROVIDENCE	2022	36103488	5036	2443	0.480	0.637	0.564
10	EXETER	2022	1898454	752	141	0.201	0.237	0.220
11	FOSTER	2022	1057919	227	64	0.279	0.484	0.395
12	GLOCESTER	2022	2422153	553	73	0.137	0.535	0.391
13	HOPKINTON	2022	5590417	1137	236	0.210	0.565	0.426
14	JAMESTOWN	2022	291969	655	36	0.058	0.000	0.041
15	JOHNSTON	2022	19496027	3287	1371	0.427	0.523	0.477
16	LINCOLN	2022	15940955	3179	830	0.276	0.536	0.426
17	LITTLE COMPTON	2022	432020	344	49	0.158	0.000	0.112
18	MIDDLETOWN	2022	8132606	2175	694	0.325	0.297	0.311
19	NARRAGANSETT	2022	2178394	1211	220	0.223	0.000	0.158
20	NEW SHOREHAM	2022	211086	148	29	0.176	0.000	0.124
21	NEWPORT	2022	14752903	2088	1471	0.733	0.000	0.518
22	NORTH KINGSTOWN	2022	11216037	3747	793	0.243	0.268	0.256
23	NORTH PROVIDENCE	2022	26608402	3536	1667	0.495	0.680	0.595
24	NORTH SMITHFIELD	2022	6204807	1653	310	0.187	0.424	0.328
25	PAWTUCKET	2022	95061517	8585	6271	0.730	0.875	0.806
26	PORTSMOUTH	2022	3062524	2295	359	0.167	0.000	0.118
27	PROVIDENCE	2022	272489702	21968	18915	0.876	0.859	0.868
28	RICHMOND	2022	5149642	1135	172	0.163	0.544	0.402
29	SCITUATE	2022	2358211	1269	147	0.124	0.200	0.166
30	SMITHFIELD	2022	6817709	2378	366	0.157	0.323	0.254
31	SOUTH KINGSTOWN	2022	4559972	2918	514	0.194	0.006	0.137
32	TIVERTON	2022	6774565	1758	407	0.262	0.388	0.331
33	WARREN	2022	6493383	1240	455	0.360	0.488	0.429
34	WARWICK	2022	39218717	8615	2818	0.358	0.397	0.378
35	WEST GREENWICH	2022	2344535	891	132	0.160	0.288	0.233
36	WEST WARWICK	2022	30857785	3607	1933	0.564	0.747	0.662
37	WESTERLY	2022	7937325	2683	875	0.348	0.000	0.246
38	WOONSOCKET	2022	69995691	5890	4586	0.799	0.902	0.852

Add education aid to tax rates, assessed values, and levy

rimf3 = pd.merge(rimf2,education_aid, on=['Municipality','fyear'], how='inner',suffixes=('_mf2','_ea'))
print(rimf3.columns.to_numpy())
rimf3.sort_values(['fyear','Municipality'],ascending=[False,True]).head(39)

['Municipality' 'Residential_av' 'Industrial_av' 'Tangible_av'
 'Vehicle_av' 'Total Assessed' 'fyear' 'Residential_lv' 'Industrial_lv'
 'Tangible_lv' 'Vehicle_lv' 'Total Levy' 'Levy Per Capita' 'population'
 'RRE' 'COMM' 'PP' 'MV' 'INVTY' 'Education Aid' 'RADM' 'FRL' 'SSRC' 'FRPL'
 'QM']

	Municipality	Residential_av	Industrial_av	Tangible_av	Vehicle_av	Total Assessed	fyear	Residential_lv	Industrial_lv	Tangible_lv	Vehicle_lv	Total Levy	Levy Per Capita	population	RRE	COMM	PP	MV	INVTY	Education Aid	RADM	FRL	SSRC	FRPL	QM
0	BARRINGTON	3200189345	150387019	45980777	87258115	3483815256	2022	61283626	2879911	880485	2617376	67661399	4194	16133.0	19.15	19.15	19.15	30.00	NaN	7924118	3416	145	0.051	0.298	0.214
1	BRISTOL	2787240049	296033364	50048887	91665937	3224988237	2022	40080512	4256960	719703	1590404	46647579	2106	22150.0	14.38	14.38	14.38	17.35	NaN	4852340	1923	485	0.230	0.199	0.215
2	BURRILLVILLE	1379931278	282880476	180839889	72441728	1916093371	2022	22658732	4644916	2969593	2172854	32446095	1956	16588.0	16.42	16.42	16.42	30.00	NaN	13780456	2226	782	0.389	0.607	0.510
3	CENTRAL FALLS	442255997	86157836	23887539	21953761	574255133	2022	9607275	3647064	1349032	658127	15261498	786	19417.0	23.76	42.33	56.47	30.00	NaN	38557253	2733	2666	0.933	0.975	0.954
4	CHARLESTOWN	2694376966	84851535	21811256	46754658	2847794415	2022	22040073	694086	178465	611166	23523789	3016	7800.0	8.18	8.18	8.18	13.08	NaN	1291300	740	156	0.214	0.000	0.151
5	COVENTRY	3270176297	481136320	99449120	171001393	4021763130	2022	61080079	11254260	1929644	3204895	77468878	2237	34631.0	19.40	23.39	19.4	18.75	NaN	24066104	4502	1207	0.303	0.565	0.453
6	CRANSTON	6866409914	1649757156	367758127	293311890	9177237087	2022	123594743	44543443	9929469	8799357	186867012	2300	81247.0	18.00	27.00	27.0	30.00	NaN	68482484	10166	4098	0.438	0.635	0.545
7	CUMBERLAND	3518317657	634418199	203717338	181457461	4537910655	2022	51860429	9351324	5775169	3432140	70419063	2021	34844.0	14.74	14.74	29.45	19.87	NaN	20401578	4593	910	0.227	0.520	0.401
8	EAST GREENWICH	2201170064	390591860	91916642	80564563	2764243129	2022	46246583	9081261	2778640	1843317	59949801	4583	13081.0	21.01	23.25	30.23	22.88	NaN	4305850	2572	173	0.086	0.198	0.153
9	EAST PROVIDENCE	2792844817	1179499750	288728740	194804957	4455878264	2022	60046147	31197772	15998448	6816698	114059066	2402	47485.0	21.50	26.45	55.41	35.00	NaN	36103488	5036	2443	0.480	0.637	0.564
10	EXETER	870934255	81371150	25730960	40340211	1018376576	2022	11949348	1116286	353056	1210210	14628901	2195	6665.0	13.72	13.72	13.72	30.00	NaN	1898454	752	141	0.201	0.237	0.220
11	FOSTER	578830988	44348300	10475354	24514943	658169585	2022	11658088	940878	307556	735331	13641853	2895	4712.0	21.34	21.34	29.36	30.00	NaN	1057919	227	64	0.279	0.484	0.395
12	GLOCESTER	1035186494	64322906	21348829	54847060	1175705289	2022	19102206	1423466	787420	1336378	22649470	2233	10143.0	18.44	22.13	36.88	24.37	NaN	2422153	553	73	0.137	0.535	0.391
13	HOPKINTON	883892640	100927741	48281770	40939086	1074041237	2022	16378516	1870191	894600	866813	20010120	2471	8098.0	18.53	18.53	18.53	21.18	NaN	5590417	1137	236	0.210	0.565	0.426
14	JAMESTOWN	2547705324	79106146	14955997	34750812	2676518279	2022	21095000	654999	123945	500714	22374657	4073	5493.0	8.28	8.28	8.28	14.42	NaN	291969	655	36	0.058	0.000	0.041
15	JOHNSTON	1892160236	563727427	193512074	147376137	2796775874	2022	43973823	15976035	12450579	4420417	76820854	2621	29310.0	23.24	28.34	64.34	30.00	NaN	19496027	3287	1371	0.427	0.523	0.477
16	LINCOLN	1563030120	664419139	187194015	121641256	2536284530	2022	31736099	16750031	5992081	3648669	58126879	2675	21730.0	20.29	25.21	32.01	30.00	NaN	15940955	3179	830	0.276	0.536	0.426
17	LITTLE COMPTON	2161884261	42555500	11244248	20187370	2235871379	2022	12800734	257035	141651	284780	13484200	3865	3489.0	6.04	6.04	12.08	13.90	NaN	432020	344	49	0.158	0.000	0.112
18	MIDDLETOWN	2757975650	719597500	100218397	79640236	3657431783	2022	33150865	12398665	1726760	1277791	48554082	3031	16019.0	12.02	17.23	17.23	16.05	NaN	8132606	2175	694	0.325	0.297	0.311
19	NARRAGANSETT	5536820247	367807312	109982141	76850067	6091459767	2022	49056227	4398974	1315386	1264928	56035516	3615	15501.0	8.86	11.96	11.96	16.46	NaN	2178394	1211	220	0.223	0.000	0.158
20	NEW SHOREHAM	1512219063	161964692	15288959	8271583	1697744297	2022	10131869	1085163	102520	80599	11400152	12446	916.0	6.70	6.70	6.7	9.75	NaN	211086	148	29	0.176	0.000	0.124
21	NEWPORT	6253671908	1409477896	152220873	74501090	7889871767	2022	58346765	19718596	2129751	1746476	81941588	3322	24666.0	9.33	13.99	13.99	23.45	NaN	14752903	2088	1471	0.733	0.000	0.518
22	NORTH KINGSTOWN	3660590770	805344286	183538760	180559202	4830033018	2022	64060339	14093525	3419262	3470760	85043886	3242	26232.0	17.50	17.50	17.5	22.04	NaN	11216037	3747	793	0.243	0.268	0.256
23	NORTH PROVIDENCE	1996593720	506759429	91286433	109182239	2703821821	2022	45542307	14974743	5913535	3274461	69705046	2141	32557.0	22.81	29.55	64.78	30.00	NaN	26608402	3536	1667	0.495	0.680	0.595
24	NORTH SMITHFIELD	1284610669	335214762	147397137	67072254	1834294822	2022	20981050	6516910	6439852	2011093	35948906	2896	12413.0	16.35	19.44	43.69	30.00	NaN	6204807	1653	310	0.187	0.424	0.328
25	PAWTUCKET	4013154906	949048713	153718310	164737056	5280658985	2022	66538110	27541394	8007187	4933972	107020663	1490	71826.0	16.58	29.02	52.09	30.00	NaN	95061517	8585	6271	0.730	0.875	0.806
26	PORTSMOUTH	3341309327	303880755	127859056	67166377	3840215515	2022	51138749	4650895	1957129	1511133	59257906	3413	17362.0	15.31	15.31	15.31	22.50	NaN	3062524	2295	359	0.167	0.000	0.118
27	PROVIDENCE	6695723334	3469276368	1117783000	307901312	11590684014	2022	167013329	127322520	62372321	9258888	365967057	2039	179484.0	24.56	36.70	55.8	30.00	NaN	272489702	21968	18915	0.876	0.859	0.868
28	RICHMOND	850001231	96033340	26235273	35957056	1008226900	2022	17527028	1980207	540986	813781	20862003	2726	7653.0	20.62	20.62	20.62	22.64	NaN	5149642	1135	172	0.163	0.544	0.402
29	SCITUATE	1251853517	353921441	26970497	54799296	1687544751	2022	21843937	8207438	1073700	1643803	32768878	3082	10632.0	18.69	23.19	39.81	30.00	NaN	2358211	1269	147	0.124	0.200	0.166
30	SMITHFIELD	2076774871	719365166	148073299	117981343	3062194679	2022	34208110	13452127	8845889	3538967	60045093	2768	21693.0	17.13	18.70	59.74	30.00	NaN	6817709	2378	366	0.157	0.323	0.254
31	SOUTH KINGSTOWN	4464036880	554765455	107007815	136900889	5262711039	2022	64505314	8016362	1546259	2560570	76628505	2500	30651.0	14.45	14.45	14.45	18.71	NaN	4559972	2918	514	0.194	0.006	0.137
32	TIVERTON	2382716127	295266002	70126333	70281877	2818390339	2022	34001362	4213446	1000703	1345168	40560680	2571	15776.0	14.27	14.27	14.27	19.14	NaN	6774565	1758	407	0.262	0.388	0.331
33	WARREN	1136266337	200000982	37340751	43706299	1417314369	2022	20134631	3544017	661634	1136094	25476376	2429	10488.0	17.72	17.72	17.72	26.00	NaN	6493383	1240	455	0.360	0.488	0.429
34	WARWICK	7247275009	2391529993	572025589	445330825	10656161416	2022	135741461	67201993	21428079	13359925	237731457	2935	80999.0	18.73	28.10	37.46	30.00	NaN	39218717	8615	2818	0.358	0.397	0.378
35	WEST GREENWICH	596066133	222585643	51343215	42003177	911998168	2022	12961156	5426647	1751849	798853	20938506	3364	6224.0	24.03	24.03	34.12	19.02	NaN	2344535	891	132	0.160	0.288	0.233
36	WEST WARWICK	1759612795	505959933	159047620	107997356	2532617704	2022	41815001	16775720	7271969	3074686	68937375	2382	28941.0	23.00	32.43	45.72	28.47	NaN	30857785	3607	1933	0.564	0.747	0.662
37	WESTERLY	5643209917	677003900	133872207	114923126	6569009150	2022	65009851	7799085	1574260	3210367	77593563	3442	22543.0	11.52	11.52	11.52	29.67	NaN	7937325	2683	875	0.348	0.000	0.246
38	WOONSOCKET	1130855404	547028703	123239200	89200938	1890324245	2022	26857836	19009252	5740485	2674739	54282311	1305	41596.0	23.75	34.75	46.58	30.00	NaN	69995691	5890	4586	0.799	0.902	0.852

Add derived fields for analysis

final = rimf3
final['Assessed per Capita'] = round(final['Total Assessed']/final['population'])    #assessed value per capita
final['RADM per Capita'] = round(final['RADM']/final['population'],2)                #RADM per capita
final['Aid at RRE'] = round(1000*final['Education Aid']/final['Residential_av'],2)   #Aid rate equivalent
final['Adjusted RRE'] = final['RRE'] + final['Aid at RRE']                           #residential rate with aid
final['Assessed per RADM'] = round(final['Total Assessed']/final['RADM'])            #assessed value per RADM
print(final.columns.to_numpy())
final = final.sort_values(['fyear','Municipality'],ascending=[False,True])
final

['Municipality' 'Residential_av' 'Industrial_av' 'Tangible_av'
 'Vehicle_av' 'Total Assessed' 'fyear' 'Residential_lv' 'Industrial_lv'
 'Tangible_lv' 'Vehicle_lv' 'Total Levy' 'Levy Per Capita' 'population'
 'RRE' 'COMM' 'PP' 'MV' 'INVTY' 'Education Aid' 'RADM' 'FRL' 'SSRC' 'FRPL'
 'QM' 'Assessed per Capita' 'RADM per Capita' 'Aid at RRE' 'Adjusted RRE'
 'Assessed per RADM']

	Municipality	Residential_av	Industrial_av	Tangible_av	Vehicle_av	Total Assessed	fyear	Residential_lv	Industrial_lv	Tangible_lv	Vehicle_lv	Total Levy	Levy Per Capita	population	RRE	COMM	PP	MV	INVTY	Education Aid	RADM	FRL	SSRC	FRPL	QM	Assessed per Capita	RADM per Capita	Aid at RRE	Adjusted RRE	Assessed per RADM
0	BARRINGTON	3200189345	150387019	45980777	87258115	3483815256	2022	61283626	2879911	880485	2617376	67661399	4194	16133.0	19.15	19.15	19.15	30.00	NaN	7924118	3416	145	0.051	0.298	0.214	215943.0	0.21	2.48	21.63	1019852.0
1	BRISTOL	2787240049	296033364	50048887	91665937	3224988237	2022	40080512	4256960	719703	1590404	46647579	2106	22150.0	14.38	14.38	14.38	17.35	NaN	4852340	1923	485	0.230	0.199	0.215	145598.0	0.09	1.74	16.12	1677061.0
2	BURRILLVILLE	1379931278	282880476	180839889	72441728	1916093371	2022	22658732	4644916	2969593	2172854	32446095	1956	16588.0	16.42	16.42	16.42	30.00	NaN	13780456	2226	782	0.389	0.607	0.510	115511.0	0.13	9.99	26.41	860779.0
3	CENTRAL FALLS	442255997	86157836	23887539	21953761	574255133	2022	9607275	3647064	1349032	658127	15261498	786	19417.0	23.76	42.33	56.47	30.00	NaN	38557253	2733	2666	0.933	0.975	0.954	29575.0	0.14	87.18	110.94	210119.0
4	CHARLESTOWN	2694376966	84851535	21811256	46754658	2847794415	2022	22040073	694086	178465	611166	23523789	3016	7800.0	8.18	8.18	8.18	13.08	NaN	1291300	740	156	0.214	0.000	0.151	365102.0	0.09	0.48	8.66	3848371.0
5	COVENTRY	3270176297	481136320	99449120	171001393	4021763130	2022	61080079	11254260	1929644	3204895	77468878	2237	34631.0	19.40	23.39	19.4	18.75	NaN	24066104	4502	1207	0.303	0.565	0.453	116132.0	0.13	7.36	26.76	893328.0
6	CRANSTON	6866409914	1649757156	367758127	293311890	9177237087	2022	123594743	44543443	9929469	8799357	186867012	2300	81247.0	18.00	27.00	27.0	30.00	NaN	68482484	10166	4098	0.438	0.635	0.545	112955.0	0.13	9.97	27.97	902738.0
7	CUMBERLAND	3518317657	634418199	203717338	181457461	4537910655	2022	51860429	9351324	5775169	3432140	70419063	2021	34844.0	14.74	14.74	29.45	19.87	NaN	20401578	4593	910	0.227	0.520	0.401	130235.0	0.13	5.80	20.54	988006.0
8	EAST GREENWICH	2201170064	390591860	91916642	80564563	2764243129	2022	46246583	9081261	2778640	1843317	59949801	4583	13081.0	21.01	23.25	30.23	22.88	NaN	4305850	2572	173	0.086	0.198	0.153	211317.0	0.20	1.96	22.97	1074745.0
9	EAST PROVIDENCE	2792844817	1179499750	288728740	194804957	4455878264	2022	60046147	31197772	15998448	6816698	114059066	2402	47485.0	21.50	26.45	55.41	35.00	NaN	36103488	5036	2443	0.480	0.637	0.564	93838.0	0.11	12.93	34.43	884805.0
10	EXETER	870934255	81371150	25730960	40340211	1018376576	2022	11949348	1116286	353056	1210210	14628901	2195	6665.0	13.72	13.72	13.72	30.00	NaN	1898454	752	141	0.201	0.237	0.220	152795.0	0.11	2.18	15.90	1354224.0
11	FOSTER	578830988	44348300	10475354	24514943	658169585	2022	11658088	940878	307556	735331	13641853	2895	4712.0	21.34	21.34	29.36	30.00	NaN	1057919	227	64	0.279	0.484	0.395	139679.0	0.05	1.83	23.17	2899425.0
12	GLOCESTER	1035186494	64322906	21348829	54847060	1175705289	2022	19102206	1423466	787420	1336378	22649470	2233	10143.0	18.44	22.13	36.88	24.37	NaN	2422153	553	73	0.137	0.535	0.391	115913.0	0.05	2.34	20.78	2126049.0
13	HOPKINTON	883892640	100927741	48281770	40939086	1074041237	2022	16378516	1870191	894600	866813	20010120	2471	8098.0	18.53	18.53	18.53	21.18	NaN	5590417	1137	236	0.210	0.565	0.426	132630.0	0.14	6.32	24.85	944627.0
14	JAMESTOWN	2547705324	79106146	14955997	34750812	2676518279	2022	21095000	654999	123945	500714	22374657	4073	5493.0	8.28	8.28	8.28	14.42	NaN	291969	655	36	0.058	0.000	0.041	487260.0	0.12	0.11	8.39	4086287.0
15	JOHNSTON	1892160236	563727427	193512074	147376137	2796775874	2022	43973823	15976035	12450579	4420417	76820854	2621	29310.0	23.24	28.34	64.34	30.00	NaN	19496027	3287	1371	0.427	0.523	0.477	95421.0	0.11	10.30	33.54	850860.0
16	LINCOLN	1563030120	664419139	187194015	121641256	2536284530	2022	31736099	16750031	5992081	3648669	58126879	2675	21730.0	20.29	25.21	32.01	30.00	NaN	15940955	3179	830	0.276	0.536	0.426	116718.0	0.15	10.20	30.49	797825.0
17	LITTLE COMPTON	2161884261	42555500	11244248	20187370	2235871379	2022	12800734	257035	141651	284780	13484200	3865	3489.0	6.04	6.04	12.08	13.90	NaN	432020	344	49	0.158	0.000	0.112	640834.0	0.10	0.20	6.24	6499626.0
18	MIDDLETOWN	2757975650	719597500	100218397	79640236	3657431783	2022	33150865	12398665	1726760	1277791	48554082	3031	16019.0	12.02	17.23	17.23	16.05	NaN	8132606	2175	694	0.325	0.297	0.311	228318.0	0.14	2.95	14.97	1681578.0
19	NARRAGANSETT	5536820247	367807312	109982141	76850067	6091459767	2022	49056227	4398974	1315386	1264928	56035516	3615	15501.0	8.86	11.96	11.96	16.46	NaN	2178394	1211	220	0.223	0.000	0.158	392972.0	0.08	0.39	9.25	5030107.0
20	NEW SHOREHAM	1512219063	161964692	15288959	8271583	1697744297	2022	10131869	1085163	102520	80599	11400152	12446	916.0	6.70	6.70	6.7	9.75	NaN	211086	148	29	0.176	0.000	0.124	1853433.0	0.16	0.14	6.84	11471245.0
21	NEWPORT	6253671908	1409477896	152220873	74501090	7889871767	2022	58346765	19718596	2129751	1746476	81941588	3322	24666.0	9.33	13.99	13.99	23.45	NaN	14752903	2088	1471	0.733	0.000	0.518	319868.0	0.08	2.36	11.69	3778674.0
22	NORTH KINGSTOWN	3660590770	805344286	183538760	180559202	4830033018	2022	64060339	14093525	3419262	3470760	85043886	3242	26232.0	17.50	17.50	17.5	22.04	NaN	11216037	3747	793	0.243	0.268	0.256	184128.0	0.14	3.06	20.56	1289040.0
23	NORTH PROVIDENCE	1996593720	506759429	91286433	109182239	2703821821	2022	45542307	14974743	5913535	3274461	69705046	2141	32557.0	22.81	29.55	64.78	30.00	NaN	26608402	3536	1667	0.495	0.680	0.595	83049.0	0.11	13.33	36.14	764655.0
24	NORTH SMITHFIELD	1284610669	335214762	147397137	67072254	1834294822	2022	20981050	6516910	6439852	2011093	35948906	2896	12413.0	16.35	19.44	43.69	30.00	NaN	6204807	1653	310	0.187	0.424	0.328	147772.0	0.13	4.83	21.18	1109676.0
25	PAWTUCKET	4013154906	949048713	153718310	164737056	5280658985	2022	66538110	27541394	8007187	4933972	107020663	1490	71826.0	16.58	29.02	52.09	30.00	NaN	95061517	8585	6271	0.730	0.875	0.806	73520.0	0.12	23.69	40.27	615103.0
26	PORTSMOUTH	3341309327	303880755	127859056	67166377	3840215515	2022	51138749	4650895	1957129	1511133	59257906	3413	17362.0	15.31	15.31	15.31	22.50	NaN	3062524	2295	359	0.167	0.000	0.118	221185.0	0.13	0.92	16.23	1673297.0
27	PROVIDENCE	6695723334	3469276368	1117783000	307901312	11590684014	2022	167013329	127322520	62372321	9258888	365967057	2039	179484.0	24.56	36.70	55.8	30.00	NaN	272489702	21968	18915	0.876	0.859	0.868	64578.0	0.12	40.70	65.26	527617.0
28	RICHMOND	850001231	96033340	26235273	35957056	1008226900	2022	17527028	1980207	540986	813781	20862003	2726	7653.0	20.62	20.62	20.62	22.64	NaN	5149642	1135	172	0.163	0.544	0.402	131743.0	0.15	6.06	26.68	888306.0
29	SCITUATE	1251853517	353921441	26970497	54799296	1687544751	2022	21843937	8207438	1073700	1643803	32768878	3082	10632.0	18.69	23.19	39.81	30.00	NaN	2358211	1269	147	0.124	0.200	0.166	158723.0	0.12	1.88	20.57	1329822.0
30	SMITHFIELD	2076774871	719365166	148073299	117981343	3062194679	2022	34208110	13452127	8845889	3538967	60045093	2768	21693.0	17.13	18.70	59.74	30.00	NaN	6817709	2378	366	0.157	0.323	0.254	141160.0	0.11	3.28	20.41	1287719.0
31	SOUTH KINGSTOWN	4464036880	554765455	107007815	136900889	5262711039	2022	64505314	8016362	1546259	2560570	76628505	2500	30651.0	14.45	14.45	14.45	18.71	NaN	4559972	2918	514	0.194	0.006	0.137	171698.0	0.10	1.02	15.47	1803534.0
32	TIVERTON	2382716127	295266002	70126333	70281877	2818390339	2022	34001362	4213446	1000703	1345168	40560680	2571	15776.0	14.27	14.27	14.27	19.14	NaN	6774565	1758	407	0.262	0.388	0.331	178651.0	0.11	2.84	17.11	1603180.0
33	WARREN	1136266337	200000982	37340751	43706299	1417314369	2022	20134631	3544017	661634	1136094	25476376	2429	10488.0	17.72	17.72	17.72	26.00	NaN	6493383	1240	455	0.360	0.488	0.429	135137.0	0.12	5.71	23.43	1142995.0
34	WARWICK	7247275009	2391529993	572025589	445330825	10656161416	2022	135741461	67201993	21428079	13359925	237731457	2935	80999.0	18.73	28.10	37.46	30.00	NaN	39218717	8615	2818	0.358	0.397	0.378	131559.0	0.11	5.41	24.14	1236931.0
35	WEST GREENWICH	596066133	222585643	51343215	42003177	911998168	2022	12961156	5426647	1751849	798853	20938506	3364	6224.0	24.03	24.03	34.12	19.02	NaN	2344535	891	132	0.160	0.288	0.233	146529.0	0.14	3.93	27.96	1023567.0
36	WEST WARWICK	1759612795	505959933	159047620	107997356	2532617704	2022	41815001	16775720	7271969	3074686	68937375	2382	28941.0	23.00	32.43	45.72	28.47	NaN	30857785	3607	1933	0.564	0.747	0.662	87510.0	0.12	17.54	40.54	702140.0
37	WESTERLY	5643209917	677003900	133872207	114923126	6569009150	2022	65009851	7799085	1574260	3210367	77593563	3442	22543.0	11.52	11.52	11.52	29.67	NaN	7937325	2683	875	0.348	0.000	0.246	291399.0	0.12	1.41	12.93	2448382.0
38	WOONSOCKET	1130855404	547028703	123239200	89200938	1890324245	2022	26857836	19009252	5740485	2674739	54282311	1305	41596.0	23.75	34.75	46.58	30.00	NaN	69995691	5890	4586	0.799	0.902	0.852	45445.0	0.14	61.90	85.65	320938.0

def get_fyear(final):
    fy = str(np.max(final['fyear'].to_numpy()))
    return(fy)

Assessed Value per Capita Ranking

avcp = final.sort_values(['fyear','Assessed per Capita'],ascending=[False,False]).head(39).\
    loc[:,['Municipality','Total Assessed','fyear','population','Assessed per Capita']].reset_index()
avcp_municipality=avcp['Municipality'].to_numpy()
avcp_assessed_per_capita=avcp['Assessed per Capita'].to_numpy()
avcp

	index	Municipality	Total Assessed	fyear	population	Assessed per Capita
0	20	NEW SHOREHAM	1697744297	2022	916.0	1853433.0
1	17	LITTLE COMPTON	2235871379	2022	3489.0	640834.0
2	14	JAMESTOWN	2676518279	2022	5493.0	487260.0
3	19	NARRAGANSETT	6091459767	2022	15501.0	392972.0
4	4	CHARLESTOWN	2847794415	2022	7800.0	365102.0
5	21	NEWPORT	7889871767	2022	24666.0	319868.0
6	37	WESTERLY	6569009150	2022	22543.0	291399.0
7	18	MIDDLETOWN	3657431783	2022	16019.0	228318.0
8	26	PORTSMOUTH	3840215515	2022	17362.0	221185.0
9	0	BARRINGTON	3483815256	2022	16133.0	215943.0
10	8	EAST GREENWICH	2764243129	2022	13081.0	211317.0
11	22	NORTH KINGSTOWN	4830033018	2022	26232.0	184128.0
12	32	TIVERTON	2818390339	2022	15776.0	178651.0
13	31	SOUTH KINGSTOWN	5262711039	2022	30651.0	171698.0
14	29	SCITUATE	1687544751	2022	10632.0	158723.0
15	10	EXETER	1018376576	2022	6665.0	152795.0
16	24	NORTH SMITHFIELD	1834294822	2022	12413.0	147772.0
17	35	WEST GREENWICH	911998168	2022	6224.0	146529.0
18	1	BRISTOL	3224988237	2022	22150.0	145598.0
19	30	SMITHFIELD	3062194679	2022	21693.0	141160.0
20	11	FOSTER	658169585	2022	4712.0	139679.0
21	33	WARREN	1417314369	2022	10488.0	135137.0
22	13	HOPKINTON	1074041237	2022	8098.0	132630.0
23	28	RICHMOND	1008226900	2022	7653.0	131743.0
24	34	WARWICK	10656161416	2022	80999.0	131559.0
25	7	CUMBERLAND	4537910655	2022	34844.0	130235.0
26	16	LINCOLN	2536284530	2022	21730.0	116718.0
27	5	COVENTRY	4021763130	2022	34631.0	116132.0
28	12	GLOCESTER	1175705289	2022	10143.0	115913.0
29	2	BURRILLVILLE	1916093371	2022	16588.0	115511.0
30	6	CRANSTON	9177237087	2022	81247.0	112955.0
31	15	JOHNSTON	2796775874	2022	29310.0	95421.0
32	9	EAST PROVIDENCE	4455878264	2022	47485.0	93838.0
33	36	WEST WARWICK	2532617704	2022	28941.0	87510.0
34	23	NORTH PROVIDENCE	2703821821	2022	32557.0	83049.0
35	25	PAWTUCKET	5280658985	2022	71826.0	73520.0
36	27	PROVIDENCE	11590684014	2022	179484.0	64578.0
37	38	WOONSOCKET	1890324245	2022	41596.0	45445.0
38	3	CENTRAL FALLS	574255133	2022	19417.0	29575.0

Bar chart of assessed value per capita

import matplotlib.pyplot as plt
import numpy as np

towns = avcp_municipality
y = avcp_assessed_per_capita

fy = get_fyear(final)
stitle = 'FY' + fy + ' Total Assessed Value per Capita'
fname = '../FY' + fy + '_Assessed_Value_per_Capita.png' 

plt.rcdefaults()
plt.rcParams['figure.figsize'] = [18, 10]
fig, ax = plt.subplots()

x_pos = np.arange(len(towns))
ax.bar(x_pos, y, align='center')
ax.set_yticks(np.arange(0,2000000,200000))
ax.set_xticklabels(towns,rotation = 90,fontsize=14)
ax.set_xticks(np.arange(len(towns)))
ax.set_ylabel('Assessed Value per Capita (millions)',fontsize=24)
ax.set_title(stitle,fontsize=28)

fig.tight_layout()
#plt.show()
plt.savefig(fname)

/tmp/ipykernel_33004/2207103273.py:18: UserWarning: FixedFormatter should only be used together with FixedLocator
  ax.set_xticklabels(towns,rotation = 90,fontsize=14)

Residential tax rate ranking

rtrr = final.sort_values(['fyear','RRE'],ascending=[False,False]).\
    loc[:,['Municipality','fyear','population','Assessed per Capita','RADM','RRE']].reset_index()
rtrr_municipality=rtrr['Municipality'].to_numpy()
rtrr_residential_tax_rate=rtrr['RRE'].to_numpy()
rtrr

	index	Municipality	fyear	population	Assessed per Capita	RADM	RRE
0	27	PROVIDENCE	2022	179484.0	64578.0	21968	24.56
1	35	WEST GREENWICH	2022	6224.0	146529.0	891	24.03
2	3	CENTRAL FALLS	2022	19417.0	29575.0	2733	23.76
3	38	WOONSOCKET	2022	41596.0	45445.0	5890	23.75
4	15	JOHNSTON	2022	29310.0	95421.0	3287	23.24
5	36	WEST WARWICK	2022	28941.0	87510.0	3607	23.00
6	23	NORTH PROVIDENCE	2022	32557.0	83049.0	3536	22.81
7	9	EAST PROVIDENCE	2022	47485.0	93838.0	5036	21.50
8	11	FOSTER	2022	4712.0	139679.0	227	21.34
9	8	EAST GREENWICH	2022	13081.0	211317.0	2572	21.01
10	28	RICHMOND	2022	7653.0	131743.0	1135	20.62
11	16	LINCOLN	2022	21730.0	116718.0	3179	20.29
12	5	COVENTRY	2022	34631.0	116132.0	4502	19.40
13	0	BARRINGTON	2022	16133.0	215943.0	3416	19.15
14	34	WARWICK	2022	80999.0	131559.0	8615	18.73
15	29	SCITUATE	2022	10632.0	158723.0	1269	18.69
16	13	HOPKINTON	2022	8098.0	132630.0	1137	18.53
17	12	GLOCESTER	2022	10143.0	115913.0	553	18.44
18	6	CRANSTON	2022	81247.0	112955.0	10166	18.00
19	33	WARREN	2022	10488.0	135137.0	1240	17.72
20	22	NORTH KINGSTOWN	2022	26232.0	184128.0	3747	17.50
21	30	SMITHFIELD	2022	21693.0	141160.0	2378	17.13
22	25	PAWTUCKET	2022	71826.0	73520.0	8585	16.58
23	2	BURRILLVILLE	2022	16588.0	115511.0	2226	16.42
24	24	NORTH SMITHFIELD	2022	12413.0	147772.0	1653	16.35
25	26	PORTSMOUTH	2022	17362.0	221185.0	2295	15.31
26	7	CUMBERLAND	2022	34844.0	130235.0	4593	14.74
27	31	SOUTH KINGSTOWN	2022	30651.0	171698.0	2918	14.45
28	1	BRISTOL	2022	22150.0	145598.0	1923	14.38
29	32	TIVERTON	2022	15776.0	178651.0	1758	14.27
30	10	EXETER	2022	6665.0	152795.0	752	13.72
31	18	MIDDLETOWN	2022	16019.0	228318.0	2175	12.02
32	37	WESTERLY	2022	22543.0	291399.0	2683	11.52
33	21	NEWPORT	2022	24666.0	319868.0	2088	9.33
34	19	NARRAGANSETT	2022	15501.0	392972.0	1211	8.86
35	14	JAMESTOWN	2022	5493.0	487260.0	655	8.28
36	4	CHARLESTOWN	2022	7800.0	365102.0	740	8.18
37	20	NEW SHOREHAM	2022	916.0	1853433.0	148	6.70
38	17	LITTLE COMPTON	2022	3489.0	640834.0	344	6.04

Bar chart of residential tax rates

import matplotlib.pyplot as plt
import numpy as np

towns = rtrr_municipality
y = rtrr_residential_tax_rate

fy = get_fyear(final)
stitle = 'FY' + fy + ' Residential Tax Rate (dollars per thousand)'
fname = '../FY' + fy + '_Residential_Tax_Rates.png' 


plt.rcdefaults()
plt.rcParams['figure.figsize'] = [18, 10]
fig, ax = plt.subplots()

x_pos = np.arange(len(towns))
ax.bar(x_pos, y, align='center')
ax.set_yticks(np.arange(0,30,4))
ax.set_xticklabels(towns,rotation = 90,fontsize=14)
ax.set_xticks(np.arange(len(towns)))
ax.set_ylabel('Residential Tax Rate (dollars per thousand)',fontsize=24)
ax.set_title(stitle,fontsize=28)

fig.tight_layout()
#plt.show()
plt.savefig(fname)

/tmp/ipykernel_33004/1609785505.py:19: UserWarning: FixedFormatter should only be used together with FixedLocator
  ax.set_xticklabels(towns,rotation = 90,fontsize=14)