[Python] Python merge multiple dataframes out of memory and chunk them while preserving...

Say I have a multindex dataframe with index lev1,lev2,lev3 that is created by merging 3 datasets with indexes [lev1], [lev1,lev2] and [lev1,lev2,lev3]. Each dataframe is a subset of the other (all lev1 levels in df2 or df3 are in df1), and all levels are sorted on each dataframe and monotonically increasing. Indexes are either integer or object datatype holding sorted strings. The merged dataframe is too big to fit into memory. I want to pass a target memory size, say 100mb , and have a function return list[pd.Dataframe] = df_chunked[df_chunk_1, ...,df_chunk_n] where each of the chunks is less than 100mb and none of the chunks have indexes split across the boundaries.( or it could calculate offsets that I can use as input to construct each chunk)

This function should work for columns of different datatypes, including object and pandas categorical variables

base case example:

df_components=[df1,df2,df3] # number of merged data frames is arbitrary here, 3 is example
# also can assume df are in order of increasing number of indexes

df_chunklist = chunkdf(df_components,target_size="100mb") # can also just be bytesize as int if easier
-> [df_1,df_2,df_3,df_4,df_5] # total dataset size 284mb, returns 4 chunks so each chunk < 100mb


I want to make sure something doesn't already exist before I re-implement the wheel. If I have to do it myself, my thought process is to query the size of each column on each dataframe (or maybe total size of each df), then merge just the index so I know how many duplicate rows I will get for the lower level indexes, then adjust the size by the relative ratio of the rows in each sub dataframe. Then I could chunk df1 based on the row offsets I calculated, and merge the other full dataframes onto each chunk of df1. I already see a problem with this for datatypes that don't scale linearly based on row counts though, like if I had categorical data with a lot of categories relative to small number of chunked rows.

Continue reading...