Transform a specific string to pandas dataframe

Question

My string looks at follow:

'{"book":"xrp_mxn","created_at":"2020-09-14T10:39:07+0000","minor":"-101850.00105195","major":"19705.14850000","fees_amount":"19.70514850","fees_currency":"xrp","minor_currency":"mxn","major_currency":"xrp","oid":"eIWDMLdNXK2Wvqxk","tid":"17970757","price":"5.1687","side":"buy","maker_side":"buy"},{"book":"xrp_mxn","created_at":"2020-09-14T10:34:23+0000","minor":"-1523.99894805","major":"294.85150000","fees_amount":"0.29485150","fees_currency":"xrp","minor_currency":"mxn","major_currency":"xrp","oid":"eIWDMLdNXK2Wvqxk","tid":"17970750","price":"5.1687","side":"buy","maker_side":"buy"},{"book":"xrp_mxn","created_at":"2020-09-14T10:29:55+0000","minor":"-103538.00000000","major":"20000.00000000","fees_amount":"20.00000000","fees_currency":"xrp","minor_currency":"mxn","major_currency":"xrp","oid":"xMSTfJJPydmMsx4a","tid":"17970738","price":"5.1769","side":"buy","maker_side":"buy"},{"book":"btc_mxn","created_at":"2020-09-14T09:37:39+0000","minor":"8947.97959853","major":"-0.04075142","fees_amount":"17.89595920","fees_currency":"mxn","minor_currency":"mxn","major_currency":"btc","oid":"GWNkpxGWGab30YCG","tid":"17970649","price":"219574.67","side":"sell","maker_side":"sell"}'

I tried to do the following to transform it into a pandas dataframe:

df = pd.read_csv(data, sep="{}")
df

but then basically my rows are just headers, any idea why? thanks!

Andrej Kesely · Accepted Answer · 2020-09-14 11:05:16Z

You can use pandas.read_json() for the task:

s = '{"book":"xrp_mxn","created_at":"2020-09-14T10:39:07+0000","minor":"-101850.00105195","major":"19705.14850000","fees_amount":"19.70514850","fees_currency":"xrp","minor_currency":"mxn","major_currency":"xrp","oid":"eIWDMLdNXK2Wvqxk","tid":"17970757","price":"5.1687","side":"buy","maker_side":"buy"},{"book":"xrp_mxn","created_at":"2020-09-14T10:34:23+0000","minor":"-1523.99894805","major":"294.85150000","fees_amount":"0.29485150","fees_currency":"xrp","minor_currency":"mxn","major_currency":"xrp","oid":"eIWDMLdNXK2Wvqxk","tid":"17970750","price":"5.1687","side":"buy","maker_side":"buy"},{"book":"xrp_mxn","created_at":"2020-09-14T10:29:55+0000","minor":"-103538.00000000","major":"20000.00000000","fees_amount":"20.00000000","fees_currency":"xrp","minor_currency":"mxn","major_currency":"xrp","oid":"xMSTfJJPydmMsx4a","tid":"17970738","price":"5.1769","side":"buy","maker_side":"buy"},{"book":"btc_mxn","created_at":"2020-09-14T09:37:39+0000","minor":"8947.97959853","major":"-0.04075142","fees_amount":"17.89595920","fees_currency":"mxn","minor_currency":"mxn","major_currency":"btc","oid":"GWNkpxGWGab30YCG","tid":"17970649","price":"219574.67","side":"sell","maker_side":"sell"}'

df = pd.read_json('[' + s + ']')
print(df)

Prints:

      book                created_at          minor         major  fees_amount fees_currency minor_currency major_currency               oid       tid        price  side maker_side
0  xrp_mxn 2020-09-14 10:39:07+00:00 -101850.001052  19705.148500    19.705148           xrp            mxn            xrp  eIWDMLdNXK2Wvqxk  17970757       5.1687   buy        buy
1  xrp_mxn 2020-09-14 10:34:23+00:00   -1523.998948    294.851500     0.294851           xrp            mxn            xrp  eIWDMLdNXK2Wvqxk  17970750       5.1687   buy        buy
2  xrp_mxn 2020-09-14 10:29:55+00:00 -103538.000000  20000.000000    20.000000           xrp            mxn            xrp  xMSTfJJPydmMsx4a  17970738       5.1769   buy        buy
3  btc_mxn 2020-09-14 09:37:39+00:00    8947.979599     -0.040751    17.895959           mxn            mxn            btc  GWNkpxGWGab30YCG  17970649  219574.6700  sell       sell

Nikhil K. · Accepted Answer · 2020-09-14 11:08:19Z

1

Here you go:

import ast 
import pandas as pd

inp_str = <your string input here>

# create a dictionary from string representation of the dictionary
inp_dict = ast.literal_eval(inp)

# convert dictionary to dataframe
inp_df = pd.DataFrame(inp_dict)

print(inp_df)

answered Sep 14, 2020 at 11:08

Nikhil K.

3011 silver badge9 bronze badges

Comments

sammywemmy · Accepted Answer · 2020-09-14 11:02:58Z

0

Try ast, which should help in reading python data structures that are embedded in strings:

from ast import literal_eval

pd.DataFrame(literal_eval(data))

answered Sep 14, 2020 at 11:02

sammywemmy

28.9k4 gold badges21 silver badges35 bronze badges

Collectives™ on Stack Overflow

Transform a specific string to pandas dataframe

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related