Lambda Apply : Referencing other rows and columns

user_13239007 0
Viewed 453

I'm trying to alter the values of a given column in my dataset based on values around the given cell. Consider the following data:

Data = {'Col1': [5593 , 5114 , 6803 , 2175 , 2175] , 'Col2': [2879 , 1176 , 7114 , 8677 , 0]}
df = pd.DataFrame(data = Data)
df.head()

Col1    Col2
0   5593    2879
1   5114    1176
2   6803    7114
3   2175    8677
4   2175    0

I create a new column to store the new values :

Data['Col3'] = Data['Col2']

I want to make an apply - lambda function which does the following: If Col3 is zero and the previous value for Col1 equals the current value for Col1, i.e: (x.shift(-2 , -1) == x.shift(-2, 0), then the actual value for Col3 should be the previous value for Col2, i.e x.shift(-1 , -1). Otherwise the value for Col3 should be unchanged.

I've tried something like the following (Pseudo-code):

df['Col3'] = df['Col3'].apply(lambda x: x.shift(-1 , -1) if (x == 0 and x.shift(-2 , -1) == x.shift(-2, 0)) else x)

For this specific subset of my data is should look like the following:

Col1    Col2    Col3
0   5593    2879    2879
1   5114    1176    1176
2   6803    7114    7114
3   2175    8677    8677
4   2175    0       8677

I'm not sure if shift is the right method to use (The series contains NaN), but hopefully the idea is clear.

My real dataset is very large, so i want the operations to perform well with many rows.

pandas apply lambda python
1 Answers

IIUC, you could use np.where with the shifted columns:

df['Col3'] = np.where(df['Col1'].shift().eq(df['Col1']), df['Col2'].shift(), df['Col2'])
print(df)

Output

   Col1  Col2    Col3
0  5593  2879  2879.0
1  5114  1176  1176.0
2  6803  7114  7114.0
3  2175  8677  8677.0
4  2175     0  8677.0

Below is step by step, explanation with comments:

# create a mask, where is True if the consecutive values in Col1 are equal
mask = df['Col1'].shift().eq(df['Col1'])

# choose between the shifted Col2 (the previous value) and Col2 using the mask
df['Col3'] = np.where(mask, df['Col2'].shift(), df['Col2'])

print(df)
user_40015920
Related