How to add columns from a data frame to another based on a common column with different values?

Question

I have two data frame both of them has a column"Country" common but in df1 it has 223 countries(means 223 rows) and in df2 it has only 188 countries(188 rows), both data frames have different columns values for ex.: df1 columns are(18) it contain the "Country", "Population", ....etc, and df2 column are(7) it contain "Country", "GNP", "Capital city",....etc.

I want to merge both data frames and match the values of columns in df2 according to the main column in df1 the "Country", so it adds the Population value from df2 for the exact "Country" that exists in df1 and a NaN for the one is missing. I have tried .join(), .merge(), and .concat(), none of these function gave me the required result.

Answer 1

The problem solved by arranging the csv files using Excel, made country column have 223 rows for both data frams, then I used

    df=pd.concat([df1,df2],axis=1)
    df=df.reset_index(drop=True)

I got the required results.

Answer 2

Did you try:

pd.merge(df1, df2, on="Country", how='outer)

Answer 3

For join(), did you make sure to select the right how= option?

By default, it is a left join, but maybe you need a outer, e.g.

df1.join(how='outer')

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.join.html