Htydjtk

The dataframe contains

>> df

        A          B            C

A

196512  196512    1325  12.9010511000000

196512  196512  114569  12.9267705000000

196512  196512  118910  12.8983353775637

196512  196512  100688  12.9505091000000

196795  196795   28978  12.7805170314276

196795  196795   34591  12.8994111000000

196795  196795   13078  12.9135746000000

196795  196795   24173  12.8769653100000

196341  196341  118910  12.8983353775637

196341  196341  100688  12.9505091000000

196641  196641   28972  12.7805170314276

196641  196641   34591  12.8994111000000

196346  196341  118910  12.8983353775637

196346  196341  100688  12.9505091000000

196646  196641   28980  12.7805170314276

196646  196641   34591  12.8994111000000

I tried to get minimum value for each group and display using the following code,

df.columns = ['a','b','c']

df.index = df.a.astype(str)

dd=df.groupby('a').min()['c']

it gives the result

196512    12.7805170314276

196795    12.7805170314276

196341    12.7805170314276

196346    12.7805170314276

but after grouping, I want to get the row with the minimum 'c' value, grouped by column 'a' and display that full matching row in result
like,

196512    118910      12.8983353775637  

196795     28978      12.7805170314276

196341     28972      12.7805170314276

196346     28980      12.7805170314276

You can do this. But I doubt the efficiency.

>> import pandas as pd

>> df = pd.DataFrame({'a':[1,1,3,3],'b':[4,5,6,3], 'c':[1,2,3,5]})

>> df

   a  b  c

0  1  4  1

1  1  5  2

2  3  6  3

3  3  3  5

>> df[df['c'].isin(df.groupby('a').min()['c'].values)]

   a  b  c

0  1  4  1

2  3  6  3

In case this can help anyone else. Here is a solution that is more computationally efficient.

TL;DR version

If each row already has a unique index, then do this:

>>> df.loc[df.groupby('A')['C'].idxmin()]

If you've already indexed by 'A', then convert 'A' back into a column first.

>>> df2 = df.reset_index()

>>> df2.loc[df2.groupby('A')['C'].idxmin()]

Step by Step explanation:

Step 1.

First, make sure each row in your dataframe is uniquely indexed. This is the default when importing csv data. e.g.

    >>> df = pd.read_csv('questionData.csv'); df

        A       B       C

    0   196512  1325    12.901051

    1   196512  114569  12.926770

    2   196512  118910  12.898335

    3   196512  100688  12.950509

    4   196795  28978   12.780517

    5   196795  34591   12.899411

    6   196795  13078   12.913575

    7   196795  24173   12.876965

    8   196341  118910  12.898335

    9   196341  100688  12.950509

    10  196641  28972   12.780517

    11  196641  34591   12.899411

    12  196346  118910  12.898335

    13  196346  100688  12.950509

    14  196646  28980   12.780517

    15  196646  34591   12.899411

Aside: If you already converted column 'A' into an index, then you can turn the index back into a column (https://stackoverflow.com/questions/20461165/how-to-convert-pandas-index-in-a-dataframe-to-a-column) by doing: df.reset_index()

Step 2.

Use the pandas.DataFrame.idxmin function to retrieve the indices of the minimum of each group.

The semantics of the example below is this: "group by 'A', then just look at the 'C' column of each group, and finally return the index corresponding to the minimum 'C' in each group.

>>> indices = df.groupby('A')['C'].idxmin; indices

A

196341     8

196346    12

196512     2

196641    10

196646    14

196795     4

Name: C, dtype: int64

Step 3.

Finally, use the retrieved indices in the original dataframe using pandas.DataFrame.loc to get the rows of the original dataframe correponding to the minimum values of 'C' in each group that was grouped by 'A'.

>>> df.loc[indices]

    A       B       C

8   196341  118910  12.898335

12  196346  118910  12.898335

2   196512  118910  12.898335

10  196641  28972   12.780517

14  196646  28980   12.780517

4   196795  28978   12.780517

Note: The groupby('A') operation returns groups sorted by A. Thus 'indices' is sorted by A. If we want the original order, we just have to do

>>> df.loc[indices].sort_index()

    A       B       C

2   196512  118910  12.898335

4   196795  28978   12.780517

8   196341  118910  12.898335

10  196641  28972   12.780517

12  196346  118910  12.898335

14  196646  28980   12.780517

First check your data. You can't get value 12.780517 for index 196341. This value will be for index 196641.

Now, to get column 'b' in your result, use pd.merge. Don't make column 'a' as index. Leave it as it is.

>>> df = pd.DataFrame({'a':[196512, 196512, 196512, 196512, 196795, 196795, 196795, 196795, 196341, 196341, 196641, 196641, 196346, 196346, 196646, 196646],'b':[1325 , 114569 , 118910 , 100688 , 28978 ,34591 , 13078 ,  24173 , 118910 , 100688 , 28972 , 34591 , 118910 , 100688 , 28980 , 34591 ],'c':[12.9010511000000 ,12.9267705000000 ,12.8983353775637 ,12.9505091000000 ,12.7805170314276 ,12.8994111000000 ,12.9135746000000 ,12.8769653100000 ,12.8983353775637 ,12.9505091000000 ,12.7805170314276 ,12.8994111000000 ,12.8983353775637 ,12.9505091000000 ,12.7805170314276 ,12.8994111000000 ]})

>>> df1 = df.groupby(['a'])['c'].min()

>>> df1

a

196341    12.898335

196346    12.898335

196512    12.898335

196641    12.780517

196646    12.780517

196795    12.780517

Name: c, dtype: float64

df1 is a series type object here. Convert it to dataframe.

>>> df1 = pd.DataFrame(df1, columns = ['c'])

Also, make index of df1 as column 'a' and change index.

>>> df1['a'] = df1.index

>>> df1.index = range(df1.shape[0])

>>> df1

       c       a

0  12.898335  196341

1  12.898335  196346

2  12.898335  196512

3  12.780517  196641

4  12.780517  196646

5  12.780517  196795

Now you have to join this df1 with df on both columns 'a' and 'c'.

>>> pd.merge(df,df1, on = ['a','c'])

    a       b          c

0  196512  118910  12.898335

1  196795   28978  12.780517

2  196341  118910  12.898335

3  196641   28972  12.780517

4  196346  118910  12.898335

5  196646   28980  12.780517 

And you have your output as desired!

You can sort the dataFrame by count and then remove duplicates. I think it's easier:

df.sort_values('c').drop_duplicates(['a'])

Credit to Rani from StackOverflow