python pass string to pandas dataframe in a specific format

Question

I am not entirely sure if this is possible but I thought I would go ahead and ask. I currently have a string that looks like the following:

myString = 
"{"Close":175.30,"DownTicks":122973,"DownVolume":18639140,"High":177.47,"Low":173.66,"Open":177.32,"Status":29,"TimeStamp":"\/Date(1521489600000)\/","TotalTicks":245246,"TotalVolume":33446771,"UnchangedTicks":0,"UnchangedVolume":0,"UpTicks":122273,"UpVolume":14807630,"OpenInterest":0}
 {"Close":175.24,"DownTicks":69071,"DownVolume":10806836,"High":176.80,"Low":174.94,"Open":175.24,"Status":536870941,"TimeStamp":"\/Date(1521576000000)\/","TotalTicks":135239,"TotalVolume":19649350,"UnchangedTicks":0,"UnchangedVolume":0,"UpTicks":66168,"UpVolume":8842514,"OpenInterest":0}"

The datasets can be varying lengths (this example has 2 datasets but it could have more), however the parameters will always be the same, (close, downticks, downvolume, etc).

Is there a way to create a dataframe from this string that takes the parameters as the index, and the numbers as the values in the column? So the dataframe would look something like this:

df = 


              0         1
index

Close        175.30    175.24
DownTicks    122973    69071
DownVolume   18639140  10806836
High         177.47    176.80
Low          173.66    174.94
Open         177.32    175.24

(etc)...

yes, i actually got this string from a json dump which yields that result — lee.edward01
– lee.edward01, Commented Jul 18, 2018 at 13:59
aren't you missing a comma at the end of the first dictionary? — lmiguelvargasf
– lmiguelvargasf, Commented Jul 18, 2018 at 14:01

W Stokvis · Accepted Answer · 2018-07-18 14:21:34Z

1

It looks like there are some issues with your input. As mentioned by @lmiguelvargasf, there's a missing comma at the end of the first dictionary. Additionally, there's a \n which you can simply use a str.replace to fix.

Once those issues have been solved, the process it pretty simple.

myString = '''{"Close":175.30,"DownTicks":122973,"DownVolume":18639140,"High":177.47,"Low":173.66,"Open":177.32,"Status":29,"TimeStamp":"\/Date(1521489600000)\/","TotalTicks":245246,"TotalVolume":33446771,"UnchangedTicks":0,"UnchangedVolume":0,"UpTicks":122273,"UpVolume":14807630,"OpenInterest":0}
 {"Close":175.24,"DownTicks":69071,"DownVolume":10806836,"High":176.80,"Low":174.94,"Open":175.24,"Status":536870941,"TimeStamp":"\/Date(1521576000000)\/","TotalTicks":135239,"TotalVolume":19649350,"UnchangedTicks":0,"UnchangedVolume":0,"UpTicks":66168,"UpVolume":8842514,"OpenInterest":0}'''
myString = myString.replace('\n', ',')
import ast
list_of_dicts = list(ast.literal_eval(myString))
df = pd.DataFrame.from_dict(list_of_dicts).T
df
                                       0                        1
Close                              175.3                   175.24
DownTicks                         122973                    69071
DownVolume                      18639140                 10806836
High                              177.47                    176.8
Low                               173.66                   174.94
Open                              177.32                   175.24
OpenInterest                           0                        0
Status                                29                536870941
TimeStamp        \/Date(1521489600000)\/  \/Date(1521576000000)\/
TotalTicks                        245246                   135239
TotalVolume                     33446771                 19649350
UnchangedTicks                         0                        0
UnchangedVolume                        0                        0
UpTicks                           122273                    66168
UpVolume                        14807630                  8842514

edited Jul 18, 2018 at 14:21

answered Jul 18, 2018 at 14:13

W Stokvis

1,43910 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

lee.edward01 Over a year ago

@W Stokvis should there just be a comma after the first dictionary and not a newline?

W Stokvis Over a year ago

Yes. The newline character was giving me an error when I tried to transform it using ast.literal_eval. You additionally will want to encapsulate myString in triple quotes since it's multiple lines. I'm editing my answer to account for that.

Collectives™ on Stack Overflow

python pass string to pandas dataframe in a specific format

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related