Interleave 2 Dataframes on certain columns

Question

I have 2 dataframes

df1:

StartLocation,StartDevice,StartPort,EndLocation,EndDevice,EndPort,LinkType,Speed 
DD1,Switch1,P1,AD1,Switch2,P2,MTP,1000
DD2,Switch2,P3,AD2,Switch3,P2,MTP,1000
DD3,Switch3,P5,AD3,Switch4,P6,MTP,1000

df2:

StartLocation,StartDevice,StartPort,EndLocation,EndDevice,EndPort
AB11,RU15,P1,AJ11,RU25,P2
AB12,RU18,P2,AB11,RU35,P2
AB13,RU19,P3,AB11,RU40,P4

I want to interleave the two dataframes and I've tried a few options but can't seem to get it to work. I'm close to the functionality with the below code but it doesn't join on the appropriate columns

import pandas as pd
from toolz import interleave

df3 = pd.DataFrame(interleave([df1.values, df2.values]), columns=df1)

Expected Output would look like

StartLocation,StartDevice,StartPort,EndLocation,EndDevice,EndPort,LinkType,Speed 
DD1,Switch1,P1,AD1,Switch2,P2,MTP,1000
AB11,RU15,P1,AJ11,RU25,P2,nan,nan
DD2,Switch2,P3,AD2,Switch3,P2,MTP,1000
AB12,RU18,P2,AB11,RU35,P2,nan,nan
DD3,Switch3,P5,AD3,Switch4,P6,MTP,1000
AB13,RU19,P3,AB11,RU40,P4,nan,nan

I think it should be pretty simple but I can't find the appropriate syntax. Can anyone give any ideas?

Thanks in advance for the help!

please provide an expected output – luigigi Feb 14 '20 at 06:49 — luigigi, Feb 14 '20 at 06:49

jezrael · Accepted Answer · 2020-02-14T07:06:26.940

If columns names are same, only difference is some new columns names in one of DataFrame is possible use:

df3 = pd.DataFrame(interleave([df1.values, df2.values]), columns=df1.columns)
print (df3)
  StartLocation StartDevice StartPort EndLocation EndDevice EndPort LinkType  \
0           DD1     Switch1        P1         AD1   Switch2      P2      MTP   
1          AB11        RU15        P1        AJ11      RU25      P2     None   
2           DD2     Switch2        P3         AD2   Switch3      P2      MTP   
3          AB12        RU18        P2        AB11      RU35      P2     None   
4           DD3     Switch3        P5         AD3   Switch4      P6      MTP   
5          AB13        RU19        P3        AB11      RU40      P4     None   

    Speed  
0  1000.0  
1     NaN  
2  1000.0  
3     NaN  
4  1000.0  
5     NaN

More general solution working for any columns names is use DataFrame.align before for prevent correct align columns for each DataFrame:

print (df1)
  EndDevice EndLocation EndPort LinkType  Speed StartDevice StartLocation  \
0   Switch2         AD1      P2      MTP   1000     Switch1           DD1   
1   Switch3         AD2      P2      MTP   1000     Switch2           DD2   
2   Switch4         AD3      P6      MTP   1000     Switch3           DD3   

  StartPort  
0        P1  
1        P3  
2        P5  

print (df2)
  EndDevice EndLocation EndPort  LinkType  Speed StartDevice StartLocation  \
0      RU25        AJ11      P2       NaN    NaN        RU15          AB11   
1      RU35        AB11      P2       NaN    NaN        RU18          AB12   
2      RU40        AB11      P4       NaN    NaN        RU19          AB13   

  StartPort  
0        P1  
1        P2  
2        P3

df3 = pd.DataFrame(interleave([df1.values, df2.values]), columns=df1.columns)
print (df3)
  EndDevice EndLocation EndPort LinkType   Speed StartDevice StartLocation  \
0   Switch2         AD1      P2      MTP  1000.0     Switch1           DD1   
1      RU25        AJ11      P2      NaN     NaN        RU15          AB11   
2   Switch3         AD2      P2      MTP  1000.0     Switch2           DD2   
3      RU35        AB11      P2      NaN     NaN        RU18          AB12   
4   Switch4         AD3      P6      MTP  1000.0     Switch3           DD3   
5      RU40        AB11      P4      NaN     NaN        RU19          AB13   

  StartPort  
0        P1  
1        P1  
2        P3  
3        P2  
4        P5  
5        P3

Another idea with Index.union and DataFrame.reindex:

cols = df1.columns.union(df2.columns, sort=False)

df1 = df1.reindex(cols, axis=1)
df2 = df2.reindex(cols, axis=1)
print (df1)
  StartLocation StartDevice StartPort EndLocation EndDevice EndPort LinkType  \
0           DD1     Switch1        P1         AD1   Switch2      P2      MTP   
1           DD2     Switch2        P3         AD2   Switch3      P2      MTP   
2           DD3     Switch3        P5         AD3   Switch4      P6      MTP   

   Speed  
0   1000  
1   1000  
2   1000  

print (df2)
  StartLocation StartDevice StartPort EndLocation EndDevice EndPort  LinkType  \
0          AB11        RU15        P1        AJ11      RU25      P2       NaN   
1          AB12        RU18        P2        AB11      RU35      P2       NaN   
2          AB13        RU19        P3        AB11      RU40      P4       NaN   

   Speed  
0    NaN  
1    NaN  
2    NaN

df3 = pd.DataFrame(interleave([df1.values, df2.values]), columns=cols)
print (df3)
  StartLocation StartDevice StartPort EndLocation EndDevice EndPort LinkType  \
0           DD1     Switch1        P1         AD1   Switch2      P2      MTP   
1          AB11        RU15        P1        AJ11      RU25      P2      NaN   
2           DD2     Switch2        P3         AD2   Switch3      P2      MTP   
3          AB12        RU18        P2        AB11      RU35      P2      NaN   
4           DD3     Switch3        P5         AD3   Switch4      P6      MTP   
5          AB13        RU19        P3        AB11      RU40      P4      NaN   

    Speed  
0  1000.0  
1     NaN  
2  1000.0  
3     NaN  
4  1000.0  
5     NaN

Problem sorted. Thanks so much Jezrael!. You're a legend! – Mors Feb 14 '20 at 07:12 — Mors, Feb 14 '20 at 07:12

Interleave 2 Dataframes on certain columns

1 Answers1