Convert pandas columns to concatenated strings

user_9067615 0
Viewed 57

Say I have the following DataFrame:

df = pd.DataFrame(np.arange(10).reshape(5,2),columns=list('AB'))

    A   B
0   0   1
1   2   3
2   4   5
3   6   7
4   8   9

And I wish to output each column header followed by the column concatenated as a string like so:

'''A
02468
B
13579'''

I can do like so with a for loop:

for col in df.columns:
  print(col, df[col].astype(str).str.cat(), sep='\n')

but I have a large number of columns - is there a more efficient way to do this?

pandas vectorization dataframe python string
2 Answers

Try converting the columns to str with astype, joining them together, then take advantage to to_csv's ability to create formatted data setting the separator to newlines, and exclude the header:

import numpy as np
import pandas as pd

df = pd.DataFrame(np.arange(10).reshape(5, 2), columns=list('AB'))

s = df.astype(str).apply(''.join).to_csv(sep='\n', header=False)
print(s)

s:

A
02468
B
13579

I was interested in the timings so I made a perfplot:

enter image description here

import numpy as np
import pandas as pd
import perfplot


def make_data(n):
    if n // 2 == 0:
        return pd.DataFrame(columns=list('AB'))
    df = pd.DataFrame(np.arange(n).reshape(n // 2, 2), columns=list('AB'))
    return df


def for_option(df):
    s = ''
    for k, v in df.astype(str).to_dict('list').items():
        s += f"{k}\n{''.join(v)}\n"
    return s


def apply_option_to_csv(df):
    s = df.astype(str).apply(''.join).to_csv(sep='\n', header=False)
    return s


def apply_option_for(df):
    s = ''
    for k, v in zip(df.columns, df.astype(str).apply(''.join)):
        s += f"{k}\n{v}\n"
    return s


if __name__ == '__main__':
    out = perfplot.bench(
        setup=make_data,
        kernels=[
            for_option,
            apply_option_to_csv,
            apply_option_for
        ],
        labels=['for option', 'apply option (to csv)', 'apply option (for)'],
        n_range=[2 ** k for k in range(25)],
        equality_check=None
    )
    out.save('res.png', transparent=False)

It appears to_csv has some overhead which makes it overall less efficient than other options. In terms of apply(''.join) vs to_dict('list').items() and joining each value they appear to behave similarly at larger values, but Scott Boston's solution is significantly faster for smaller frames.

user_154978880

Try this:

for k,v in df.astype(str).to_dict('list').items():
    print(k)
    print(''.join(v))

It may be faster than using df.apply you'll have to test with your dataframe.

user_63615310
Related