0

I have a excel sheet containing only "Employee id" in Column A as shown below.

1677
5597
5623
5618

I have one more excel sheet containing "Employee details" of 10000+ Employees. For Eg: The Employee details excel sheet contains contains data of lot of employee here is the example one of employee id shown below.

Empid   Name    Location    JobTitle    Email-id     Department
1677    Umesh     Gadag      ASE      abc@gmail.com    Civil

Here is the Working code

import pandas as pd
df1 = pd.read_excel (r'C:\\Users\\Kiran\\Desktop\\Employee id.xlsx',header=None)# excel sheet containing only ids
df2= pd.read_excel (r'C:\\Users\\Kiran\\Desktop\\Employee details.xlsx)# excel sheet containing all details of 10000+ employees
df3 = df2[df2['Empid'].isin(df1[0])]
df3.to_excel("Output1.xlsx",index=False)#Final output

The code works fine but i get output as Randomly

Empid   Name    Location    JobTitle    Email-id       Department
1677    Umesh     Gadag      ASE      abc@gmail.com      Civil
5623    Kiran     Hubli      SE       123@gmail.com      Civil
5618    Rudra     Bidar      ASE      xyz@gmail.com     Electrical
5597    Suresh    Udupi      ASE       ppp@gmail.com    Mechanical

But i need output in this sequence as follows, since the Employee id are in a particular order. Basically what is the order i gave in Employee id details i need Employee details in same sequence as shown below.

Empid   Name    Location    JobTitle    Email-id      Department
1677    Umesh     Gadag      ASE      abc@gmail.com     Civil
5597    Suresh    Udupi      ASE      ppp@gmail.com     Mechanical 
5623    Kiran     Hubli      SE       123@gmail.com     Civil
5618    Rudra     Bidar      ASE      xyz@gmail.com     Electrical
Kiran
  • 55
  • 1
  • 7
  • 1
    Does this answer your question? [Custom sorting in pandas dataframe](https://stackoverflow.com/questions/13838405/custom-sorting-in-pandas-dataframe) – RichieV Aug 02 '20 at 18:37

1 Answers1

2

Here's a solution:

df1 = df1.reset_index().rename(columns= {"index": "order"})
res = pd.merge(df1, df2, on = "Empid").sort_values("order").drop("order", axis=1)

The output is:

   Empid    Name Location JobTitle       Email-id  Department
0   1677   Umesh    Gadag      ASE  abc@gmail.com       Civil
1   5597  Suresh    Udupi      ASE  ppp@gmail.com  Mechanical
2   5623   Kiran    Hubli       SE  123@gmail.com       Civil
3   5618   Rudra    Bidar      ASE  xyz@gmail.com  Electrical
Roy2012
  • 11,755
  • 2
  • 22
  • 35
  • Great. Could you please accept my answer for future generations? (Click the grey check mark next to the answer, turning it to green). – Roy2012 Aug 02 '20 at 18:09