Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to row-wise concatenate several columns containing strings?

I have a specific series of datasets which come in the following general form:

import pandas as pd
import random
df = pd.DataFrame({'n': random.sample(xrange(1000), 3), 't0':['a', 'b', 'c'], 't1':['d','e','f'], 't2':['g','h','i'], 't3':['i','j', 'k']})

The number of tn columns (t0, t1, t2 ... tn) varies depending on the dataset, but is always <30. My aim is to merge the content of the tn columns for each row so that I achieve this result (note that for readability I need to keep the whitespace between elements):

df['result'] = df.t0 +' '+df.t1+' '+df.t2+' '+ df.t3

enter image description here

So far so good. This code may be simple but it becomes clumsy and inflexible as soon as I receive another dataset, where the number of tn columns goes up. This is where my question comes in:

Is there any other syntax to merge the content across multiple columns? Something agnostic to the number columns, akin to:

df['result'] = ' '.join(df.ix[:,1:])

Basically, I want to achieve the same as the OP in the link below, but with whitespace between the strings: Concatenate row-wise across specific columns of dataframe

like image 332
EmEs Avatar asked Sep 19 '16 11:09

EmEs


1 Answers

The key to operate in columns (Series) of strings en mass is the Series.str accessor.

I can think of two .str methods to do what you want.

str.cat()

The first is str.cat. You have to start from a series, but you can pass a list of series (unfortunately you can't pass a dataframe) to concatenate with an optional separator. Using your example:

column_names = df.columns[1:]  # skipping the first, numeric, column
series_list = [df[c] for c in column_names[1:]]
# concatenate:
df['result'] = series_list[0].str.cat(series_list[1:], sep=' ')

Or, in one line:

df['result'] = df[df.columns[1]].str.cat([df[c] for c in df.columns[2:]], sep=' ')

str.join()

The second is the .str.join() method, which works like the standard Python method string.join(), but for which you need to have a column (Series) of iterables, for example, a column of tuples, which we can get by applying tuples row-wise to a sub-dataframe of the columns you're interested in:

tuple_series = df[column_names].apply(tuple, axis=1)
df['result'] = tuple_series.str.join(' ')

Or, in one line:

df['result'] = df[df.columns[1:]].apply(tuple, axis=1).str.join(' ')

BTW, don't try the above with list instead of tuple. As of pandas-0.20.1, if the function passed into the Dataframe.apply() method returns a list and the returned list has the same number entries as the columns of the original (sub)dataframe, Dataframe.apply() returns a Dataframe instead of a Series.

like image 99
LeoRochael Avatar answered Sep 19 '22 23:09

LeoRochael