python - Changing row order in pandas dataframe without losing or messing up data

Question

I have following dataframe:

(Index)    sample    reads yeasts    
9          CO ref    10
10         CO raai   20
11         CO tus    30

I want to change the order of the columns based on sample, expected output:

(Index)    sample    reads yeasts    
9          CO ref    10
11         CO tus    30
10         CO raai   10

I'm not interested in the Index of the rows.

I've tried following code based on other stackoverflow/google posts:

df=df.reindex(["CO ref","CO tus","CO raai"])

This correctly changes the index, but all the other columns get value nan

I've also tried:

df.index=["CO ref","CO tus","CO raai"]  

This changes the index correctly but the other columns do not switch so it messes up the dataframe.

Also:

df["sample"].index=["CO ref","CO tus","CO raai"]   

But this does nothing.

How can I get this to work?

Answer 1

For reindex is necessary create index from sample column:

df=df.set_index(['sample']).reindex(["CO ref","CO tus","CO raai"]).reset_index()

Or use ordered categorical:

cats = ["CO ref","CO tus","CO raai"]
df['sample'] = pd.CategoricalIndex(df['sample'], ordered=True, categories=cats)
df = df.sort_values('sample')