Create Pandas DataFrame from String

Question

To test some functions, I would like to create a DataFrame from a string. Say my test data looks like this:

 TESTDATA="""col1;col2;col3 1;4.4;99 2;4.5;200 3;4.7;65 4;3.2;140 """ 

What is the easiest way to read this data in a Pandas DataFrame ?

Answer 1

An easy way to do this is to use StringIO.StringIO (python2) or io.StringIO (python3) and pass this to the pandas.read_csv function. For example:

 import sys if sys.version_info[0] < 3: from StringIO import StringIO else: from io import StringIO import pandas as pd TESTDATA = StringIO("""col1;col2;col3 1;4.4;99 2;4.5;200 3;4.7;65 4;3.2;140 """) df = pd.read_csv(TESTDATA, sep=";") 

Answer 2

A traditional variable-width CSV cannot be read to store data as a string variable. Specifically for use inside a .py file, instead consider pipe-wise data of a fixed width. Various IDEs and editors may have a plugin for formatting pipe-split text into a neat table.

The following works for me. To use it, save it to a file, for example, pandas_util.py . An example is included in the docstring function. If you are using a version of Python older than 3.6, remove the type annotations from the function definition line.

 import re import pandas as pd def read_pipe_separated_str(str_input: str, **kwargs) -> pd.DataFrame: """Read a Pandas object from a pipe-separated table contained within a string. Example: | int_score | ext_score | eligible | | | 701 | True | | 221.3 | 0 | False | | | 576 | True | | 300 | 600 | True | The leading and trailing pipes are optional, but if one is present, so must be the other. 'kwargs' are passed to 'read_csv'. They must not include 'sep'. In PyCharm, the "Pipe Table Formatter" plugin has a "Format" feature that can be used to neatly format a table. """ # Ref: https://stackoverflow.com/a/46471952/ substitutions = [ ('^ *', ''), # Remove leading spaces (' *$', ''), # Remove trailing spaces (r' *\| *', '|'), # Remove spaces between columns ] if all(line.lstrip().startswith('|') and line.rstrip().endswith('|') for line in str_input.strip().split('\n')): substitutions.extend([ (r'^\|', ''), # Remove redundant leading delimiter (r'\|$', ''), # Remove redundant trailing delimiter ]) for pattern, replacement in substitutions: str_input = re.sub(pattern, replacement, str_input, flags=re.MULTILINE) return pd.read_csv(pd.compat.StringIO(str_input), sep='|', **kwargs) 

Broken Alternative:

The code below does not work properly, because it adds an empty column on the left and right sides.

 df = pd.read_csv(pd.compat.StringIO(df_str), sep=r'\s*\|\s*', engine='python') 

Answer 3

A quick and easy solution for interactive work is to copy and paste text, loading data from the clipboard.

Select the contents of the line with the mouse:

In Python shell use read_clipboard()

 >>> pd.read_clipboard() col1;col2;col3 0 1;4.4;99 1 2;4.5;200 2 3;4.7;65 3 4;3.2;140 

Use the appropriate delimiter:

 >>> pd.read_clipboard(sep=';') col1 col2 col3 0 1 4.4 99 1 2 4.5 200 2 3 4.7 65 3 4 3.2 140 >>> df = pd.read_clipboard(sep=';') # save to dataframe 

Answer 4

Split method

 data = input_string df = pd.DataFrame([x.split(';') for x in data.split('\n')]) print(df)