How to merge two CSV files into one using Python

Question

I have read some information about this on stack overflow, like this one Merging two CSV files using Python, but it seems like the strategy is not exactly working for me. I have two csv files that I want to merge, the first one look like this:

The second one look like this:

I want to merge them together and have them side by side, look something like this:

This is what my code looks like:

# Find first school csv file in file explore
file1 = file1_path_in_folder
df1 = pd.read_csv(file1)
df1.head()

# Find second school csv file in file explore
file2 = file2_path_in_folder
df2 = pd.read_csv(file2)
df2.head()

merged = df1.merge(df2, on=' ') # The space in quotation marks is there because the first cell (A1) is blank
merged.to_csv("output.csv", index=False)

I just end up getting an error that look like this:

Traceback (most recent call last):
  File "c:\Users\anyuy\Documents\College_search\program\compare_read_csv.py", line 25, in <module>    
    merged = df1.merge(df2, on=' ')
  File "C:\Users\anyuy\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\pandas\core\frame.py", line 9345, in merge
    return merge(
  File "C:\Users\anyuy\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\pandas\core\reshape\merge.py", line 107, in merge        
    op = _MergeOperation(
  File "C:\Users\anyuy\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\pandas\core\reshape\merge.py", line 700, in __init__     
    ) = self._get_merge_keys()
  File "C:\Users\anyuy\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\pandas\core\reshape\merge.py", line 1097, in _get_merge_keys
    right_keys.append(right._get_label_or_level_values(rk))
  File "C:\Users\anyuy\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\pandas\core\generic.py", line 1840, in _get_label_or_level_values
    raise KeyError(key)
KeyError: ' '

I tried taking out the space in the quotation marks, it still throws the same error. What did I do wrong and how should I fix it? Thanks for all the help out there.

If the first line is blank you should remove it before processing. — monkut
– monkut, Commented Aug 17, 2022 at 2:21

Lokeshwar G · Accepted Answer · 2022-08-17 05:09:14Z

3

You can merge two csv files using pandas DataFrame.

df3 = pd.concat([df1,df2], axis=1, join='inner')

Try this may work!

answered Aug 17, 2022 at 5:09

Lokeshwar G

1466 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to merge two CSV files into one using Python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related