Merging column values in a data frame in Pandas / Python

Question

I'm trying to merge the values of columns (Columns B and C) within the same dataframe. B and C sometimes have the same values. Some values in B are present in C while some values in C are present in B. The final results would show one column that is the combination of the two columns.

Initial data:

 A          B          C            D
Apple    Canada        ''          RED
Bananas    ''          Germany     BLUE
Carrot     US          US          GREEN
Dorito     ''          ''          INDIGO

Expected Data:

 A          B         C
Apple    Canada      RED
Bananas  Germany      BLUE
Carrot     US        GREEN
Dorito     ''        INDIGO

What if B and C had different values?

Scott Boston
– Scott Boston

2019-05-07 17:25:45 +00:00
Commented May 7, 2019 at 17:25 — Scott Boston
– Scott Boston, Commented May 7, 2019 at 17:25

BENY · Accepted Answer · 2019-05-07 17:27:10Z

2

IIUC

df['B']=df[['B','C']].replace("''",np.nan).bfill(1).loc[:,'B']
df=df.drop('C',1).rename(columns={'D':'C'})
df
Out[102]: 
         A        B       C
0    Apple   Canada     RED
1  Bananas  Germany    BLUE
2   Carrot       US   GREEN
3   Dorito      NaN  INDIGO

answered May 7, 2019 at 17:27

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mykola Zotko · Accepted Answer · 2019-05-07 19:52:16Z

1

You can sort strings and take the last one:

df['B'] = df[['B', 'C']].apply(lambda x: x.sort_values()[1], axis=1)

df=df.drop('C', 1).rename(columns={'D':'C'})    
print(df)

Output:

         A        B       C
0    Apple   Canada     RED
1  Bananas  Germany    BLUE
2   Carrot       US   GREEN
3   Dorito       ''  INDIGO

edited May 7, 2019 at 19:52

answered May 7, 2019 at 18:38

Mykola Zotko

18.2k6 gold badges88 silver badges90 bronze badges

Comments

Erfan · Accepted Answer · 2019-05-07 18:03:39Z

0

Another way would be to make smart use of list comprehension:

# Make sets of the column B and C combined to get rid of duplicates
k = [set(b.strip() for b in a) for a in zip(df['B'], df['C'])]

# Flatten sets to strings
k = [''.join(x) for x in k]

# Create desired column
df['B'] = k
df.drop('C', axis=1, inplace=True)

print(df)
         A        B       D
0    Apple   Canada     RED
1  Bananas  Germany    BLUE
2   Carrot       US   GREEN
3   Dorito           INDIGO

answered May 7, 2019 at 18:03

Erfan

43.3k10 gold badges75 silver badges86 bronze badges

Collectives™ on Stack Overflow

Merging column values in a data frame in Pandas / Python

Initial data:

Expected Data:

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Initial data:

Expected Data:

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related