Create column based on another dataframe

Question

Good night! I have two dataframes ex8_alunos and ex8_cursos

ex8_alunos

ex8_cursos

I would like to create a column on ex8_alunos calling for CO_UF

To power this column I would need to cross-reference the data from the two dataframes mentioned above In the ex8_cursos use the CO_IES and the CO_UF to create in the ex8_alunos the column CO_UF and with the respective UF Codes of each faculty.

I tried the following method without result:

ex8_alunos['CO_UF'] = 0
for i in range (0, ex8_alunos.shape[0]):
    if ex8_alunos.at[i, "CO_IES"] == ex8_cursos["CO_IES"]: 
        ex8_alunos['CO_UF'] = ex8_alunos['CO_UF'].append(ex8_cursos["CO_IES"].loc[[i]])

Can someone help me? Thanks in advance!

Answer 1

The solution is to do the merge differently from what is in the commentary

Following example:

Creating Test Dataframes

>>> import pandas as pd
>>> df1 = pd.DataFrame({"A": [1, 2, 2, 3, 4, 3, 3, 2, 1, 5]})
>>> df2 = pd.DataFrame({"B": [1, 2, 3, 4, 5], "C": ["a", "b", "c", "d", "e"]})

The content of dataframes:

>>> df1
   A
0  1
1  2
2  2
3  3
4  4
5  3
6  3
7  2
8  1
9  5

>>> df2
   B  C
0  1  a
1  2  b
2  3  c
3  4  d
4  5  e

Merging

>>> df3 = pd.merge(df1, df2, left_on='A', right_on='B')

Content of df3

>>> df3
   A  B  C
0  1  1  a
1  1  1  a
2  2  2  b
3  2  2  b
4  2  2  b
5  3  3  c
6  3  3  c
7  3  3  c
8  4  4  d
9  5  5  e

For more details see documentation