Sorting columns with strings in excel file [with python,pandas]

user_12188185 0
Viewed 247

I'm quite newbie with pandas and im looking for sorting and comparing columns, but i want to sort strings. I want to sort strings, and leave empty spaces like it was Do you have any ideas guys? I was wondering to do something with code below, but i dont know what to do next with that

import pandas as pd

df = pd.read_excel('test.xlsx')
print(df.query('a != b'))

INPUT: 
  a b
1 A C
2 B D
3 D 
4 C A

OUTPUT: 
  a b
1 A A
2 B 
3 D D
4 C C
pandas excel python
2 Answers

You need find the correct order of the series b using Series.map. Finally replace using Series.replace:

df['b']=df['a'].map(pd.Series(df.index,df['b'].values)).replace(df['b'])
print(df)

   a    b
0  A    A
1  B  NaN
2  D    D
3  C    C

if you want to order following the dictionary you need to order first based on the column a using DataFrame.sort_values:

df=df.sort_values('a')
df['b']=df['a'].map(pd.Series(df.index,df['b'].values)).replace(df['b'])
print(df)

   a    b
0  A    A
1  B  NaN
3  C    C
2  D    D

Also you can use DataFrame.merge with sort = False:

new_df=df['a'].to_frame().merge(df['b'].to_frame(),left_on='a',right_on='b',how='outer',indicator=True,sort=False).query('_merge!="right_only"').drop('_merge',axis=1)
print(new_df)

  a    b
0  A    A
1  B  NaN
2  D    D
3  C    C

or with sort = True

new_df=df['a'].to_frame().merge(df['b'].to_frame(),left_on='a',right_on='b',how='outer',indicator=True).query('_merge!="right_only"').drop('_merge',axis=1)
    print(new_df)

-----------------------

      a    b
    0  A    A
    1  B  NaN
    2  C    C
    3  D    D
user_118842370

Based on your example, it seems you just want to find out where there are no exact matches but retain the sort order. You can achieve by using this:

df['b'] = df['a'].where(df['a'].isin(df['b']), '')

>>> df

   a  b
0  A  A
1  B   
2  D  D
3  C  C

>>>> df.query('a != b')

   a  b 
1  B
user_91833440
Related