Vlookup with Python without using Microsoft Excel functions
Certifications:
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
- How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024
The Microsoft Excel it is very efficient when handling small amount of data. But it requires the use of functions which are very easy to modify by mistake or to forget to remove them before saving the result.
Somewhere there comes the Python which with a few lines of code can perform the tasks we want and export the data to a file Excel.
One of the most useful and used functions is the function vlookup (vertical lookup). THE vlookup provides cross-linking of relationships between different Excel files or different friends in Excel. This action can be done easily by using Python.
Let's look at the problem we want to solve.
We have an Excel file with the total sales named sales.xlsx.
There is a second Excel file with the customer names called pelates.xlsx.
We would like to have the names of the customers in the sales.xlsx file, let's see how this is done.
Apart from having Python installed, we will need the following libraries which are easily installed by running the following commands in the command prompt.
pip install xlrd pip install pandas pip install numpy pip install openpyxl
We import the libraries
import pandas as pd import numpy as np
We fill 2 dataframe variables with the records of each Excel. With the sheet_name parameter we can also choose the gender where the data is located.
df_pelates = pd.read_excel(‘pelates.xlsx’,sheet_name=’Sheet1′) df_sales = pd.read_excel(‘sales.xlsx’,sheet_name=’Sheet1′)
We should rename the id field to customer_id so that it has the same name as it has in the sales dataframe.
df_pelates.rename(columns={'id':'customer_id'}, inplace=True)
In this step the vlookup, we merge both dataframes into a new one (df_final) in the customer_id field. In the how parameter we declare the way it will be done join as in SQL we have options left, right, outer, inner.
By choice right we declare that we want all the records from the second dataframe, linking to those linked to the first.
If there is no common customer_id it will have the value NaN/Null or else the blank.
df_final = pd.merge(df_pelates, df_sales, on='customer_id', how='right')
Let's see the fields that the merged dataframe now has.
print(df_final.columns)
The result so far is this.
print(df_final)
In the case that the client does not exist and we do not want the NaN values to come to us in their place and we just want to have an empty cell, we execute the following.
df_final = df_final.replace(np.nan, '', regex=True)
We can also choose which fields of these to keep.
df_final = df_final[['onoma','epitheto','eidos','posotita','kostos']]
The end result
And finally to export the results in a new excel.
df_final.to_excel('vlookuped.xlsx', index=False)
Nice!