Menu
I am grouping my dataset by column A and then would like to take the minimum value in column B and the corresponding value in column C.
data = pd.DataFrame({'A': [1, 2], 'B':[ 2, 4], 'C':[10, 4]}) data A B C 0 1 4 3 1 1 5 4 2 1 2 10 3 2 7 2 4 2 4 4 5 2 6 6 and I would like to get :
A B C 0 1 2 10 1 2 4 4 For the moment I am grouping by A, and creating a value that indicates me the rows I will keep in my dataset:
a = data.groupby('A').min() a['A'] = a.index to_keep = [str(x[0]) + str(x[1]) for x in a[['A', 'B']].values] data['id'] = data['A'].astype(str) + data['B'].astype('str') data[data['id'].isin(to_keep)] I am sure that there is a much more straight forward way to do this. I have seen many answers here that use multi-indexing but I would like to do this without adding multi-index to my dataframe. Thank you for your help.
370
To select the rows, the syntax is df. loc[start:stop:step] ; where start is the name of the first-row label to take, stop is the name of the last row label to take, and step as the number of indices to advance after each extraction; for example, you can use it to select alternate rows.
To select a single value from the DataFrame, you can do the following. You can use slicing to select a particular column. To select rows and columns simultaneously, you need to understand the use of comma in the square brackets.
I feel like you're overthinking this. Just use groupby and idxmin:
df.loc[df.groupby('A').B.idxmin()] A B C 2 1 2 10 4 2 4 4 df.loc[df.groupby('A').B.idxmin()].reset_index(drop=True) A B C 0 1 2 10 1 2 4 4 Had a similar situation but with a more complex column heading (e.g. "B val") in which case this is needed:
df.loc[df.groupby('A')['B val'].idxmin()] If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
