converting 2d json(?) array to pandas dataframe

Question

I have used BeautifulSoup to collect a 2D data array from a website as a string. I believe that the table format is related to the json format, however, when I tried to apply pandas.read_json() on the string it gives a value error. I tried converting the "nul" to "0" and remove the "\n" from the string to no avail.

data_str = """[[{label:'column 1',type:'number'},{label:'column2',type:'number'},{label:'column 3',type:'number'}],
[205, null,  89748],
[206, null,  66813],
[235,   75,   null],
[236,  138,   null]]"""

I can convert the string to a pandas DataFrame by splitting the first row of the table containing the column names from the data entries, but this seems rather clumsy (see below).

import numpy as np
import pandas as pd
import ast

col_names, data_str = data_str.split('\n',1)
col_names = re.findall(r'label:\'(.*?)\'', col_names)
data_str = data_str.replace('\n','')
data_str = data_str.replace('null','0.')

data_arr = np.array(ast.literal_eval('[' + data_str))
data_df = pd.DataFrame(data_arr, columns = col_names)

Is there a more pythonic way to convert the string to a pandas DataFrame?

This is an aside, and not directly related to the question as asked, but I would think that there was likely a better way to pull this information from whatever site into a dataframe. — Henry Ecker
– Henry Ecker ♦, Commented Jun 25, 2021 at 15:40
@HenryEcker thanks for your comment, this is the first time I try something like this, so you are probably right. Could you be so kind to provide a hint how to proceed? This is the cite I try to get the data from. — Paul
– Paul, Commented Jun 25, 2021 at 15:47

SCKU · Accepted Answer · 2021-06-25 16:31:01Z

1

No, It's not a valid JSON but a javascript object as raw string. You need install another module like demjson. see the answer here for more detais.

answered Jun 25, 2021 at 16:31

SCKU

83310 silver badges15 bronze badges

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

Collectives™ on Stack Overflow

converting 2d json(?) array to pandas dataframe

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related