4
$\begingroup$

I have a groupby in jupyter-notebook that takes ages to run and after 10 minutes of running it says 'kernel died...',

The groupby looks like this:

df1.groupby(['date', 'unit', 'company', 'city'])['col1',
'col2',
'col3',
'col4',
  ...
'col20'].mean()

All of the 'col' columns are float values. I am running everything locally. Any ideas?

UPDATE:

The shape of df1 is:

(1360, 24)

Memory and dtypes:

dtypes: category(3), datetime64[ns](2), float64(17), int64(2)
memory usage: 266.9 KB

The unique size of city, date, company, unit:

len(df1.date.unique()) = 789
len(df1.unit.unique()) = 76
len(df1.company.unique()) = 205
len(df1.city.unique()) = 237

I have 16GB of memory on MacBook Pro.

UPDATE 2:

It works only if I have date and unit inside the groupby columns as the only 2 columns. If I add either a company or city, it doesn't work anymore, it keeps running indefinitely.

$\endgroup$
  • $\begingroup$ Some details that would help (us or you) to diagnose the problem are: 1. The size of df1 before you run this operation- both in terms of rows/columns and in terms of memory. 2. The cardinality (number of unique values) of date, unit, company, city 3. How much memory is available to the process. $\endgroup$ – Thomas Cleberg May 7 '19 at 14:09
  • $\begingroup$ Thanks, I updated my question $\endgroup$ – BlueIvy May 7 '19 at 14:16
  • $\begingroup$ Can you look at system monitor while it is running to see if you run out of memory? Just as a sanity check. $\endgroup$ – n1k31t4 May 7 '19 at 15:32
  • $\begingroup$ Yes, it says that Python3.7 takes 122GB of memory and all of my memory is being used. Usually I am using only 5GB out of 16GB of memory. I noticed that when I only have two columns in groupby date and unit that I get many NaN value rows and then I need to drop them to get the needed not-NaN value rows. That is strange. Perhaps, in this case, it is also creating NaN values, but not sure why is it happening. $\endgroup$ – BlueIvy May 7 '19 at 15:37
  • $\begingroup$ So you are basically running out of memory? You should be able to see in terminal, using a tool like htop, how much of the actual 16Gb is used. The 122 Gb measurement must include virtual memory. $\endgroup$ – n1k31t4 May 7 '19 at 15:53
1
$\begingroup$

I thought it might be because of the different types being used in the columns, but I created an example below, which works fine over mixed column types. The only real different is the size - that is why I think you are probably running out of memory.

Working example

I use int, str and datetime objects:

In [1]: import pandas as pd                                                                                                                                                                                                                          

In [2]: import datetime                                                                                                                                                                                                                              

In [3]: df = pd.DataFrame({'Branch': 'A A A A A A A B'.split(),
                           'Buyer': 'Carl Mark Carl Carl Joe Joe Joe Carl'.split(),
                           'Quantity': [1, 3, 5, 1, 8, 1, 9, 3],
                           'Date':[datetime.datetime(2013, 1, 1, 13, 0),
                                   datetime.datetime(2013, 1, 1, 13, 5),
                                   datetime.datetime(2013, 10, 1, 20, 0),
                                   datetime.datetime(2013, 10, 2, 10, 0), 
                                   datetime.datetime(2013, 10, 1, 20, 0),
                                   datetime.datetime(2013, 10, 2, 10, 0),
                                   datetime.datetime(2013, 12, 2, 12, 0),
                                   datetime.datetime(2013, 12, 2, 14, 0)]})                                                                                                                                                                                                                                          

In [4]: df                                                                                                                                                                                                                                           
Out[4]: 
  Branch Buyer  Quantity                Date
0      A  Carl         1 2013-01-01 13:00:00
1      A  Mark         3 2013-01-01 13:05:00
2      A  Carl         5 2013-10-01 20:00:00
3      A  Carl         1 2013-10-02 10:00:00
4      A   Joe         8 2013-10-01 20:00:00
5      A   Joe         1 2013-10-02 10:00:00
6      A   Joe         9 2013-12-02 12:00:00
7      B  Carl         3 2013-12-02 14:00:00


In [5]: df.shape                                                                                                                                                                                                                                    
Out[5]: (8, 4)

Now I just repeat the dataframe again, but add one hour to each of the datetime values, just to increase the number of groupby combinations to expect:

In [14]: df.iloc[0:8, 3] += datetime.timedelta(hours=1)                                                                                                                                                                                              

Now perform a groupby over all columns, and sum only on Quantity (it is my only numeric column).

The reuslts are as expected:

In [16]: df.groupby(["Branch", "Buyer", "Quantity", "Date"])["Quantity"].sum()                                                                                                                                                                       
Out[16]: 
Branch  Buyer  Quantity  Date               
A       Carl   1         2013-01-01 13:00:00    1
                         2013-01-01 14:00:00    1
                         2013-10-02 10:00:00    1
                         2013-10-02 11:00:00    1
               5         2013-10-01 20:00:00    5
                         2013-10-01 21:00:00    5
        Joe    1         2013-10-02 10:00:00    1
                         2013-10-02 11:00:00    1
               8         2013-10-01 20:00:00    8
                         2013-10-01 21:00:00    8
               9         2013-12-02 12:00:00    9
                         2013-12-02 13:00:00    9
        Mark   3         2013-01-01 13:05:00    3
                         2013-01-01 14:05:00    3
B       Carl   3         2013-12-02 14:00:00    3
                         2013-12-02 15:00:00    3
Name: Quantity, dtype: int64

Break your problem down

It might be difficult to break down your problem, because you need to whole data for the groupby operation. You could however save each of the groups to disk, perform the mean() computation on them separately and merge the results yourself. The name of each group is actually the combination of the groupby columns selected. This can be used to build the index of the reuslting dataframe.

It could look something like this:

for name, group in df1.groupby(['date', 'unit', 'company', 'city']):
    print("Processing groupby combination: ", name)    # This is the current groupby combination
    result = group.mean()
    _df = pd.DataFrame(index=[name], data=[result])
    _df.to_csv("path/somewhere/" + name + ".csv

You will then have a folder full of the results for each group and will have to just read them back in and combine them.

Other methods

It is known that Pandas does not handle many operations on huge datasets very efficienty (compared to e.g. the data.table package). There is the Dask package, which essentially does Pandas things in a distributed manner, but that might be overkill (and you'll of course need more resources!)

| improve this answer | |
$\endgroup$
  • $\begingroup$ Sounds reasonable, let me try it today or tomorrow morning, will get back to you as soon as I do. Thanks! $\endgroup$ – BlueIvy May 7 '19 at 16:22
  • $\begingroup$ Great, this worked! Thanks a lot for a detailed explanation. I didn't have to create CSV's at the end but I only appended to a dataframe in a loop. :) $\endgroup$ – BlueIvy May 8 '19 at 10:29
0
$\begingroup$

I bet that company and unit are category type?

I couldn't explain the underlying reason, but groupby doesn't like the category type.

Change your column type to 'object' and it will run in a couple of milliseconds without consuming any memory

| improve this answer | |
$\endgroup$
0
$\begingroup$

len(df1.date.unique()) = 789
len(df1.unit.unique()) = 76
len(df1.company.unique()) = 205
len(df1.city.unique()) = 237

That gives 2,913,350,940 possible combinations, yet you say your dataframe has only 1360 rows.

Try creating a column that is built from concatinating the four columns, then doing groupby that.

| improve this answer | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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