How to Convert Microsoft Excel's XLOOKUP to Python for Pandas DataFrame
This walks through how to do an XLOOKUP in Python's Pandas DataFrame. This is for when you need to have XLOOKUP for an entire column of a table. Also discuss the add-on code needed to change the match mode to -1 or 1.
Here is the entire code:
--- This sets up out table initially ----
import pandas as pd
import datetime as dt
import numpy as np
begin = dt.datetime(year=2022,month=2,day=7,hour=7,minute=30,second=0)
end = dt.datetime(year=2022,month=2,day=7,hour=8,minute=30,second=0)
datetime1 = pd.date_range(start =begin, end=end,freq = '1min')
datetime15 = pd.date_range(start =begin, end=end,freq = '15min')
i1 = []
for i,element in enumerate(datetime1):
i = np.random.randint(0,999)
i1.append(i)
i15 = []
for i2,element in enumerate(datetime15):
i2 = np.random.randint(0,999)
i15.append(i2)
data1 = {'datetime':datetime1,
'column1':i1}
data15 = {'datetime':datetime15,
'column15':i15}
df1 = pd.DataFrame(data1)
df15 = pd.DataFrame(data15)
--- This is the code used to re-create XLOOKUP ---
df1= pd.merge(left = df1, right = df15, how = 'left')
df1.ffill(inplace = True)
--- This prints the tables to a CSV file. CHANGE THE PATH VARIABLE ---
df1.to_csv(
path_or_buf= r'PLACE FILE DIRECTORY ADDRESS HERE'/df1.csv,
index = False,
#columns = []
)
df15.to_csv(
path_or_buf= r'PLACE FILE DIRECTORY ADDRESS HERE'/df15.csv,
index = False,
#columns = []
)