I am looking to create a dataframe "df3" based on two other dataframes: "df1" and "df2".
Say I have these dataframes:
I want to add the population from df2 onto df1 based on the ID and Year columns. Such that I have df3 which looks like this:
EDIT:
I have tried df3 = df1.merge(df2, on=['ID', 'Year'])
but this results in this error:
>
---------------------------------------------------------------------------
KeyError Traceback (most recent call last)
<ipython-input-29-882d6f907355> in <module>
----> 1 aps_all_pop = aps_all.merge(pop, on=['ID', 'Year'])
2 # Add Population
3
4 # Run a loop to find per 1000 figures
/opt/anaconda/envs/Python3/lib/python3.8/site-packages/pandas/core/frame.py in merge(self, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)
8193 from pandas.core.reshape.merge import merge
8194
-> 8195 return merge(
8196 self,
8197 right,
/opt/anaconda/envs/Python3/lib/python3.8/site-packages/pandas/core/reshape/merge.py in merge(left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)
72 validate=None,
73 ) -> "DataFrame":
---> 74 op = _MergeOperation(
75 left,
76 right,
/opt/anaconda/envs/Python3/lib/python3.8/site-packages/pandas/core/reshape/merge.py in __init__(self, left, right, how, on, left_on, right_on, axis, left_index, right_index, sort, suffixes, copy, indicator, validate)
666 self.right_join_keys,
667 self.join_names,
--> 668 ) = self._get_merge_keys()
669
670 # validate the merge keys dtypes. We may need to coerce
/opt/anaconda/envs/Python3/lib/python3.8/site-packages/pandas/core/reshape/merge.py in _get_merge_keys(self)
1044 right_keys.append(rk)
1045 if lk is not None:
-> 1046 left_keys.append(left._get_label_or_level_values(lk))
1047 join_names.append(lk)
1048 else:
/opt/anaconda/envs/Python3/lib/python3.8/site-packages/pandas/core/generic.py in _get_label_or_level_values(self, key, axis)
1682 values = self.axes[axis].get_level_values(key)._values
1683 else:
-> 1684 raise KeyError(key)
1685
1686 # Check for duplicates
KeyError: 'Year'
I would really appreciate any help!
Thanks