Pull a list of json-formatterd strings from a redis database into a Pandas dataframe

Question

I have a webapp that reads from a redis database. The database returns a List of Strings in json format. See this code snippet:

import redis
r = redis.StrictRedis(**redis_config)
keys = r.keys(pattern="*")
redis_values = r.mget(keys)
print(values[0:2])
print(type(redis_values))
print(type(redis_valus[0]))

Output:

['{"timestampx": "1621544968.075360000", "length": "528", "dscp": "0", "srcip": "172.16.1.2", "destip": "172.17.4.2"}', '{"timestampx": "1621544968.075750000", "length": "96", "dscp": "0", "srcip": "172.17.4.2", "destip": "172.16.1.2"}']
<class 'list'>
<class 'str'>

I cannot get this List of JSON strings into a Pandas dataframe. If I use:

myFrame = pd.DataFrame(redis_values)
print(myFrame.head()

Output:

                                                   0
0  {"timestampx": "1621620153.864122000", "length...
1  {"timestampx": "1621620111.615499000", "length...
2  {"timestampx": "1621620157.386244000", "length...
3  {"timestampx": "1621620123.367638000", "length...
4  {"timestampx": "1621620152.200464000", "length...

That's a 1-column frame with strings, not a 5-column frame with the data pulled from JSON.

What if I use read_json?

myFrame = pd.read_json(redis_values)

Output:
ValueError: Invalid file path or buffer object type: <class 'list'>

That fails completely.

What if I convert the List of strings to a List of JSON objects?

myJson = []
for rv in redis_values:
    rv = json.loads(rv)
    myJson.append(rv)
myFrame = pd.read_json(myJson)

Output: 

ValueError: Invalid file path or buffer object type: <class 'list'>

If I dump redis_values to a file and then use read_json it works, but that's incredibly inefficient.

f = open('myjson.txt','w')
for rv in redis_values:
    f.write(rv+'\n')
f.close()
myFrame = pd.read_json('myjson.txt', lines=True)

Converting a List of Strings in JSON to a DataFrame shouldn't be this difficult. Can you help me?

Rakesh · Accepted Answer · 2021-05-21 18:39:19Z

1

Try using json.loads with pd.DataFrame

Ex:

import json

df = pd.DataFrame(map(json.loads, vals))  # OR redis_values
print(df)

answered May 21, 2021 at 18:39

Rakesh

82.9k17 gold badges86 silver badges122 bronze badges

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

Collectives™ on Stack Overflow

Pull a list of json-formatterd strings from a redis database into a Pandas dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related