my groupby() does not work in pandas dataframe

Question

I have to make a group by very simple but it does not work in my case. I can not reproduce the actual data but suppose that my DF is:

              Cod          Cost        Date                 VAL
0            A123          123         2017-12-21           0.0
1            A123          123         2017-12-21          -2.0
2            A123          123         2017-12-21         -10.0
3            FB00          180         2016-12-11          80.0
4            FB00          180         2016-12-11          80.0

I have to make a groupby for Cod, Cost and Date and get the corresponding minimum of VAL with reset_index(). My code is:

DF = DF.groupby(['Cod', 'Cost','Date'])['VAL'].min().reset_index()

the expected is:

              Cod          Cost        Date                 VAL
0            A123          123         2017-12-21         -10.0
1            FB00          180         2016-12-11          80.0

but it return:

              Cod          Cost        Date                 VAL
0            A123          123         2017-12-21           0.0
1            A123          123         2017-12-21           0.0
2            A123          123         2017-12-21          80.0
3            FB00          180         2016-12-11          80.0
4            FB00          180         2016-12-11          80.0

I just used your code and it yields the expected, what versions are you using? (Pandas, Python) — Yuca, Feb 21 '19 at 14:23
my pandas version '0.18.1' e python version Python 3.5.2. How can I upgrade its, I have installed Anaconda — stefanodv, Feb 21 '19 at 14:54
Its working for me on this version i just updated the sam in my ans.. — Karn Kumar, Feb 21 '19 at 15:07
I tried to install pandas in many ways but always returns the error ' Proxyerror ' — stefanodv, Feb 21 '19 at 15:23
@stefanodv if you are in a corporate network, try doing the upgrade using a personal one. — anky, Feb 21 '19 at 16:44

score 1 · Answer 1 · answered Feb 21 '19 at 14:55

Like other contributors on here, you're code yields the expected results for me, but since this isn't the case for you allow me to propose another, though far less efficient, solution.

Starting by replicating your dataframe

df = pd.DataFrame([['A123', 123, '2017-12-21', 0], ['A123', 123, '2017-12-21', -2], ['A123', 123, '2017-12-21', -10], ['FB00', 180, '2016-12-11', 80], ['FB00', 180, '2016-12-11', 80]], columns=['Cod', 'Cost', 'Date', 'VAL'])

We can then sort the values by Cod, Cost, Date and VAL

df.sort_values(['Cod', 'Cost', 'Date', 'VAL'], ascending=True, inplace=True)

Output:

    Cod     Cost   Date     VAL
2   A123    123 2017-12-21  -10
1   A123    123 2017-12-21  -2
0   A123    123 2017-12-21  0
3   FB00    180 2016-12-11  80
4   FB00    180 2016-12-11  80

We can then drop duplicate Cod, Cost and Dates, keeping the first record (the lowest value), leaving us with your expected output.

df.drop_duplicates(['Cod', 'Cost', 'Date'], keep='first', inplace=True)
df.reset_index(drop=True, inplace=True)

With an output of:

    Cod     Cost    Date    VAL
0   A123    123 2017-12-21  -10
1   FB00    180 2016-12-11  80

Very hacky, not recommended, but should get you the result you expect.

Karn Kumar · Answer 2 · 2019-02-21T15:04:42.160

0

I just simulated your sample and its works for me:

Sample Example:

>>> df
    Cod  Cost        Date   VAL
0  A123   123  2017-12-21   0.0
1  A123   123  2017-12-21  -2.0
2  A123   123  2017-12-21 -10.0
3  FB00   180  2016-12-11  80.0
4  FB00   180  2016-12-11  80.0

Result:

>>> df.groupby(['Cod', 'Cost','Date'])['VAL'].min().reset_index()                                                                                                            Cod  Cost        Date   VAL
0  A123   123  2017-12-21 -10.0
1  FB00   180  2016-12-11  80.0

Applied on pandas: 0.23.3

Alternate:

You can also apply like below:

>>> df.groupby(['Cod', 'Cost','Date']).min().reset_index()
    Cod  Cost        Date   VAL
0  A123   123  2017-12-21 -10.0
1  FB00   180  2016-12-11  80.0

It should also work with 0.18.1 :

Python 3.5.1 (default, May 25 2016, 07:19:16)
[GCC 4.8.3] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pandas as pd
>>> pd.__version__
'0.18.1'

Sample

>>> df
    Cod  Cost        Date   VAL
0  A123   123  2017-12-21   0.0
1  A123   123  2017-12-21  -2.0
2  A123   123  2017-12-21 -10.0
3  FB00   180  2016-12-11  80.0
4  FB00   180  2016-12-11  80.0
>>> df.groupby(['Cod', 'Cost','Date']).min().reset_index()
    Cod  Cost        Date   VAL
0  A123   123  2017-12-21 -10.0
1  FB00   180  2016-12-11  80.0

OR

>>> df.groupby(['Cod', 'Cost','Date'])['VAL'].min().reset_index()
    Cod  Cost        Date   VAL
0  A123   123  2017-12-21 -10.0
1  FB00   180  2016-12-11  80.0

edited Feb 21 '19 at 15:04

answered Feb 21 '19 at 14:31

Karn Kumar

8,518
3
27
53

not my downvote but I do have to say this is not an answer – Yuca Feb 21 '19 at 14:33
yeah, there's nothing we can do about them. But at least now you know you're not alone in this! :) – Yuca Feb 21 '19 at 14:44
@Yuca, well said :-) , you made my day. – Karn Kumar Feb 21 '19 at 14:45
my pandas version '0.18.1' e python version Python 3.5.2. How can I upgrade its, I have installed Anaconda. – stefanodv Feb 21 '19 at 14:53
@stefanodv by the process mentioned [here](https://stackoverflow.com/questions/45197777/how-do-i-update-anaconda) – anky Feb 21 '19 at 14:56
@stefanodv, i think you can do it simply as `conda update pandas` – Karn Kumar Feb 21 '19 at 14:57
1

However @anky_91, provided you the link but even you can [look at here as well](https://stackoverflow.com/questions/37954195/upgrade-version-of-pandas/37966781) – Karn Kumar Feb 21 '19 at 14:59
1

@pygo yes, whatever serves the purpose. :) – anky Feb 21 '19 at 15:01
But i see the code provided by @stefanodv working well in in the pandas version `0.18.1` with Python 3.5.1 at least on my linux machine – Karn Kumar Feb 21 '19 at 15:06

my groupby() does not work in pandas dataframe

2 Answers2

Sample Example:

Result:

Alternate: