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
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
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
