How to groupby, and filter a dataframe based on the sum?

Question

So I have a dataframe, milk_countries_exports, that consists of columns of:

The 'Period', the year and month for a particular row (the dataset is month by month for a year)
The 'Reporter' country, that is doing the exporting
The 'Partner' countries that are importing from the 'reporter'
The 'Commodity', which consists of 2 items, 'Milk and cream, neither concentrated nor sweetened', and 'Milk and cream, concentrated or sweetened'
The 'Commodity Code', which is the number assigned to the item types, so '0401' and '0402' respectively
'Trade Values (US$)', the amount of trade that took place for the given month, country, and commodity type for that row

The first few rows of the dataframe milk_countries_exports

I have been asked to:

Filter the dataset so that it only contains rows where the total exports across all the milk products for a particular country are at least two million dollars in any given monthly period. (HINT: group on partner and period and filter against a function that tests the minimum trade value exceeds the required value.)

To me that seems to mean for each given month and partner, if the sum of 0401 and 0402 for that month (e.g. 201401) and partner (e.g. Germany) is greater than 2 million dollars, keep those 2 rows , otherwise filter them out. So if for one month-partner pair you have the values 900,000 and 2,000,000 for 0401 and 0402 respectively, the sum is 2,900,000, so both rows are kept, even though one of them is less than the 2 million threshold, because it's going off the sum of 0401 and 0402, not their values individually.

As a reference point in the data I used the first few rows of 'Germany', because as a partner it contains examples of both 0401 and 0402 (many partners had only one or the other), and crucially the first month contains a case of 0401 being below the 2 million threshold at 982240, and an 0402 of 3187636, over the threshold. So, if one or both of these rows is missing after filtering, I know I've done something wrong.

The first few rows of partner Germany within the dataframe milk_countries_exports

I attempt to apply it in this way:

I define a function that tests if a dataset meets the criteria for being greater than or equal to 2 million:

def groupsWithExportsOver2million (g): 

    return g['Trade Value (US$)'].min() >= 2000000

Then:

milk_countries_exports[milk_countries_exports['Partner'] == 'Germany'].groupby(['Period', 'Partner']).filter(groupsWithExportsOver2million)

Which produces a completely blank dataframe, and:

milk_countries_exports[milk_countries_exports['Period'] == '201401'].groupby(['Period', 'Partner']).filter(groupsWithExportsOver2million)

Does the same. However:

milk_countries_exports.groupby(['Period', 'Partner']).filter(groupsWithExportsOver2million).sort_values('Trade Value (US$)')

(i.e. displaying the entire dataset not only displaying only one country or period group) produces a dataframe of 56 rows, which consists only of 'Trade Value (US$)' rows over 2 million, and I think but I'm not sure, it's filtered in such as way as to say if any partner country or any period value has anywhere in the dataframe a trade value of less than 2 million, that entire country or period value is eliminated from the resulting dataframe. Or, in other words leaving a table consisting of only countries and months for which every value of Trade Value in every instance was above 2 million.

I could combine the values for 0401 and 0402 for each country/period pair and test that sum against the filter as well, but that of course returns a dataframe where 0401 and 0402 are combined as one value, whereas in effect what I want to do is to be able to sum them, test the filter, then uncombine them so both 0401 and 0402 are still present separately.

How do I do what I'm trying to do?

Data:

# copy data and create dataframe with 
df = pd.read_clipboard(sep=',')

Period,Reporter,Partner,Commodity,Commodity Code,Trade Value (US$)
2014-09-01,United Kingdom,World,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,33279381
2014-09-01,United Kingdom,Australia,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,4558
2014-09-01,United Kingdom,Austria,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,290
2014-09-01,United Kingdom,Barbados,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,3016
2014-09-01,United Kingdom,Belgium,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,578297
2014-09-01,United Kingdom,Br. Virgin Isds,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,33746
2014-09-01,United Kingdom,Canada,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,34657
2014-09-01,United Kingdom,China,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,1547888
2014-09-01,United Kingdom,Cyprus,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,20199
2014-09-01,United Kingdom,Denmark,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,89685
2014-09-01,United Kingdom,Falkland Isds (Malvinas),Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,35560
2014-09-01,United Kingdom,Finland,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,141693
2014-09-01,United Kingdom,France,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,599127
2014-09-01,United Kingdom,Germany,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,587925
2014-09-01,United Kingdom,Gibraltar,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,29503
2014-09-01,United Kingdom,Greece,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,55
2014-09-01,United Kingdom,Guyana,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,74259
2014-09-01,United Kingdom,"China, Hong Kong SAR",Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,73082
2014-09-01,United Kingdom,Hungary,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,4141
2014-09-01,United Kingdom,Ireland,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,28619275
2014-09-01,United Kingdom,Italy,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,11989
2014-09-01,United Kingdom,Japan,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,3337
2014-09-01,United Kingdom,Lithuania,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,275
2014-09-01,United Kingdom,Malta,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,3756
2014-09-01,United Kingdom,"Other Asia, nes",Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,5040
2014-09-01,United Kingdom,Netherlands,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,406087
2014-09-01,United Kingdom,Portugal,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,3939
2014-09-01,United Kingdom,Qatar,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,10796
2014-09-01,United Kingdom,Saint Helena,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,3323
2014-09-01,United Kingdom,Singapore,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,10254
2014-09-01,United Kingdom,Viet Nam,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,10529
2014-09-01,United Kingdom,Slovenia,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,11
2014-09-01,United Kingdom,South Africa,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,8067
2014-09-01,United Kingdom,Spain,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,210956
2014-09-01,United Kingdom,Trinidad and Tobago,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,9680
2014-09-01,United Kingdom,United Arab Emirates,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,37348
2014-09-01,United Kingdom,United States of America,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,67035
2014-05-01,United Kingdom,World,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,21665661
2014-05-01,United Kingdom,Denmark,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,2311364
2014-05-01,United Kingdom,France,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,1638838
2014-05-01,United Kingdom,Germany,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,2199671
2014-05-01,United Kingdom,Italy,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,9950
2014-09-01,United Kingdom,World,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,27320918
2014-09-01,United Kingdom,Algeria,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,1659167
2014-09-01,United Kingdom,Angola,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,923506
2014-09-01,United Kingdom,Austria,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,443
2014-09-01,United Kingdom,Bangladesh,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,895167
2014-09-01,United Kingdom,Barbados,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,2027
2014-09-01,United Kingdom,Belgium,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,83721
2014-09-01,United Kingdom,Cameroon,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,83356
2014-09-01,United Kingdom,Sri Lanka,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,15332
2014-09-01,United Kingdom,China,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,1460603
2014-09-01,United Kingdom,Congo,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,673852
2014-09-01,United Kingdom,Cuba,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,849676
2014-09-01,United Kingdom,Cyprus,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,13521
2014-09-01,United Kingdom,Benin,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,112444
2014-09-01,United Kingdom,Denmark,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,63293
2014-09-01,United Kingdom,Dominican Rep.,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,535019
2014-09-01,United Kingdom,Finland,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,209
2014-09-01,United Kingdom,France,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,262833
2014-09-01,United Kingdom,Germany,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,2921594
2014-09-01,United Kingdom,Greece,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,52
2014-09-01,United Kingdom,Guinea,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,441798
2014-09-01,United Kingdom,Guyana,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,65483
2014-09-01,United Kingdom,"China, Hong Kong SAR",Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,1210921
2014-09-01,United Kingdom,Hungary,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,28794
2014-09-01,United Kingdom,Iraq,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,111141
2014-09-01,United Kingdom,Ireland,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,5498955
2014-09-01,United Kingdom,Italy,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,4204
2014-09-01,United Kingdom,Côte d'Ivoire,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,2322223
2014-09-01,United Kingdom,Jamaica,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,131078
2014-09-01,United Kingdom,Kuwait,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,623076
2014-09-01,United Kingdom,Lebanon,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,388137
2014-09-01,United Kingdom,Latvia,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,37
2014-09-01,United Kingdom,Malaysia,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,284043
2014-09-01,United Kingdom,Mali,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,220000
2014-09-01,United Kingdom,Malta,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,12190
2014-09-01,United Kingdom,Mauritania,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,73724
2014-09-01,United Kingdom,Mauritius,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,108406
2014-09-01,United Kingdom,Netherlands,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,979298
2014-09-01,United Kingdom,Nigeria,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,308385
2014-09-01,United Kingdom,Norway,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,5736
2014-09-01,United Kingdom,Pakistan,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,8151
2014-09-01,United Kingdom,Panama,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,142387
2014-05-01,United Kingdom,Lithuania,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,9354
2014-05-01,United Kingdom,Netherlands,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,664032
2014-05-01,United Kingdom,Portugal,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,912
2014-05-01,United Kingdom,Romania,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,225281
2014-05-01,United Kingdom,Slovakia,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,45434
2014-05-01,United Kingdom,Sweden,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,64547
2014-05-01,United Kingdom,United Arab Emirates,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,6742
2014-05-01,United Kingdom,Belgium,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,2707248
2014-05-01,United Kingdom,Ireland,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,11065926
2014-05-01,United Kingdom,Poland,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,716363
2014-10-01,United Kingdom,World,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,26615557
2014-10-01,United Kingdom,Austria,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,745
2014-10-01,United Kingdom,Belgium,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,609637
2014-10-01,United Kingdom,Bermuda,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,1439
2014-10-01,United Kingdom,Canada,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,27639
2014-10-01,United Kingdom,Sri Lanka,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,2230
2014-10-01,United Kingdom,China,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,1493672
2014-10-01,United Kingdom,Cyprus,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,5509
2014-10-01,United Kingdom,Denmark,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,72569
2014-10-01,United Kingdom,Falkland Isds (Malvinas),Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,15189
2014-10-01,United Kingdom,Finland,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,184498
2014-10-01,United Kingdom,France,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,658064
2014-10-01,United Kingdom,Germany,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,936431
2014-10-01,United Kingdom,Gibraltar,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,13367
2014-10-01,United Kingdom,Guatemala,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,200251
2014-10-01,United Kingdom,"China, Hong Kong SAR",Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,265362
2014-10-01,United Kingdom,Hungary,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,11
2014-10-01,United Kingdom,Ireland,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,21360068
2014-10-01,United Kingdom,Italy,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,12806
2014-10-01,United Kingdom,Japan,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,2127
2014-10-01,United Kingdom,Rep. of Korea,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,18549
2014-10-01,United Kingdom,Luxembourg,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,58
2014-10-01,United Kingdom,Malta,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,14189
2014-10-01,United Kingdom,Netherlands,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,337973
2014-10-01,United Kingdom,Portugal,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,3142
2014-10-01,United Kingdom,Singapore,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,8653
2014-10-01,United Kingdom,Viet Nam,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,10406
2014-10-01,United Kingdom,South Africa,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,7512
2014-10-01,United Kingdom,Spain,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,195426
2014-10-01,United Kingdom,United Arab Emirates,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,37325
2014-10-01,United Kingdom,Ukraine,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,2587
2014-10-01,United Kingdom,United States of America,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,118121
2014-07-01,United Kingdom,World,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,39508127
2014-07-01,United Kingdom,Afghanistan,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,4017
2014-07-01,United Kingdom,Austria,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,365
2014-07-01,United Kingdom,Bahrain,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,3348
2014-07-01,United Kingdom,Belgium,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,842327
2014-07-01,United Kingdom,Bermuda,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,2062
2014-07-01,United Kingdom,Br. Virgin Isds,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,39168
2014-07-01,United Kingdom,Canada,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,33987
2014-07-01,United Kingdom,Sri Lanka,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,3170
2014-07-01,United Kingdom,China,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,1178497
2014-07-01,United Kingdom,Cyprus,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,21465
2014-07-01,United Kingdom,Denmark,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,111562
2014-07-01,United Kingdom,Falkland Isds (Malvinas),Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,23319
2014-07-01,United Kingdom,Finland,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,135463
2014-07-01,United Kingdom,France,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,3606510
2014-07-01,United Kingdom,Germany,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,898346
2014-07-01,United Kingdom,Gibraltar,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,8565
2014-07-01,United Kingdom,Greece,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,1743
2014-07-01,United Kingdom,"China, Hong Kong SAR",Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,665185
2014-07-01,United Kingdom,Hungary,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,8635
2014-07-01,United Kingdom,Ireland,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,29943028
2014-07-01,United Kingdom,Italy,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,14349
2014-07-01,United Kingdom,Japan,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,7631
2014-07-01,United Kingdom,Rep. of Korea,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,2489
2014-07-01,United Kingdom,Malta,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,23238
2014-07-01,United Kingdom,Netherlands,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,1424401
2014-07-01,United Kingdom,Portugal,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,9260
2014-07-01,United Kingdom,Singapore,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,7865
2014-07-01,United Kingdom,Viet Nam,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,11085
2014-07-01,United Kingdom,South Africa,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,9104
2014-07-01,United Kingdom,Spain,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,311615
2014-07-01,United Kingdom,Sweden,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,792
2014-07-01,United Kingdom,Switzerland,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,1444
2014-07-01,United Kingdom,United Arab Emirates,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,25733
2014-07-01,United Kingdom,United States of America,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,128358
2014-06-01,United Kingdom,World,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,16022428
2014-06-01,United Kingdom,Belgium,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,1346169
2014-06-01,United Kingdom,Denmark,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,1972893
2014-06-01,United Kingdom,France,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,1449614
2014-06-01,United Kingdom,Germany,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,1856919
2014-06-01,United Kingdom,Ireland,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,7597407
2014-06-01,United Kingdom,Italy,Milk and cream; not concentrated nor containing added sugar or other sweetening matter,0401,8011
2014-09-01,United Kingdom,Poland,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,528311
2014-09-01,United Kingdom,Portugal,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,4418
2014-09-01,United Kingdom,Saint Lucia,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,11489
2014-09-01,United Kingdom,Saudi Arabia,Milk and cream; concentrated or containing added sugar or other sweetening matter,0402,715791

Error message from attempting solution:

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
 in 
----> 1 df_filtered = milk_countries_exports.groupby(['Partner', pd.Grouper(key='Period', freq='1M')]).filter(lambda x: x['Trade Value (US$)'].sum() > 2000000)

~\anaconda3\lib\site-packages\pandas\core\frame.py in groupby(self, by, axis, level, as_index, sort, group_keys, squeeze, observed)
   5808             group_keys=group_keys,
   5809             squeeze=squeeze,
-> 5810             observed=observed,
   5811         )
   5812 

~\anaconda3\lib\site-packages\pandas\core\groupby\groupby.py in __init__(self, obj, keys, axis, level, grouper, exclusions, selection, as_index, sort, group_keys, squeeze, observed, mutated)
    407                 sort=sort,
    408                 observed=observed,
--> 409                 mutated=self.mutated,
    410             )
    411 

~\anaconda3\lib\site-packages\pandas\core\groupby\grouper.py in get_grouper(obj, key, axis, level, sort, observed, mutated, validate)
    623                 in_axis=in_axis,
    624             )
--> 625             if not isinstance(gpr, Grouping)
    626             else gpr
    627         )

~\anaconda3\lib\site-packages\pandas\core\groupby\grouper.py in __init__(self, index, grouper, obj, name, level, sort, observed, in_axis)
    294             # check again as we have by this point converted these
    295             # to an actual value (rather than a pd.Grouper)
--> 296             _, grouper, _ = self.grouper._get_grouper(self.obj, validate=False)
    297             if self.name is None:
    298                 self.name = grouper.result_index.name

~\anaconda3\lib\site-packages\pandas\core\resample.py in _get_grouper(self, obj, validate)
   1409     def _get_grouper(self, obj, validate: bool = True):
   1410         # create the resampler and return our binner
-> 1411         r = self._get_resampler(obj)
   1412         r._set_binner()
   1413         return r.binner, r.grouper, r.obj

~\anaconda3\lib\site-packages\pandas\core\resample.py in _get_resampler(self, obj, kind)
   1402 
   1403         raise TypeError(
-> 1404             "Only valid with DatetimeIndex, "
   1405             "TimedeltaIndex or PeriodIndex, "
   1406             f"but got an instance of '{type(ax).__name__}'"

TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'Int64Index'

user_7758804 · Answer

g['Trade Value (US$)'].min() >= 2000000 filters everything out, because it means the minimum must be greater than 2000000.
Use pandas.Grouper to groupby Period with a specified frequency.
pandas.core.groupby.DataFrameGroupBy.filter to filter based on the sum of 'Trade Value (US$)'.
- x['Trade Value (US$)'].sum() > 2000000 is the filter function. It can be put into an external def function, but it's not necessary.
Commodity Code can also be added to the groupby:
- groupby(['Partner', 'Commodity Code', pd.Grouper(key='Period', freq='1M')])

import pandas as pd

# load the data
df = pd.read_csv('https://raw.githubusercontent.com/trenton3983/stack_overflow/master/data/so_data/2020-09-01%2063694704/comtrade.csv', dtype={'Commodity Code': str})

# select desired columns
df = df.loc[:, ['Period', 'Reporter', 'Partner', 'Commodity', 'Commodity Code', 'Trade Value (US$)']]

# convert Period to datetime format
df.Period = pd.to_datetime(df.Period, format='%Y%m')

# display(df.head(3))
      Period        Reporter    Partner                                                                               Commodity Commodity Code  Trade Value (US$)
0 2014-09-01  United Kingdom      World  Milk and cream; not concentrated nor containing added sugar or other sweetening matter           0401           33279381
1 2014-09-01  United Kingdom  Australia  Milk and cream; not concentrated nor containing added sugar or other sweetening matter           0401               4558
2 2014-09-01  United Kingdom    Austria  Milk and cream; not concentrated nor containing added sugar or other sweetening matter           0401                290

# groupby Partner and month, and filter by sum of Trade value > 2000000
df_filtered = df.groupby(['Partner', pd.Grouper(key='Period', freq='1M')]).filter(lambda x: x['Trade Value (US$)'].sum() > 2000000)

# verify the period Trade Value sums per partner per month are > 2000000
df_filtered.groupby(['Partner', pd.Grouper(key='Period', freq='1M')]).agg({'Trade Value (US$)': sum})

[out]:
                                 Trade Value (US$)
Partner              Period                       
Algeria              2014-01-31            4792662
                     2014-02-28            7220679
                     2014-03-31            9835523
                     2014-04-30           14875816
                     2014-05-31           19656679
                     2014-06-30           22411564
                     2014-07-31            3214364
                     2014-10-31            4074424
                     2014-11-30            2107597
                     2014-12-31            3464600
Angola               2014-03-31            2324977
                     2014-12-31            2030001
Belgium              2014-01-31           14531571
                     2014-02-28            6955784
                     2014-03-31            9576248
                     2014-04-30            8569745
                     2014-05-31            7635442
                     2014-06-30            5435766
                     2014-07-31            5128432
                     2014-08-31            5169545
                     2014-09-30            5707207
                     2014-10-31            4982965
                     2014-11-30            8547975
                     2014-12-31            5441072
China                2014-03-31            2460056
                     2014-07-31            2778780
                     2014-09-30            3008491
                     2014-10-31            4777912
                     2014-11-30            3774279
                     2014-12-31            3045122
China, Hong Kong SAR 2014-01-31            2170443
                     2014-07-31            2048469
                     2014-11-30            2049788
Côte d'Ivoire        2014-03-31            2842636
                     2014-06-30            2499308
                     2014-08-31            2173727
                     2014-09-30            2322223
Denmark              2014-01-31            2399943
                     2014-02-28            2136906
                     2014-03-31            2523950
                     2014-04-30            2523958
                     2014-05-31            2490132
                     2014-06-30            2191829
                     2014-07-31            3180516
                     2014-08-31            2497068
                     2014-09-30            3052401
                     2014-10-31            3019545
                     2014-11-30            2929672
                     2014-12-31            4497179
France               2014-01-31           12651302
                     2014-02-28           10284508
                     2014-03-31           14342231
                     2014-04-30           12846655
                     2014-05-31           12826328
                     2014-06-30           11756821
                     2014-07-31           13075198
                     2014-08-31            9966348
                     2014-09-30           10636585
                     2014-10-31           11120326
                     2014-11-30           10612800
                     2014-12-31            9512056
Germany              2014-01-31            9744449
                     2014-02-28            7688820
                     2014-03-31            8956210
                     2014-04-30           10604432
                     2014-05-31           10207829
                     2014-06-30           10104134
                     2014-07-31            7074641
                     2014-08-31            7768101
                     2014-09-30           12061074
                     2014-10-31           13060791
                     2014-11-30            8306606
                     2014-12-31            7132246
Ghana                2014-01-31            2389385
Guinea               2014-04-30            2098146
                     2014-05-31            2179330
Ireland              2014-01-31           57621249
                     2014-02-28           53529377
                     2014-03-31           52525722
                     2014-04-30           55134986
                     2014-05-31           57244611
                     2014-06-30           56814970
                     2014-07-31           52322023
                     2014-08-31           45421969
                     2014-09-30           51185200
                     2014-10-31           38818201
                     2014-11-30           37431831
                     2014-12-31           37494188
Lebanon              2014-07-31            2359805
Netherlands          2014-01-31           15376408
                     2014-02-28            9160546
                     2014-03-31           11064742
                     2014-04-30           15584558
                     2014-05-31           13182208
                     2014-06-30           14262841
                     2014-07-31           10843821
                     2014-08-31            7521907
                     2014-09-30            8164473
                     2014-10-31           13886896
                     2014-11-30           14965454
                     2014-12-31            6844463
Nigeria              2014-08-31            4676807
Poland               2014-09-30            2680608
                     2014-11-30            2694120
Spain                2014-01-31            2075305
                     2014-09-30            3185937
                     2014-10-31            2421800
                     2014-11-30            2318918
World                2014-01-31          139512730
                     2014-02-28          111789785
                     2014-03-31          131100878
                     2014-04-30          139406387
                     2014-05-31          144276262
                     2014-06-30          144420208
                     2014-07-31          117675469
                     2014-08-31          102032532
                     2014-09-30          117302843
                     2014-10-31          113368963
                     2014-11-30          106377174
                     2014-12-31           95273667
Yemen                2014-08-31            3311725

Resources

Comtrade Data Analysis - This is where I found out how to get the data
UN Comtrade Database - Data available here
- Type of Product: goods
- Frequency: monthly
- Periods: all of 2014
- Reporter: United Kingdom
- Partners: all
- Flows: imports and exports
- HS (as reported) commodity codes: 0401 (Milk and cream, neither concentrated nor sweetened) and 0402 (Milk and cream, concentrated or sweetened)
- Clicking on 'Preview' results in a message that the data exceeds 500 rows. Data was downloaded using the Download CSV button and the download file renamed appropriately.

How to groupby, and filter a dataframe based on the sum?

Data:

1 Answers

Resources