2

I have dataframe not sequences. if I use len(df.columns), my data has 3586 columns. How to re-order the data sequences?

ID  V1  V10 V100 V1000 V1001 V1002 ...  V990 V991 V992 V993 V994
A   1   9.0 2.9  0.0   0.0   0.0   0.0  0.0  0.0  0.0  0.0  0.0
B   1   1.2 0.1  3.0   0.0   0.0   0.0  1.0  0.0  0.0  0.0  0.0
C   2   8.6 8.0  2.0   0.0   0.0   0.0  2.0  0.0  0.0  0.0  0.0
D   3   0.0 2.0  0.0   0.0   0.0   0.0  3.0  0.0  0.0  0.0  0.0
E   4   7.8 6.6  3.0   0.0   0.0   0.0  4.0  0.0  0.0  0.0  0.0

I used this df = df.reindex(sorted(df.columns), axis=1) (based on this question Re-ordering columns in pandas dataframe based on column name) but still not working.

thank you

Arief
  • 897
  • 1
  • 8
  • 17
  • use `data.reindex_axis(sorted(data.columns, key=lambda x: float(x[1:])), axis=1)` – Nihal Apr 08 '19 at 09:38
  • its in the answer of the like you posted, and `sorted(df.columns)` will sort list based on string, not based on `int`, because you have `V` – Nihal Apr 08 '19 at 09:39
  • @Nihal - I got an error `could not convert string to float: 'ac'` – Arief Apr 08 '19 at 09:44
  • what is `ac`?? in your dataframe? can you please post the output of `print(list(df.columns))` – Nihal Apr 08 '19 at 09:56
  • @Nihal - I used `print(list(df.columns))` and the result is ID and all 'V' with number but not sequence. I don't know where the `ac` come from – Arief Apr 08 '19 at 10:09
  • @Arief - Edited answer, it should working with `ac` columns too. – jezrael Apr 08 '19 at 10:27

1 Answers1

3

First get all columns without pattern V + number by filtering with str.contains, then sorting all another values by Index.difference, add together and pass to DataFrame.reindex - get first all non numeric non matched columns in first positions and then sorted V + number columns:

L1 = df.columns[~df.columns.str.contains('^V\d+$')].tolist()

L2 = sorted(df.columns.difference(L1), key=lambda x: float(x[1:]))

df = df.reindex(L1 + L2, axis=1)
print (df)
   ID   V1  V10  V100  V990  V991  V992  V993  V994  V1000  V1001  V1002
A   1  9.0  2.9   0.0   0.0   0.0   0.0   0.0   0.0    0.0    0.0    0.0
B   1  1.2  0.1   3.0   1.0   0.0   0.0   0.0   0.0    0.0    0.0    0.0
C   2  8.6  8.0   2.0   2.0   0.0   0.0   0.0   0.0    0.0    0.0    0.0
D   3  0.0  2.0   0.0   3.0   0.0   0.0   0.0   0.0    0.0    0.0    0.0
E   4  7.8  6.6   3.0   4.0   0.0   0.0   0.0   0.0    0.0    0.0    0.0
jezrael
  • 729,927
  • 78
  • 1,141
  • 1,090