I want to merge 2 columns of the same dataframe, but by using some specific condition.
consider the following dataframe :
number-first | Number-second |
---|---|
1 | Nan |
2 | 4C |
3A | 5 |
Nan | 6 |
Nan | 7 |
Nan | Nan |
The conditions are:
- If the Number-first column has a alphanumeric value and the Number-second Column has a Nan value or a ” (empty string) -> the Result column should only consider the value from Number-first
- If the Number-first column has a Nan or ” (empty string) value and the Number-second Column has a alphanumeric value -> the Result column should only consider the value from Number-second
- If the values from both the columns are alphanumeric the result column should consist of value from Number-first and Number-second, which are separated by a ‘-‘
- If both the Columns have Nan or empty string values, the result should consist of a ” (empty string) value
Following would be the output for the above dataframe:
Number-first | Number-second | Result |
---|---|---|
1 | Nan | 1 |
2 | 4C | 2 – 4C |
3A | 5 | 3A – 5 |
Nan | 6 | 6 |
Nan | 7 | 7 |
Nan | Nan | Nan |
I have been unsuccessful using the .select method and providing the above conditions.
Thanks in advance for the help !
below is the code snippet of the conditions, which don’t seem to work for me:
conditions = [ df['Number-first'].str.isalnum(), df['Number-second'].str.isalnum(), df['Number-first'].str.isalnum() & df['Number-second'].str.isalnum() ]
Advertisement
Answer
You can use the combine
function to do this with a custom function like so:
import pandas as pd import numpy as np def custom_combine(v1, v2): if pd.isna(v1) & pd.isna(v2): return np.nan elif pd.isna(v1): return v2 elif pd.isna(v2): return v1 else: return f'{v1} - {v2}' df['Result'] = ( # ignore non alphanumeric values df.where(df.apply(lambda s: s.str.isalnum())) .pipe(lambda df: df['Number-first'].combine(df['Number-second'], custom_combine) ) ) print(df) Number-first Number-second Result 0 1 NaN 1 1 2 4C 2 - 4C 2 3A 5 3A - 5 3 NaN 6 6 4 NaN 7 7 5 NaN NaN NaN
Alternatively, you can take advantage of pandas’ vectorized string methods
import pandas as pd import numpy as np df['Result'] = ( df.where(df.apply(lambda s: s.str.isalnum())) .pipe(lambda df: df['Number-first'].str.cat(df['Number-second'], '-', na_rep='') ) .str.strip('-') .replace('', np.nan) ) print(df) Number-first Number-second Result 0 1 NaN 1 1 2 4C 2-4C 2 3A 5 3A-5 3 NaN 6 6 4 NaN 7 7 5 NaN NaN NaN