My question is similar to Pandas: remove reverse duplicates from dataframe but I have an additional requirement. I need to maintain row value pairs.
For example:
I have data
where column A
corresponds to column C
and column B
corresponds to column D
.
import pandas as pd
# Initial data frame
data = pd.DataFrame({'A': [0, 10, 11, 21, 22, 35, 5, 50],
'B': [50, 22, 35, 5, 10, 11, 21, 0],
'C': ["a", "b", "r", "x", "c", "w", "z", "y"],
'D': ["y", "c", "w", "z", "b", "r", "x", "a"]})
data
# A B C D
#0 0 50 a y
#1 10 22 b c
#2 11 35 r w
#3 21 5 x z
#4 22 10 c b
#5 35 11 w r
#6 5 21 z x
#7 50 0 y a
I would like to remove duplicates that exist in columns A
and B
but I need to preserve their corresponding letter value in columns C
and D
.
I have a solution here but is there a more elegant way of doing this?
# Desired data frame
new_data = pd.DataFrame()
# Concat numbers and corresponding letters
new_data['AC'] = data['A'].astype(str) + ',' + data['C']
new_data['BD'] = data['B'].astype(str) + ',' + data['D']
# Drop duplicates despite order
new_data = new_data.apply(lambda r: sorted(r), axis = 1).drop_duplicates()
# Recreate dataframe
new_data = pd.DataFrame.from_items(zip(new_data.index, new_data.values)).T
new_data = pd.concat([new_data.iloc[:,0].str.split(',', expand=True),
new_data.iloc[:,1].str.split(',', expand=True)], axis=1)
new_data.columns=['A', 'B', 'C', 'D']
new_data
# A B C D
#0 0 a 50 y
#1 10 b 22 c
#2 11 r 35 w
#3 21 x 5 z
EDIT technically output should look like this:
new_data.columns=['A', 'C', 'B', 'D']
new_data
# A B C D
#0 0 a 50 y
#1 10 b 22 c
#2 11 r 35 w
#3 21 x 5 z
See Question&Answers more detail:
os