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.

  • 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 – Aditya Mar 6 at 21:00
  • Also you can just make the changes in place using lambda or map – Aditya Mar 6 at 21:10
  • Is there an advantage to making the changes in place vs adding a column to the DF that contains that info? – thesimplevoodoo Mar 7 at 22:49
up vote 0 down vote accepted

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
  • 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? – thesimplevoodoo Mar 7 at 22:48
  • 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... – MaxU Mar 7 at 23:12
  • 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 – thesimplevoodoo Mar 8 at 3:22

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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