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!)
df1
before you run this operation- both in terms of rows/columns and in terms of memory. 2. The cardinality (number of unique values) ofdate
,unit
,company
,city
3. How much memory is available to the process. $\endgroup$htop
, how much of the actual 16Gb is used. The 122 Gb measurement must include virtual memory. $\endgroup$