Keep other column values constant after groupby and manipulating sub-dataframe

user_11901732 0
Viewed 133

I want to group dfx by Branch and Buyer, get the row where Quantity is middle value in each sub-dataframe, and return the final dataframe. How can I keep the corresponding value in the Date column as well?

dfx:

  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

Reproducible sample:

 dfx = 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)]
   })

Using:

grouped1 = dfx.groupby(['Branch', 'Buyer'])
grouped1['Quantity'].apply(lambda x: x.iloc[0])

returned:

Branch  Buyer
A       Carl     1
        Joe      8
        Mark     3
B       Carl     3
Name: Quantity, dtype: int64

but not keeping Date.

Expected output for middle value in Quantity:

  Branch Buyer  Quantity                Date
0      A  Carl         1 2013-01-01 13:00:00  
1      A  Mark         3 2013-01-01 13:05:00

4      A   Joe         8 2013-10-01 20:00:00

7      B  Carl         3 2013-12-02 14:00:00

For (A,Carl), there are only two unique values 1 and 5, but 1 appeared first so keeping this row. (A,Joe) has 8, 1, 9, so keeping the row with middle value which is 8. (A,Mark) and (B,Carl) has only one row in total so keeping the original data.

pandas dataframe group-by numpy python
2 Answers

Groupby Extended

To get the middle element of Quantity you can take the first and the last index and divide by 2 to get the middle index. Corresponding to the value of Quantity you will get the Date column just by specifying the column name i.e. Date after groupby as shown above.

user_136059950

Use custom function with get indices of difference with median and absolute values with Series.idxmin and then select rows by DataFrame.loc:

f = lambda x: x.sub(x.median()).abs().idxmin()
df = dfx.loc[dfx.groupby(['Branch', 'Buyer'], sort=False)['Quantity'].apply(f)]
print (df)
  Branch Buyer  Quantity                Date
0      A  Carl         1 2013-01-01 13:00:00
1      A  Mark         3 2013-01-01 13:05:00
4      A   Joe         8 2013-10-01 20:00:00
7      B  Carl         3 2013-12-02 14:00:00
user_29010020
Related