how to concatenate contents with linebreaks in dataframe

I have three columns (col1, col2, col3) having string contents with line breaks inserted. I would like to concatenate col1,col2 and col3 with new column (DesiredCol) as shown below

Here is the dataset

import pandas as pd

d = {'col1': ["ABC1"+"\n"+"ABC2"+"\n"+"ABC3", "BBC1"+"\n"+"BBC2"+"\n"+"BBC3"], 'col2': ["A"+"\n"+"B"+"\n"+"C", "A"+"\n"+"B"+"\n"+"C"],'col3': ["YES"+"\n"+"NO"+"\n"+"YES", "NO"+"\n"+"NO"+"\n"+"YES"]}

df = pd.DataFrame(data=d)

I tried using lambda function as below, however its not giving the desired column

cols=['col1','col2','col3']

df['DesiredCol'] = df[cols].apply(lambda row: ' '.join(row.values.astype(str)), axis=1)

Here is one possible solution (the key being to use the zip built-in function to join the respective elements, and then further apply methods on the dataframe to insert the desired newline characters specifically):

A) Edit: Fastest Solution

> (Thanks to mozway.)
> For an understanding of what this code achieves, and how each operation changes the data step by step into the desired column output, see solution "B" below. However, for large dataframes, it is better to avoid the use of applymap and instead combine map and zip in a list comprehension as shown here.

df['DesiredCol'] = ['\n\n'.join(map(' '.join, zip(*(y.splitlines() for y in x))))
                    for x in zip(*(df[c] for c in cols))]

B) Original Solution

> Providing a breakdown of the dataframe operations. *Note: applymap is a simple way of applying a function to every element (or cell) of a dataframe and is shown here for illustration of the intermittent states of the dataframe, but usually there is a faster approach.

Full code:

Computationally more efficient solution:

> However, solution "A" above will be faster and is recommended. Using the transpositions as shown here is not the most efficient approach.

df = pd.DataFrame(data=d)

df2 = df.T.apply(
    lambda x: list(
        map(" ".join, list(zip(*[y.split("\n") for y in x.values])))
    )
).T

cols = df2.columns
df['col4'] = df2[cols].astype(str).apply('\n'.join, axis=1)

Full code for solution as used in explained steps below:

df2 = df.applymap(lambda s: s.split('\n'))

df3 = df2.T.apply(lambda x: list(zip(*x.values)))

df3 = df3.T.apply(lambda x: x.str.join(' '))

cols = df3.columns
df['col4'] = df3[cols].astype(str).apply('\n'.join, axis=1)

Breakdown of df operations

1) Use applymap* to delimit each cell in cols 1-3

import pandas as pd

d = {
    "col1": [
        "ABC1" + "\n" + "ABC2" + "\n" + "ABC3",
        "BBC1" + "\n" + "BBC2" + "\n" + "BBC3",
    ],
    "col2": ["A" + "\n" + "B" + "\n" + "C", "A" + "\n" + "B" + "\n" + "C"],
    "col3": [
        "YES" + "\n" + "NO" + "\n" + "YES",
        "NO" + "\n" + "NO" + "\n" + "YES",
    ],
}

df = pd.DataFrame(data=d)

df2 = df.applymap(lambda s: s.split("\n"))
df2

2) Then, apply zip to respectively join the elements of the lists from the columns

df3 = df2.T.apply(lambda x: list(zip(*x.values)))

df3 = df3.T.apply(lambda x: x.str.join(" "))
df3

3) And lastly join the zipped new multi-column-concatenated lists into newline character-delimited strings

cols = df3.columns
df["col4"] = df3[cols].astype(str).apply("\n".join, axis=1)

df

which gives the final solution:

Using a list comprehension with zip:

df['out'] = [list(map(' '.join, zip(*(y.splitlines() for y in x))))
             for x in zip(df['col1'], df['col2'], df['col3'])]

               col1     col2          col3                                  out
0  ABC1\nABC2\nABC3  A\nB\nC  YES\nNO\nYES  [ABC1 A YES, ABC2 B NO, ABC3 C YES]
1  BBC1\nBBC2\nBBC3  A\nB\nC   NO\nNO\nYES   [BBC1 A NO, BBC2 B NO, BBC3 C YES]

And for a double line break:

df['out'] = ['\n\n'.join(map(' '.join, zip(*(y.splitlines() for y in x))))
             for x in zip(df['col1'], df['col2'], df['col3'])]

               col1     col2          col3                                    out
0  ABC1\nABC2\nABC3  A\nB\nC  YES\nNO\nYES  ABC1 A YES\n\nABC2 B NO\n\nABC3 C YES
1  BBC1\nBBC2\nBBC3  A\nB\nC   NO\nNO\nYES   BBC1 A NO\n\nBBC2 B NO\n\nBBC3 C YES

generalization to an arbitrary number of columns

cols = ['col1', 'col2', 'col3']
# for all columns
# cols = list(df)

df['DesiredCol'] = ['\n\n'.join(map(' '.join, zip(*(y.splitlines() for y in x))))
                    for x in zip(*(df[c] for c in cols))]

comparison of answers

difference in behavior

• John's and Mark's approaches use all columns, thus if you repeat the operation, the output is included. To avoid this behavior in the timing I added in all approaches a step to check for and remove the output column before running the code. I also added an alternative approach in my answer to handle an arbitrary number of columns.
• Pravash' approach uses the same string to join the strings chunks and the joined substrings, which I ignored for the timing considering it a valid output.

timings for a varying number of rows

John's code is likely slower due to the use of applymap and of pandas to perform the transposition operations (constructing the intermediate DataFrames is expensive). Mark's approach bottlenecks are the use of explode and groupby.

You can try using below code -

I have used zip and split('\n') to create individual tuples for rows in cols.

df["DesiredCol"] = df.apply(lambda row: " ".join(value for col in zip(row["col1"].split("\n"), row["col2"].split("\n"), row["col3"].split("\n")) for value in col), axis=1)

Here's another answer

o = df.apply(lambda x: x.str.split('\n').explode()).apply(lambda x: ' '.join(x), axis=1)
df['new col'] = o.groupby(o.index).apply(lambda x: '\n'.join(x)).reset_index(drop=True)