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.

  • $\begingroup$ If your dataset isn't toooooo large,then after creating the DF just save it into feather format, provided that you are not low on space for faster retrieval $\endgroup$
    – Aditya
    Commented Mar 6, 2018 at 21:00
  • $\begingroup$ Also you can just make the changes in place using lambda or map $\endgroup$
    – Aditya
    Commented Mar 6, 2018 at 21:10
  • $\begingroup$ Is there an advantage to making the changes in place vs adding a column to the DF that contains that info? $\endgroup$ Commented Mar 7, 2018 at 22:49

1 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
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()
ND    254008
SD       200
Name: Value, dtype: int64
  • $\begingroup$ So in this case you chose option two - the helper dictionary is the "lookup table." Any thoughts on what format you'd store that lookup table in if you weren't keeping it directly in the code? IE whats most efficient, csv/json/sql? $\endgroup$ Commented Mar 7, 2018 at 22:48
  • $\begingroup$ I would use one of those: HDF5, Feather, Parquet or SQL. I would definitely NOT use CSv - it's very slow and it looses information about data types, so they must be inferred eveytime we read it... $\endgroup$ Commented Mar 7, 2018 at 23:12
  • $\begingroup$ One advantage I thought for csv was ease of use - very easy for a non-coder to add a state and location number to the list for example when changes occurred. Its unlikely there would be more than a couple thousand rows in the lookup table, at least for this application. Thanks for your thoughts on this $\endgroup$ Commented Mar 8, 2018 at 3:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.