Skip to content
Advertisement

Merge 2 columns from a single Dataframe in Pandas

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:

  1. 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
  2. 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
  3. 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 ‘-‘
  4. 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
Advertisement