CODEWITHSUNDEEP
pythonarrayspython-3.xpandasdata-science
Chris90
Chris90

Reputation: 1998

Turning a Json table into a standard df

I have a json array table that I am inserting into notebook such as

Table
{"id":"v100",signal:"2017-10-02 03:30:00",mode:"online"}
{"id":"v100",signal:"2017-10-02 06:30:00",mode:"online"}
{"id":"v400",signal:"2017-10-03 03:30:00",mode:"online"}
{"id":"v400",signal:"2017-10-04 05:30:00",mode:"offline"}

Without column names and just those headers and values in each row.

How can I transform this into a dataframe that has column names for:

id          | signal       | mode
v100   2017-10-02 03:30:00   online
v100   2017-10-02 06:30:00   online
v400   2017-10-02 03:30:00   online
v400   2017-10-02 05:30:00   offline

Thanks

Upvotes: 0

Views: 46

Answers (2)

jezrael
jezrael

Reputation: 862611

Use list comprehension with convert json to dictionaries and then pass it to DataFrame constructor:

import ast, json

print (df)
                                               Table
0  {"id":"v100","signal":"2017-10-02 03:30:00","m...
1  {"id":"v100","signal":"2017-10-02 06:30:00","m...
2  {"id":"v400","signal":"2017-10-03 03:30:00","m...
3  {"id":"v400","signal":"2017-10-04 05:30:00","m...


df = pd.DataFrame([ast.literal_eval(x) for x in df['Table']])
#alternative
#df = pd.DataFrame([json.loads(x) for x in df['Table']])
print (df)
     id               signal     mode
0  v100  2017-10-02 03:30:00   online
1  v100  2017-10-02 06:30:00   online
2  v400  2017-10-03 03:30:00   online
3  v400  2017-10-04 05:30:00  offline

Or if posible use:

df = pd.read_json(file, lines=True)

Upvotes: 1

Frank-Rene Schäfer
Frank-Rene Schäfer

Reputation: 3352

Do:

awk 'BEGIN{FS="\""}{ print $4 " " $6 " " $8; }' myTable.txt

The table line etc. is trivial.

Upvotes: 0

Related Questions