In this post we will understand how to combine two excel columns in pandas.
We can do a lot of operations in excel data in python using pandas package and this is just a basic operation of combining two columns in excel by reading it python.
Let’s understand the sheet in which we have columns that will combine in python, below is the screenshot of the sheet ‘Trade’ of the file TRD 2021.xlsx.
This sheet has equity trading data for different scrips along with quantity and buys and sell price.
Our objective here is to combine two excel columns “Buy” and “Sell” in pandas and create a new column by reading this excel sheet in python.
Open your python editor(we are using Spyder here) and insert the following program to combine two excel column in python using pandas.
import pandas as pd
#Replace – Path where the Excel file is stored\File name.xlsx
#Replace – your Excel sheet name
df = pd.read_excel (r’D:\Pers\TRD 2021.xlsx’, sheet_name=’Trade’)
df[‘new_column’] = df[‘Buy’] + df[‘Sell’]
The first step imports pandas package, if you don’t have pandas installed you can install in in easy steps by looking into the python documentation.
Replace the file path and change the sheet name to accommodate your excel file and sheet name along with new column name and existing columns.
Execute the code by pressing F5
Once the program finishes executing, you can see the result in the console as shown in the screenshot below.
This screenshot shows the newly created column ‘new_column’ with the sum of column ‘Buy’ and ‘Sell’ from the excel file.
Pandas is a very helpful package in python and it comes with a lot of features tat you can use to manipulate excel data(or any other flat data files).
We can achieve a lot of task related to excel using pandas and the example we just saw is a very basic operation of combining two excel columns in pandas.
Hope this helps.