Most Efficient Post Processing with Python and Pandas

Question

This question is about best practices for working in Pandas dataframes. Speed, ease of use, and memory consumed could all impact any answers you might have. I start by pulling a data set into a dataframe like this:

Date    Location    Value
3/4/2018    1        4795
3/5/2018    1        4795
3/4/2018    2        5022
3/5/2018    2        5088
3/4/2018    3        100
3/5/2018    3        100
3/4/2018    4        117154
3/5/2018    4        117154

I would like to sum the Value based on some other criteria. For this example, lets use two states, SD and ND. Location 1,2,4 are in ND, and Location 3 is in SD. As I see it, I have two options:

1. Have Pandas post process the location numbers. IE Pythonicly: ND = Sum(Loc 1,2,4), SD = Sum(Loc 4). Then build/pivot the time series based on state

2. Build a lookup table and have Pandas append the state to each row in the dataframe. Then filter/group by state for the time series. Lookup table would look like so:

   Location    State
   1           ND
   1           ND
   2           ND
   2           ND
   3           SD
   3           SD
   4           ND
   4           ND
   

In option one, would Pandas add a new row to the dataframe for each day of the timeseries with the state totals? Or would the output be a pivot table like structure with only the state totals.

If option two, what would be the best type of way to host the lookup table? CSV? JSON? Table in SQL DB?

I'm concerned in option one changes would need to be made directly to the code. Whereas in option two an addition to the lookup table could add the information required to add location to the correct group.

While I know this is open ended, I hope someone is willing to provide thoughts on efficient structure for this type of data flow.

Answer 1

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