CODEWITHSUNDEEP
pythonpandasdataframe
Nae
Nae

Reputation: 15325

How to concatenate values of all rows in a dataframe into a single row without altering the columns?

I have a data frame input that looks like:

  col1 col2 col3
0    3    1  NaN
1  NaN    7    8

How to collapse all rows while concatenating the data in the rows with ', '?

The desired data frame output:

  col1  col2 col3
0    3  1, 7    8

Sample input code:

import pandas as pd
import numpy as np


d = {'col1': ["3", np.nan], 'col2': ["1", "7"], 'col3': [np.nan, "8"]}
df = pd.DataFrame(data=d)

Upvotes: 5

Views: 2185

Answers (5)

BENY
BENY

Reputation: 323226

With stack 

df.stack().groupby(level=1).apply(','.join).to_frame().T
Out[163]: 
  col1 col2 col3
0    3  1,7    8

Upvotes: 4

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210832

One more option:

In [156]: pd.DataFrame([[df[c].dropna().astype(int).astype(str).str.cat(sep=', ') 
                         for c in df]], 
                       columns=df.columns)
Out[156]:
  col1  col2 col3
0    3  1, 7    8

Upvotes: 3

piRSquared
piRSquared

Reputation: 294218

pd.DataFrame(
    [[
        ', '.join(map(str, map(int, filter(pd.notna, c))))
        for c in zip(*df.values)
    ]], columns=df.columns
)

  col1  col2 col3
0    3  1, 7    8

Upvotes: 5

sacuL
sacuL

Reputation: 51335

One way to get what you want would be to create a new dataframe with the same columns as your old dataframe, and populate the first index with your desired data. In your case, your desired data would be a list of each column, joined by ', ', and with your NaN values removed:

new_df = pd.DataFrame(columns=df.columns)

for col in df.columns:
    new_df.loc[0, col] = ', '.join(df[col].dropna().tolist())

>>> new_df
  col1  col2 col3
0    3  1, 7    8

Upvotes: 4

cs95
cs95

Reputation: 402263

agg + dropna + str.join comes to mind.

df.agg(lambda x: ', '.join(x.dropna())).to_frame().T

  col1  col2 col3
0    3  1, 7    8

There are other solutions, my peers will find them for you :)

Upvotes: 7

Related Questions