CODEWITHSUNDEEP
pythonpandas
Murcielago
Murcielago

Reputation: 1005

pandas: modify a string value based on its rank in column

I am trying to modify the value of a string based on the rank of that string in the column. I am stuck and I can't find resources to troubleshoot.

Here is what the problem looks like:

    Id Supplier  Quantity
0  001      XXX        10
1  001      XYZ        12
2  002      XWA         9
3  002      XFF        15
4  003      WQE         6

and here is what I have been able to do alone (the easy part)

dataset = datatest.groupby('Id').apply(pd.DataFrame.sort_values,by='Quantity', ascending=False)

but after that I can't find a way to: - select the values in 'Id' that are not the first one - add the suffix '-S' at the end of each of them to get the end result like:

      Id Supplier  Quantity
0    001      XYZ        12
1  001-S      XXX        10
2    002      XFE        15
3  002-S      XWA         9
4    003      WQE         6

I get what I need to do but my underlying problem is that I don't what tool could help me do that operation

Upvotes: 0

Views: 57

Answers (2)

unltd_J
unltd_J

Reputation: 494

aList = dataset.Id
IDs = []
for i in aList[0:-1:2]:
    IDs.append(i+'-S')
dataset.Id = IDs

There's definitely a better way to do this, but this should do the trick if you're all you're looking to do is apply '-S' to every other element in your Id column

Upvotes: 0

Quang Hoang
Quang Hoang

Reputation: 150745

You don't need groupby for the easy part:

dataset = datatest.sort_values(['Id','Quantity'], ascending=[True, False])

dataset.loc[dataset.duplicated('Id'),'Id'] += '-S'

Output:

      Id Supplier Quantity
0    001      XXX       10
1  001-S      XYZ       12
3    002      XFF       15
2  002-S      XWA        9
4    003      WQE        6

Upvotes: 2

Related Questions