Concat Columns of Dataframe in python?

Question

I have a data frame generate with the code as below:

# importing pandas as pd 
import pandas as pd 

# Create the dataframe 
df = pd.DataFrame({'Category':['A', 'B', 'C', 'D'], 
                   'Event':['Music Theater', 'Poetry Music', 'Theatre Comedy', 'Comedy Theatre'], 
                   'Cost':[10000, 5000, 15000, 2000]}) 

# Print the dataframe 
print(df)

I want a list to be generated combining all three columns and also removing whitespaces by "_" like and removing all trailing spaces too:-

[A_Music_Theater_10000, B_Poetry_Music_5000,C_Theatre_Comedy_15000,D_Comedy_Theatre_2000]

I want to it in most optimized way as running time is a issue for me. So looking to avoid for loops. Can anybody tell me how can i achieve this is most optimized way ?

jezrael · Accepted Answer · 2019-10-16 13:46:39Z

The most general solution is convert all values to strings, use join and last replace:

df['new'] = df.astype(str).apply('_'.join, axis=1).str.replace(' ', '_')

If need filter only some columns:

cols = ['Category','Event','Cost']
df['new'] = df[cols].astype(str).apply('_'.join, axis=1).str.replace(' ', '_')

Or processing each columns separately - if necessary replace and also convert numeric column to strings:

df['new'] = (df['Category'] + '_' + 
             df['Event'].str.replace(' ', '_') + '_' + 
             df['Cost'].astype(str))

Or after converting to strings add _, sum, but necessary after replace remove traling _ by rstrip:

df['new'] = df.astype(str).add('_').sum(axis=1).str.replace(' ', '_').str.rstrip('_')

print(df) 
  Category           Event   Cost                     new
0        A   Music Theater  10000   A_Music_Theater_10000
1        B    Poetry Music   5000     B_Poetry_Music_5000
2        C  Theatre Comedy  15000  C_Theatre_Comedy_15000
3        D  Comedy Theatre   2000   D_Comedy_Theatre_2000

Collectives™ on Stack Overflow

Concat Columns of Dataframe in python?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related