Pandas columns became sorted after appending to new dataframe

user_5100166 0
Viewed 139

I would like to append the row(s) that have unique values (excluding empty cells) from df1 to an empty df2. The structure of df1 is like this:

idx   A1     B1    C1    A2    B2    C2 
---------------------------------------
I1    n      n            b     c 
J1    x      x      x     x     x     x
K1    a      b      c     d     e     f

So in this case, row K1 will be appended to df2 because all the values are unique. To append the row, I tried

df2 = pd.DataFrame()
df2 = df2.append(df1.loc['K1'],sort=False)

I was expecting

idx   A1     B1    C1    A2    B2    C2 
---------------------------------------
K1    a      b      c     d     e     f

But actually I got

idx   A1     A2    B1    B2    C1    C2 
---------------------------------------
K1    a      d      b    e      c     f

Looks like the columns were sorted, even I set sort=False. I know I can hard code it as

df2 = df2.append({'idx':idx,'A1':df1.loc[idx,'A1'],'A2':df1.loc[idx,'A2'],'B1':df1.loc[idx,'B1']...}) to achieve what I want. But my actual file has 30+ columns and it's impossible to hard code every column.

pandas dataframe excel python
2 Answers

We can do the fix

df2 = df2.reindex(df1.columns, axis=1)
   A1 B1 C1 A2 B2 C2
K1  a  b  c  d  e  f

The sort = False in append is for row sort

user_79645270

You append a series to a dataframe, so it sorts the columns. Try to append on both dataframe to keep original columns order (note: append defaults sort to False, so you don't need specify it)

df_final = df2.append(df1.loc[['K1']])

Out[63]:
    A1 B1 C1 A2 B2 C2
idx
K1   a  b  c  d  e  f
user_101892140
Related