looping through list of json arrays within pandas dataframe column

Question

I am trying to extract all of the 'uid' values into a simple python list for each row of the 'data' column and have been unsuccessful.

Here is the code so far:

import json

df = pd.DataFrame({
    'bank_account': [101, 102, 201, 301],
    'data': [
        '[{"uid": 100, "account_type": 1, "account_data": {"currency": {"current": 1000, "minimum": -500}, "fees": {"monthly": 13.5}}, "user_name": "Alice"},{"uid": 150, "account_type": 1, "account_data": {"currency": {"current": 1000, "minimum": -500}, "fees": {"monthly": 13.5}}, "user_name": "jer"}]',
        '[{"uid": 100, "account_type": 2, "account_data": {"currency": {"current": 2000, "minimum": 0},  "fees": {"monthly": 0}}, "user_name": "Alice"}]',
        '[{"uid": 200, "account_type": 1, "account_data": {"currency": {"current": 3000, "minimum": 0},  "fees": {"monthly": 13.5}}, "user_name": "Bob"}]',        
        '[{"uid": 300, "account_type": 1, "account_data": {"currency": {"current": 4000, "minimum": 0},  "fees": {"monthly": 13.5}}, "user_name": "Carol"}]'        
    ]},
    index = ['Alice', 'Alice', 'Bob', 'Carol']
)

# df["data"] = df["data"].apply(lambda x: pd.read_json(x, lines=True)["uid"][0])

df["data"] = [[json.loads(d)['uid'] for d in li] for li in df['data']]
print(df)

user7864386 · Accepted Answer · 2022-03-04 00:25:14Z

2

You could iterate over the list created by json.loads and get "uid" values:

import json
df['data'] = [[item['uid'] for item in json.loads(d)] for d in df['data']]

Output:

       bank_account        data
Alice           101  [100, 150]
Alice           102       [100]
Bob             201       [200]
Carol           301       [300]

You could also explode "data":

df = df.explode('data')

Output:

       bank_account data
Alice           101  100
Alice           101  150
Alice           102  100
Bob             201  200
Carol           301  300

answered Mar 4, 2022 at 0:25

user7864386

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

Comments

Parvesh Kumar · Accepted Answer · 2022-03-04 00:25:59Z

Modified apply a bit as you're using apply

import pandas as pd

df = pd.DataFrame(
    {
        "bank_account": [101, 102, 201, 301],
        "data": [
            '[{"uid": 100, "account_type": 1, "account_data": {"currency": {"current": 1000, "minimum": -500}, "fees": {"monthly": 13.5}}, "user_name": "Alice"},{"uid": 150, "account_type": 1, "account_data": {"currency": {"current": 1000, "minimum": -500}, "fees": {"monthly": 13.5}}, "user_name": "jer"}]',
            '[{"uid": 100, "account_type": 2, "account_data": {"currency": {"current": 2000, "minimum": 0},  "fees": {"monthly": 0}}, "user_name": "Alice"}]',
            '[{"uid": 200, "account_type": 1, "account_data": {"currency": {"current": 3000, "minimum": 0},  "fees": {"monthly": 13.5}}, "user_name": "Bob"}]',
            '[{"uid": 300, "account_type": 1, "account_data": {"currency": {"current": 4000, "minimum": 0},  "fees": {"monthly": 13.5}}, "user_name": "Carol"}]',
        ],
    },
    index=["Alice", "Alice", "Bob", "Carol"],
)
df["uid"] = df["data"].apply(lambda x: eval(x)[0]["uid"])
print(df)

Then for list you can do print(list(df["uid"]))

Collectives™ on Stack Overflow

looping through list of json arrays within pandas dataframe column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related