Skip to content
Advertisement

Python Pandas Column Formation

Is there an easy way to reformat the columns from

2000-01-03 Location1 A1 B1 C1 A2 B2 C2 A3 B3 C3  
2000-01-04 Location2 A1 B1 C1 A2 B2 C2 A3 B3 C3  
2000-01-05 Location3 A1 B1 C1 A2 B2 C2 A3 B3 C3

to

2000-01-03 Location1 A1 A2 A3 B1 B2 B3 C1 C2 C3  
2000-01-04 Location2 A1 A2 A3 B1 B2 B3 C1 C2 C3  
2000-01-05 Location3 A1 A2 A3 B1 B2 B3 C1 C2 C3 

Thanks

Advertisement

Answer

To reorder columns dynamically based on value, here is a way to do it:

df.sort_values(df.index[0], axis=1)

This returns a dataframe with columns (axis=1) ordered based on sorted value of first row.

Here is a full example using your data sample:

import pandas as pd
from io import StringIO

sample=StringIO('''date location x1 y1 z1 x2 y2 z2 x3 y3 z3
2000-01-03 Location1 A1 B1 C1 A2 B2 C2 A3 B3 C3
2000-01-04 Location2 A1 B1 C1 A2 B2 C2 A3 B3 C3
2000-01-05 Location3 A1 B1 C1 A2 B2 C2 A3 B3 C3''')

df = pd.read_csv(sample, sep=' ')
print(df)

df2 = df.sort_values(df.index[0], axis=1)
initialCols = ['date','location']
restCols = [col for col in df2.columns if col not in initialCols]
dfFinal = df2[initialCols + restCols]
dfFinal

enter image description here

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement