This code compares based on condition
the var1
and var2
and creates Results1
based on choices
(this code works well):
# from: https://stackoverflow.com/questions/27474921/compare-two-columns-using-pandas?answertab=oldest#tab-top
# from: https://stackoverflow.com/questions/60099141/negation-in-np-select-condition
import pandas as pd
import numpy as np
# Creating one column from two columns. We asume that in every row there is one NaN and one value and that value fills new column.
df = pd.DataFrame({ 'var1': ['a', 'b', 'c',np.nan, np.nan],
'var2': [1, 2, np.nan , 4, np.nan],
'var3': [np.nan , "x", np.nan, "y", "z"],
'var4': [np.nan , 4, np.nan, 5, 6],
'var5': ["a", np.nan , "b", np.nan, "c"],
'var6': [1, np.nan , 2, np.nan, 3]
})
#all conditions that are connected with logical operators (&, |, etc) should be in ().
conditions = [
(df["var1"].notna()) & (df['var2'].notna()),
(pd.isna(df["var1"])) & (pd.isna(df["var2"])),
(df["var1"].notna()) & (pd.isna(df["var2"])),
(pd.isna(df["var1"])) & (df['var2'].notna())]
choices = ["Both values", np.nan, df["var1"], df["var2"]]
df['Result1'] = np.select(conditions, choices, default=np.nan)
df
looks like as it should:
| | var1 | var2 | var3 | var4 | var5 | var6 | Result1 |
|---:|:-------|-------:|:-------|-------:|:-------|-------:|:------------|
| 0 | a | 1 | nan | nan | a | 1 | Both values |
| 1 | b | 2 | x | 4 | nan | nan | Both values |
| 2 | c | nan | nan | nan | b | 2 | c |
| 3 | nan | 4 | y | 5 | nan | nan | 4 |
| 4 | nan | nan | z | 6 | c | 3 | nan |
Now I want to compare multiple pandas columns (in my example var1
and var2
, after var3
and var4
, after var5
and var6
) and based on condition
and choices
create corresponding Results
column (in my example Result1
, Result2
, Result3
). I thought the best way should be to use vectorization (because of better performance). The df
I want to get should look like:
| | var1 | var2 | var3 | var4 | var5 | var6 | Result1 | Result2 | Result3 |
|---:|:-------|-------:|:-------|-------:|:-------|-------:|:------------|:------------|:------------|
| 0 | a | 1 | nan | nan | a | 1 | Both values | nan | Both values |
| 1 | b | 2 | x | 4 | nan | nan | Both values | Both values | nan |
| 2 | c | nan | nan | nan | b | 2 | c | nan | Both values |
| 3 | nan | 4 | y | 5 | nan | nan | 4 | Both values | nan |
| 4 | nan | nan | z | 6 | c | 3 | nan | Both values | Both values |
I tried this:
import pandas as pd
import numpy as np
# Creating one column from two columns. We asume that in every row there is one NaN and one value and that value fills new column.
df = pd.DataFrame({ 'var1': ['a', 'b', 'c',np.nan, np.nan],
'var2': [1, 2, np.nan , 4, np.nan],
'var3': [np.nan , "x", np.nan, "y", "z"],
'var4': [np.nan , 4, np.nan, 5, 6],
'var5': ["a", np.nan , "b", np.nan, "c"],
'var6': [1, np.nan , 2, np.nan, 3]
})
col1 = ["var1", "var3", "var5"]
col2 = ["var2", "var4", "var6"]
colR = ["Result1", "Result2", "Result3"]
#all conditions that are connected with logical operators (&, |, etc) should be in ().
conditions = [
(df[col1].notna()) & (df[col2].notna()),
(pd.isna(df[col1])) & (pd.isna(df[col2])),
(df[col1].notna()) & (pd.isna(df[col2])),
(pd.isna(df[col1])) & (df[col2].notna())]
choices = ["Both values", np.nan, df[col1], df[col2]]
df[colR] = np.select(conditions, choices, default=np.nan)
Buy it gave me error:
ValueError: shape mismatch: objects cannot be broadcast to a single shape
Question: How to achieve my goal with vectorization (preferable because of better performance) or other method?
See Question&Answers more detail:
os