python merge panda dataframes keep dates
Question: I'd like to merge two dataframes together but add in a column based on some
logic. A simplified example of my dataframes are below:

    
    
     DF_1:
     domain               ttl           nameserver               file_date
     fakedomain.com       86400         ns1.fakedomain.com       8/8/2008
     fakedomainz.com      86400         ns1.fakedomainz.com      8/8/2008
    
    
     DF_2:
     domain               ttl           nameserver               file_date
     fakedomain.com       86400         ns1.fakedomain.com       9/8/2008
     fakedomainz.com      86400         ns1.fakedomainz.com      9/8/2008
    

What I want to do is merge both of these dataframes into a single data frame
that looks like this:

    
    
     DF_2:
     domain               ttl           nameserver               first seen     last seen
     fakedomain.com       86400         ns1.fakedomain.com       8/8/2008       9/8/2008
     fakedomainz.com      86400         ns1.fakedomainz.com      8/8/2008       9/8/2008
    

I can't find a way to merge them and keep the dates. I also want to make sure
the dates are in the correct fields. Its important to note that I'm creating
the dates from regex pulled from the file names. I will also be running this
script continuously over time so the first seen date will only change when
something else changes e.g. the domain changes its name server.

The only way I can think of is to merge them with renamed date columns, then
loop over the entire dataframe sorting the dates appropriately but this seems
inefficient.


Answer: I am not sure which process produces these frames or whether it is a
continuous stream of new data, but to just comment on the substance of the
question, you could do like so:

    
    
    import pandas as pd
    from StringIO import StringIO
    
    s1="""
    domain               ttl           nameserver               file_date
    fakedomain.com       86400         ns1.fakedomain.com       8/8/2008
    fakedomainz.com      86400         ns1.fakedomainz.com      8/8/2008
    """
    
    s2="""
    domain               ttl           nameserver               file_date
    fakedomain.com       86400         ns1.fakedomain.com       9/8/2008
    fakedomainz.com      86400         ns1.fakedomainz.com      9/8/2008
    """
    

Then,

    
    
    df1 = pd.DataFrame.from_csv(StringIO(s1), sep='\s+',parse_dates=['file_date']  )
    df2 = pd.DataFrame.from_csv(StringIO(s2), sep='\s+',parse_dates=['file_date']  )
    

And assuming that you want the unique combination of `domain`, `ttl`, and
`nameserver`, along with first and last observation for that combination,
concatenate the frames and grab min and max dates.

    
    
    result = pd.concat([df1,df2]).reset_index().groupby(['domain','ttl','nameserver']).file_date.agg({'first_seen':'min','last_seen':'max'})
    

And finally:

    
    
    result
                                              first_seen last_seen
    domain          ttl   nameserver                              
    fakedomain.com  86400 ns1.fakedomain.com    8/8/2008  9/8/2008
    fakedomainz.com 86400 ns1.fakedomainz.com   8/8/2008  9/8/2008
    

