How to get unique information from multiple columns of a pandas dataframe?

user_3590067 0
Viewed 173

I have a dataframe df like the following

  Name1      Name2  ID1  ID2
0    John    Jack    3    2
1    John  Albert    3    0
2    Jack     Eva    2    1
3  Albert    Sara    0    4
4     Eva    Sara    1    4

I would like a two-column dataframe df1 with the ID of each Name

df1
     Name     ID
0    Albert   0
1    Eva      1
2    Jack     2
3    John     3
4    Sara     4
pandas python
3 Answers

You can use pd.wide_to_long along with DataFrame.drop_duplicates to get the unique values:

(pd.wide_to_long(df.reset_index(), stubnames=['Name','ID'], i='index', j='ix')
  .drop_duplicates().reset_index(drop=True))

     Name  ID
0    John   3
1    Jack   2
2  Albert   0
3     Eva   1
4    Sara   4

Details

pd.wide_to_long will give you a two column dataframe (Name and ID), using the specified stubmanes:

x = pd.wide_to_long(df.reset_index(), stubnames=['Name','ID'], i='index', j='ix')
            Name  ID
index ix            
0     1     John   3
1     1     John   3
2     1     Jack   2
3     1   Albert   0
4     1      Eva   1
0     2     Jack   2
1     2   Albert   0
2     2      Eva   1
3     2     Sara   4
4     2     Sara   4

Now you only need to drop_duplicates to get the unique values for the Name-ID combination:

     Name  ID
0    John   3
1    Jack   2
2  Albert   0
3     Eva   1
4    Sara   4
user_96986840

Use:

v = df[['Name1','Name2']].values.ravel()
a, b = pd.factorize(v)

df = pd.DataFrame({'Name': b[a], 'ID':a}).drop_duplicates()
print (df)
     Name  ID
0    John   0
1    Jack   1
3  Albert   2
5     Eva   3
7    Sara   4
user_29010020 
df1=df[['ID1','Name1']].copy()
df1.rename(columns={'ID1':'ID','Name1':'Name'},inplace=True)
df2=df[['ID2','Name2']].copy()
df2.rename(columns={'ID2':'ID','Name2':'Name'},inplace=True)

new_df=pd.concat([df1,df2])
new_df.drop_duplicates(inplace=True)
new_df.sort_values(['ID'],inplace=True)
new_df.reset_index(drop=True,inplace=True)

new_df.head()

Obviously, @yatu and @jezrael's solution is more advanced and I learned new API too. my solution is straight forward and maybe easier to understand.

    ID  Name
0   0   Albert
1   1   Eva
2   2   Jack
3   3   John
4   4   Sara
user_66583070
Related