How to Add Column from Another Dataframe in Pandas Python [6 Methods]

Do you want to add a column from another dataframe? In this Pandas blog, Let me tell many different ways to “Add column from another dataframe in Pandas Python” with the help of some examples.

When I was working with some dataframes, it was tough to manually add the same column to different dataframes, so I researched and found six different ways to add a column from other dataframes in Pandas Python.

We can use the following methods to add a column to a dataframe from another dataframe in Python:

  • Using join method
  • Using insert() method
  • Using merge() function
  • Using map function
  • Using assign method
  • Using concat() function

Let’s see all of them one by one in detail with the help of some examples:

1. Add column from another dataframe in Pandas Python using the join method

The join method in Pandas is used to combine two dataframes based on their common index or column.

Here is an example of how we can use the join method in Python to add a column from one dataframe to another in Pandas:

import pandas as pd

Employee_name = pd.DataFrame({'ID': [1, 2, 3], 'Name': ['Alice', 'Bob', 'Charlie']})
Employee_age = pd.DataFrame({'ID': [1, 2, 3], 'Age': [25, 30, 35]})

Employee_details = Employee_name.set_index('ID').join(Employee_age.set_index('ID'))
print(Employee_details)

Here we have used the set_index() method in Python Pandas to set the ‘ID’ values as the index for both of the original dataframes.

Employee_name.set_index('ID').join(Employee_age.set_index('ID'))

Output:

       Name  Age
ID              
1     Alice   25
2       Bob   30
3   Charlie   35

Here is the screenshot of the code execution:

How to Add a Column from Another Dataframe in Pandas Python

2. Add a column from another dataframe in Pandas using the insert() function

The insert() in Pandas Python is used to a column from one dataframe to another at a specific position, Else it is similar to the above join method.

READ:  Python Django format date

Let’s see an instance that demonstrates how to use the insert() function to add column to dataframe in Python:

import pandas as pd

state_info = pd.DataFrame({
    'State': ['California', 'Texas', 'New York'],
    'Population': [39512223, 28995881, 19453561],
    'Area (sq mi)': [423967, 695662, 54555]
})
state_additional_info = pd.DataFrame({
    'State': ['California', 'Texas', 'New York'],
    'Governor': ['Gavin Newsom', 'Greg Abbott', 'Kathy Hochul']
})

extracted_governor_col = state_additional_info['Governor']
state_info.insert(2, "Governor", extracted_governor_col)
print("First DataFrame after adding the column from the second DataFrame:\n", state_info)

First, we have to extract the column that we want to add to another dataframe in Python:

extracted_governor_col = state_additional_info['Governor']
print(type(extracted_governor_col))

This way we have created a class ‘pandas.core.series.Series’ in Python.

state_info.insert(2, "Governor", extracted_governor_col)

Here, we are inserting this series at the 2nd position in the state_info dataframe with the help of the insert() function in Pandas Python.

The output of the source code is:

First DataFrame after adding the column from the second DataFrame:
         State  Population      Governor  Area (sq mi)
0  California    39512223  Gavin Newsom        423967
1       Texas    28995881   Greg Abbott        695662
2    New York    19453561  Kathy Hochul         54555

The following screenshot was taken after the code was implemented in the Pycharm editor.

dataframe add column from another dataframe in Pandas Python

3. Add columns from another dataframe in Pandas using the merge() function

The Pandas merge() function combines two dataframes based on a common column. The merge() function performs join operations similar to relational databases like SQL.

Here is an example, of how to add column from one dataframe to another in Python using the merge() function:

import pandas as pd

employees_df = pd.DataFrame({
    'EmployeeID': [1, 2, 3],
    'Name': ['John', 'Alice', 'Bob']
})
departments_df = pd.DataFrame({
    'EmployeeID': [2, 3, 4],
    'Department': ['HR', 'Engineering', 'Marketing']
})
merged_df = pd.merge(employees_df, departments_df, on='EmployeeID', how='left')
print("Merged DataFrame:\n", merged_df)

Here, we have provided the on and how as parameters to the merge() function. The on=’EmployeeID’ parameter takes the name of the column, that will help merging.

READ:  How to Convert Dict to Tensor

The how=’left’ specifics which keys are included in the result table.

merged_df = pd.merge(employees_df, departments_df, on='EmployeeID', how='left')

Output:

Merged DataFrame:
    EmployeeID   Name   Department
0           1   John          NaN
1           2  Alice           HR
2           3    Bob  Engineering

Upon executing the code in Pycharm, the resulting output is displayed in the screenshot below.

dataframe add columns from another dataframe in Python Pandas

4. Add a column from one dataframe to another in Python using the map() function

The map() function in Pandas is used for element-wise transformation on a single column.

However, it can be used in combination with a Python dictionary to add a column to a dataframe in Pandas.

Let’s take an example to demonstrate how to use the map function to do so:

import pandas as pd

state_info = pd.DataFrame({
    'State': ['California', 'Texas', 'New York', 'Florida'],
    'Population': [39512223, 28995881, 19453561, 21477737],
    'Area (sq mi)': [423967, 695662, 54555, 170312],
})
state_regions = {
    'California': 'West',
    'Texas': 'South',
    'New York': 'Northeast',
    'Florida': 'South'
}
state_info['Region'] = state_info['State'].map(state_regions)
print("DataFrame with Region Column:\n", state_info)

Output:

DataFrame with Region Column:
         State  Population  Area (sq mi)     Region
0  California    39512223        423967       West
1       Texas    28995881        695662      South
2    New York    19453561         54555  Northeast
3     Florida    21477737        170312      South
adding a column to a dataframe in python

5. Add columns from one dataframe to another using the assign method

The assign method in Pandas allows us to add a new column to a dataframe in a chained manner. We will use the map function to map the column of the dataframe to the original column.

Here’s how to add a new column in a dataframe using Python Pandas:

import pandas as pd

Users_dataframe = pd.DataFrame({'ID': [1, 2, 3], 'Name': ['Alice', 'Bob', 'Charlie']})
Age_dataframe = pd.DataFrame({'ID': [2, 3, 4], 'Age': [25, 30, 22]})

Users_dataframe = Users_dataframe.assign(Age=Users_dataframe['ID'].map(Age_dataframe.set_index('ID')['Age']))
print('The extended user dataframe:\n', Users_dataframe)

We are using the Age=Users_dataframe[‘ID’] to add a new column to the dataframe. The value for this new will be obtained by mapping the ‘ID‘ column of the dataframe to the ‘Age’ values in the second dataframe.

Users_dataframe.assign(Age=Users_dataframe['ID'].map(Age_dataframe.set_index('ID')['Age']))

Output:

The extended user dataframe:
    ID     Name   Age
0   1    Alice   NaN
1   2      Bob  25.0
2   3  Charlie  30.0

After the code was implemented in the Pycharm editor, the below screenshot was taken to explain the output:

pandas dataframe add columns from another dataframe in Python

6. Pandas add columns from another dataframe using the concat() function

The concat function in Python can be useful for adding columns when the DataFrames have the same index.

READ:  How to Create a Snake game in Python using Turtle

Here is an instance, of how to add a column from another dataframe in Pandas Python:

import pandas as pd

clients_dataframe = pd.DataFrame({'ID': [1, 2, 3], 'Name': ['John', 'Joe', 'Monica']})
clients_cities = pd.DataFrame({'Cities': ['Texas', 'Florida', 'Alaska']})

concatenated_df = pd.concat([clients_dataframe, clients_cities], axis=1)
print(concatenated_df)

Output:

   ID    Name   Cities
0   1    John    Texas
1   2     Joe  Florida
2   3  Monica   Alaska

Here is the screenshot of the code implementation in the Pycharm Python editor:

add a column from one dataframe to another in Python pandas

Conclusion

There are six different methods to add column from another dataframe in Pandas Python such as the insert() function, merge() function, map() function, assign method, concat method, etc makes the task easy for any coder.

After reading this article, I hope you understand all the methods and will use them in your program whenever needed.

You may also like to read: