Create Pandas DataFrame from a string

Question

In order to test some functionality I would like to create a DataFrame from a string  Let s say my test data looks like   TESTDATA    col1 col2 col3 1 4 4 99 2 4 5 200 3 4 7 65 4 3 2 140       What is the simplest way to read that data into a Pandas DataFrame

User · Answer

Split Method  data   input string df   pd DataFrame  x split      for x in data split   n     print df

User · Answer

A quick and easy solution for interactive work is to copy-and-paste the text by loading the data from the clipboard   Select the content of the string with your mouse     In the Python shell use read clipboard     gt  gt  gt  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 separator    gt  gt  gt  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   gt  gt  gt  df   pd read clipboard sep        save to dataframe

User · Answer

This answer applies when a string is manually entered  not when it s read from somewhere  A traditional variable-width CSV is unreadable for storing data as a string variable  Especially for use inside a  py file  consider fixed-width pipe-separated data instead  Various IDEs and editors may have a plugin to format pipe-separated text into a neat table  Using read csv Store the following in a utility module  e g  util pandas py  An example is included in the function s docstring  import io import re  import pandas as pd   def read psv str input  str    kwargs  - gt  pd DataFrame       quot  quot  quot Read a Pandas object from a pipe-separated table contained within a string       Input 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  quot Pipe Table Formatter quot  plugin has a  quot Format quot  feature that can      be used to neatly format a table       Ref  https   stackoverflow com a 46471952       quot  quot  quot       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 io StringIO str input   sep        kwargs    Non-working alternatives The code below doesn t work properly because it adds an empty column on both the left and right sides  df   pd read csv io StringIO df str   sep r  s    s    engine  python    As for read fwf  it doesn t actually use so many of the optional kwargs that read csv accepts and uses  As such  it shouldn t be used at all for pipe-separated data

User · Answer

In one line  but first import IO import pandas as pd import io     TESTDATA  quot  quot  quot col1 col2 col3 1 4 4 99 2 4 5 200 3 4 7 65 4 3 2 140  quot  quot  quot   df   pd read csv   io StringIO TESTDATA     sep  quot   quot   print   df

User · Answer

Simplest way is to save it to temp file and then read it   import pandas as pd  CSV FILE NAME    temp file csv     Consider creating temp file  look URL below with open CSV FILE NAME   w   as outfile      outfile write TESTDATA  df   pd read csv CSV FILE NAME  sep        Right way of creating temp file  How can I create a tmp file in Python

User · Answer

A simple way to do this is to use StringIO StringIO  python2  or io StringIO  python3  and pass that to the pandas read csv function  E g   import sys if sys version info 0   lt  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

[python] Create Pandas DataFrame from a string

Examples related to python

Examples related to string

Examples related to pandas

Examples related to csv

Examples related to csv-import