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.
