CODEWITHSUNDEEP
pythonpandas
Nick
Nick

Reputation: 39

Trouble transforming a dataframe (python, pandas)

Suppose I have the following data frame. I want to transform it in way where it is ordered by "Days Left". IDs can be used more than once, and all Report numbers are unique. But I want every ID to appear once, before an already used ID is used again.

Like, AABBCC -> ABCABC or AABCCC -> ABCACC.

How would I turn this.

ID Report Days Left
171 11 5
171 12 9
182 13 1
182 14 2

into this?

ID Report Days Left
182 13 1
171 11 5
182 14 2
171 12 9

Upvotes: 1

Views: 53

Answers (2)

ListenSoftware Louise Ai Agent
ListenSoftware Louise Ai Agent

Reputation: 4253

I liked the solution

ID=[171,171,182,182]
Report=[11,12,13,14]
DaysLeft=[5,9,1,2]

df=pd.DataFrame({'id':ID,'report':Report,'daysleft':DaysLeft})
df=df.sort_values(by="daysleft",ascending=True)\
.assign(order=lambda x: x.groupby("id").cumcount())\
.sort_values(by=['order','daysleft'])

output:
    id  report  daysleft  order
2  182      13         1      0
0  171      11         5      0
3  182      14         2      1
1  171      12         9      1

Upvotes: 0

Ian Wright
Ian Wright

Reputation: 346

cumcount should work

df.sort_values("Days Left") \
  .assign(order=lambda x: x.groupby("ID").cumcount()) \
  .sort_values(["order", "Days Left"]) \
  .drop(columns="order")

Upvotes: 2

Related Questions