How can I do a string object comparison between two csv files in Pandas (Python 2.7) and generate a new dataframe or csv file

Question

I am reading two .csv files, "CSV_1" & "CSV_2". For the most part, they have different columns and data besides the columns I will be using as my string key.

The elements per row in the four columns (basically my key) need to be identical in both in order to concatenate the data from CSV_2 to the end of each row that matches in CSV_1.

The four columns in both csv files are "Date" (format: Month/Day/Year), "Hour" (format: 0-23), "Make", & "Model" and are str obj datatypes.

It would basically read the 1st row in csv_1 and take those four factors, then look through CSV_2 and find any instance the elements of those four columns. Once it finds a match in both, I want to take all the columns in CSV_1 and CSV_2 and concatenate the columns into a new dataframe without duplicating the "Date", "Hour", "Car", & "Model" columns since they would be identical in both. Isuppose I could delete the duplicate columns from the new dataframe.

I am sure there will be a few instances where there wont be any matches, I would still need that data as well, so I guess I would need to fillna or something to just generate blank cells at the end of the row from CSV_1 for each column added from CSV_2 before adding the row to the new dataframe.

I generated fake data for the example, but it should do something like this with the outputs (except the data is read from the two csv files, I cannot provide a snippet for the actual code)

CSV_1:

import pandas as pd
from pandas import DataFrame

date = ['5/10/2012', '10/17/2012', '1/2/2013', '5/3/2014']
hr = ['1', '0', '23', '13']
make = ['Honda', 'Toyota', 'Chevy', 'Honda']
model = ['Accord', 'Camry', 'Sonic', 'Civic']
gas = ['9', '9', '7','8']
safe = ['8', '10', '6','7']


dataSet = zip(date, hr, make, model, gas, safe)
df = pd.DataFrame(data = dataSet, columns=['Date', 'Hour', 'Make', 'Model', 'Gas Rating', 'Safety Rating'])
>>>df

CSV_2:

make2 = ['Honda', 'Toyota', 'Honda']
model2 = ['Accord', 'Camry', 'Civic']
mile = ['10', '10','9']
speed = ['7', '7', '6']

dataSet2 = zip(date, hr, make2, model2, mile, speed)
df2 = pd.DataFrame(data = dataSet2, columns=['Date', 'Hour', 'Make', 'Model', 'Mileage Rating', 'Speed Rating'])
>>>df2

Here is where the string key comparison should come into play and basically give me the output of the following code (The headers will always be the same, but the amount of data will not, there are actually close to 100+ columns in both)

Final_df:

date = ['5/10/2012', '10/17/2012', '1/2/2013', '5/3/2014']
hr = ['1', '0', '23', '13']
make = ['Honda', 'Toyota', 'Chevy', 'Honda']
model = ['Accord', 'Camry', 'Sonic', 'Civic']
gas = ['9', '9', '7', '8']
safe = ['8', '10', '6', '7']
mile = ['10', '10', ' ','9']
speed = ['7', '7', ' ', '6']

dataSet3 = zip(date, hr, make, model, gas, safe, mile, speed)
df3 = pd.DataFrame(data = dataSet3, columns=['Date', 'Hour', 'Make', 'Model', 'Gas Rating', 'Safety Rating', 'Mileage Rating', 'Speed Rating'])
>>>df3     

Answer 1

Initially, you requested joining on 4 different columns (Make, Model, Date, Hour), but second table really only had two matching (Make, Model).

Outer join will still apply, but the original answer is correct:

In [8]: df
Out[8]: 
         Date Hour    Make   Model Gas Rating Safety Rating
0   5/10/2012    1   Honda  Accord          9             8
1  10/17/2012    0  Toyota   Camry          9            10
2    1/2/2013   23   Chevy   Sonic          7             6
3    5/3/2014   13   Honda   Civic          8             7

In [9]: df2
Out[9]: 
         Date Hour    Make   Model Mileage Rating Speed Rating
0   5/10/2012    1   Honda  Accord             10            7
1  10/17/2012    0  Toyota   Camry             10            7
2    1/2/2013   23   Honda   Civic              9            6

In [10]: final = pd.merge(df,df2, how='outer', on=['Date', 'Hour', 'Make', 'Model'])

In [11]: final
Out[11]: 
         Date Hour    Make   Model Gas Rating Safety Rating Mileage Rating  \
0   5/10/2012    1   Honda  Accord          9             8             10   
1  10/17/2012    0  Toyota   Camry          9            10             10   
2    1/2/2013   23   Chevy   Sonic          7             6            NaN   
3    5/3/2014   13   Honda   Civic          8             7            NaN   
4    1/2/2013   23   Honda   Civic        NaN           NaN              9   

  Speed Rating  
0            7  
1            7  
2          NaN  
3          NaN  
4            6  

In [12]: final.fillna(0, inplace=True)

In [13]: final
Out[13]: 
         Date Hour    Make   Model Gas Rating Safety Rating Mileage Rating  \
0   5/10/2012    1   Honda  Accord          9             8             10   
1  10/17/2012    0  Toyota   Camry          9            10             10   
2    1/2/2013   23   Chevy   Sonic          7             6              0   
3    5/3/2014   13   Honda   Civic          8             7              0   
4    1/2/2013   23   Honda   Civic          0             0              9   

  Speed Rating  
0            7  
1            7  
2            0  
3            0  
4            6