How to add the missing rows from one dataframe to another based on condition in Pandas?

user_11025208 0
Viewed 1462

My sample data is below:

data1 = {'index':  ['001', '001', '001', '002', '002', '003', '004','004'],
        'type' : ['red', 'red', 'red', 'yellow', 'red', 'green', 'blue', 'blue'],
        'class' : ['A', 'A', 'A', 'A', 'A', 'A', 'A', 'A']}
df1 = pd.DataFrame (data1, columns = ['index', 'type', 'class']) 
df1
    index   type    class
0   001     red     A
1   001     red     A
2   001     red     A
3   002     yellow  A
4   002     red     A
5   003     green   A
6   004     blue    A
7   004     blue    A

data2 = {'index':  ['001', '001', '002', '003', '004'],
        'type' : ['red', 'red', 'yellow', 'green', 'blue'],
        'class' : ['A', 'A', 'A', 'B', 'A']}
df2 = pd.DataFrame (data2, columns = ['index', 'type', 'class']) 
df2
    index   type    class   
0   001     red     A      
1   001     red     A      
2   002     yellow  A      
3   003     green   B      
4   004     blue    A

In df1, the class = A, in df2 it can be A, B or C. I want to add the missing rows in df2 from df1. df1 has the counts of types for each index. For example if in df1 index 001 appears 3 times it means I should also have it 3 times in df2. OUTPUT should be:

    index   type    class   
0   001     red     A       
1   001     red     A       
2   001     red     A       
3   002     yellow  A      
4   002     red     A       
5   003     green   A       
6   003     green   B       
7   004     blue    A       
8   004     blue    A

I tried with pd.concat and pd.merge but I kept getting duplicates or wrong rows added. Does someone have an idea of how to do this?

pandas python
1 Answers
  1. you can concat df1 with the records in df2 which are not in df1 : df2[~df2.isin(df1)].dropna()
  2. you then sort your values and reset_index

Long story short, you could do it in one line :

pd.concat([df1, df2[~df2.isin(df1)].dropna()]).sort_values(['index','type','class']).reset_index(drop=True)

Will give the following output:

    index   type    class
0   001     red     A
1   001     red     A
2   001     red     A
3   002     yellow  A
4   002     red     A
5   003     green   A
6   003     green   B
7   004     blue    A
8   004     blue    A
user_76525440
Related