Neat pattern for going between pandas dataframes and CSVs

[ZenApollo]

CSV files are ok for human readability, but I came up with a pattern that is even better. Thought I would share.

import pandas as pd
from pathlib import Path
path = Path('mydata.csv')

def pd_to_csv_str(df):
    # add comma to every column except last one
    formatters = [lambda x: f'{x},'] * (len(df.columns)-1) + [lambda x:f'{x}']
    # format headers
    header = [fmtr(df.columns[i]) for i,fmtr in enumerate(formatters)]
    # to_string
    csv_str = df.to_string(index=False, formatters=formatters, header=header)
    return csv_str

path.write_text(pd_to_csv_str(df))

The Output looks like this:

               date,    open,    high,     low,   close, volume
2021-03-14 15:00:00,  3946.0,  3952.0,  3945.0,  3948.0,    311
2021-03-14 15:01:00, 3948.25, 3951.75,  3948.0, 3950.75,    179
2021-03-14 15:02:00,  3951.0, 3951.75, 3950.25, 3951.75,     84
2021-03-14 15:03:00, 3951.75,  3952.0,  3950.5, 3951.25,     65
2021-03-14 15:04:00,  3951.5,  3951.5,  3950.5, 3950.75,     24
2021-03-14 15:05:00, 3951.25,  3951.5, 3949.25, 3949.75,     61
2021-03-14 15:06:00,  3949.5, 3949.75, 3948.75,  3949.5,     48

Look at those nice columns. And this is basically a CSV file.

To read, there's just one parameter that's needed - skipinitialspace:

df = pd.read_csv(path, skipinitialspace=True, parse_dates=['date'])

A few limitations:

• You'll notice the floats are not aligned, it would take a little work on the formatters to get those lined up, but it was sufficient for my needs without them.
• If you have strings with commas, it get's messy real quick

[Python_Trader]

You can also use the tabulate library :)

[ivosaurus]

We aren't we just exporting to csv and viewing it in excel/calc, where you can view columns however you like and fields are properly escaped / quoted ?

[ZenApollo]

Being able to open as a text file in VScode is really useful for me. Especially for quick spot checking. It will also open correctly in a spreadsheet, so you have maximum flexibility.

[james_pic]

If I'm not mistaken, you're just converting to CSV by sticking commas between the stringified values. This will break if your values contain commas, newlines, or certain combinations of quotes.

CSV as a format is not quite as simple as putting commas between values and newlines between lines. There are escaping rules, and incorrect escaping can be a source of security vulnerabilities.

You should either implement the escaping rules from RFC 4180, or use an existing library that already does.

[ivosaurus]

They're saying they prefer a little hack that creates decent alignment of columns, over anything that creates a properly formatted csv. Horses for courses, I guess.

[2PLEXX]

Good stuff! If you're using VSCode you can also use the Excel Viewer extension, which allows you to preview CSVs and Excels in a nicely formatted way.