Is there a way to make this eaiser?

user_13541525 0
Viewed 60

I have two dataframes like following,

   x   y   z
0  10  a   1
1  20  b   2
2  10  c   3
3  20  d   4
4  10  e   5 

   x   a   
0  10  1   
1  20  1.5

for the first dataframe, I want to create a new column that will have '1' if value of column 'z' greater than the value of 'a' (from second dataframe) ,which has the same variable 'x', otherwise '0'.

For example, when it checks this row

2  10  c   3

it will become

2  10  c   3   1

Simply, I tried to write a method but my data is very big so my solution does not seem efficient. Maybe there are some pandas functionalities make it easier.

pandas dataframe python-3.x python
2 Answers

Try merge on 'x' to get values in the same frame, then doing the comparison with np.where for vectorized comparison:

import numpy as np
import pandas as pd

df1 = pd.DataFrame({
    'x': {0: 10, 1: 20, 2: 10, 3: 20, 4: 10},
    'y': {0: 'a', 1: 'b', 2: 'c', 3: 'd', 4: 'e'},
    'z': {0: 1, 1: 2, 2: 3, 3: 4, 4: 5}
})

df2 = pd.DataFrame({
    'x': {0: 10, 1: 20},
    'a': {0: 1.0, 1: 1.5}
})

# Merge DF1 and DF2 on X
merged = df1.merge(df2, on='x')

# Create Check Column
merged['check'] = np.where(merged['z'] > merged['a'], 1, 0)

# Get Rid of A
merged = merged.drop(columns='a')

print(merged)

merged:

    x  y  z  check
0  10  a  1      0
1  10  c  3      1
2  10  e  5      1
3  20  b  2      1
4  20  d  4      1
user_154978880

Use DataFrame.join to find the correct value of a for each row in your first DataFrame.

import pandas as pd

df = pd.DataFrame({
    "x": [10, 20, 10, 20, 10],
    "y": ["a", "b", "c", "d", "e"],
    "z": [1, 2, 3, 4, 5]
})
x = pd.DataFrame({"x": [10, 20], "a": [1, 1.5]})
df = df.join(x.set_index("x"), on="x")
df["z > a"] = (df["z"] > df["a"]).astype(int)
print(df)

    x  y  z    a  z > a
0  10  a  1  1.0      0
1  20  b  2  1.5      1
2  10  c  3  1.0      1
3  20  d  4  1.5      1
4  10  e  5  1.0      1
user_96810810
Related