Most Efficient Post Processing with Python and Pandas
I'd do it this way:

helper dictionary:

In [79]: d = {'SD':[3], 'ND':[1,2,4]}


let's convert it to a Pandas Series:

In [80]: lkp = pd.Series({el:key for key,lst in d.items() for el in lst})

In [81]: lkp
Out[81]:
1    ND
2    ND
3    SD
4    ND
dtype: object


now we can map Location into State and group by it:

In [82]: df.assign(Location=df['Location'].map(lkp)).groupby('Location')['Value'].sum()
Out[82]:
Location
ND    254008
SD       200
Name: Value, dtype: int64