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