Python pandas extract values from dict within a list

Question

Basically I would like to extract price value from this column, but the problem that if I use index [0] it would bring index[0] error in line 11 and price key also does not exit in this line.

ex
0    {u'availableToBack': [{u 'price' : 1.02, u 'size' : 2.15}], u 'availableToLay' : [], u 'tradedVolume' : []}
1    {u'availableToBack': [{u'price': 6.8, u'size':...
2    {u'availableToBack': [{u'price': 8.2, u'size':...
3    {u'availableToBack': [{u'price': 9.6, u'size':...
10   {u'availableToBack': [{u'price': 70.0, u'size'...
11   {u'availableToBack': [], u'availableToLay': []...
12   {u'availableToBack': [], u'availableToLay': []...

I use something like this

table['price'] = table['ex']['availableToBack'][0].apply(lambda x: x.get('price'))

Can you help me how to go around it, please?

JSON looks like this:

    u 'runners' : [{
            u 'status' : u 'ACTIVE',
            u 'handicap' : 0.0,
            u 'selectionId' : 10861647,
            u 'totalMatched' : 0.0,
            u 'adjustmentFactor' : 16.631,
            u 'ex' : {
                u 'availableToBack' : [{
                        u 'price' : 1.02,
                        u 'size' : 2.15
                    }
                ],
                u 'availableToLay' : [],
                u 'tradedVolume' : []
            }
        }, {
            u 'status' : u 'ACTIVE',
            u 'handicap' : 0.0,
            u 'selectionId' : 10861648,
            u 'totalMatched' : 0.0,
            u 'adjustmentFactor' : 13.237,
            u 'ex' : {
                u 'availableToBack' : [{
                        u 'price' : 1.01,
                        u 'size' : 7.11
                    }
                ],
                u 'availableToLay' : [],
                u 'tradedVolume' : []
            }
        },

can you post your json ? I don't see `:` after 0 – dnit13 Feb 26 '16 at 15:00 — dnit13, Feb 26 '16 at 15:00

sabbahillel · Answer 1 · 2016-02-26T15:51:59.463

10   {u'availableToBack': [{u'price': 70.0, u'size'...
11   {u'availableToBack': [], u'availableToLay': []...

I just saw that your entry for availableToBack contains an empty list. Put in a test which will set it to [dict()] if the list value is empty. This will allow the x.get('price', 0) to work correctly. Then you will be able to process the value of 'price' as 0 in the rest of your code.

You will then not have to test for 'None'.

x.get('price') has no "default" value specified so it returns "None". Set up a default value (such as x.get('price', 0)) That way you should be able to perform the operations with a 0 price.

Python dictionary get() Method

Description

The method get() returns a value for the given key. If key is not available then returns default value None. Syntax

Following is the syntax for get() method −

dict.get(key, default=None)

Parameters
key -- This is the Key to be searched in the dictionary.

default -- This is the Value to be returned in case key does not exist.
Return Value

This method returns a value for the given key. If key is not available, then it returns the default value None.

score 0 · Answer 2 · edited May 23 '17 at 11:59

I don't think Python has a safe, built-in method for getting a value from a list without triggering an IndexError, but it is easy enough to write one. (This answer has more detail on that.)

If I understand your intention correctly, this may help you. (Note that this only addresses the issue of getting the list item safely. You may want to use .get for each dictionary value you are retrieving for more safety.)

def safeget(seq, index, default=None):
    try:
        return seq[index]
    except IndexError:
        return default

for table in runners:
    price = safeget(table['ex']['availableToBack'], 0, default={}).get('price')
    print price

I tested against this structure, which has a an empty list for u'availableToBack in the second object.

runners = [
    {
        u'status' : u'ACTIVE',
        u'handicap' : 0.0,
        u'selectionId' : 10861647,
        u'totalMatched' : 0.0,
        u'adjustmentFactor' : 16.631,
        u'ex' : {
            u'availableToBack' : [
                    {u'price' : 1.02, u'size' : 2.15},
                ],
            u'availableToLay' : [],
            u'tradedVolume' : []
            }
    }, {
        u'status' : u'ACTIVE',
        u'handicap' : 0.0,
        u'selectionId' : 10861648,
        u'totalMatched' : 0.0,
        u'adjustmentFactor' : 13.237,
        u'ex' : {
            u'availableToBack' : [
                ## this one is MISSING a price
            ],
            u'availableToLay' : [],
            u'tradedVolume' : []
        }
    }
]

I think that he is using a dict() type with the key 'price' I think that the problem is that with no default, it returns a value of None. — sabbahillel, Feb 26 '16 at 15:37
I thought the issue was that his `availableToBack` is an array that may sometimes be empty, so his `[0]` index sometimes raised an IndexError. (Line 11 of his first example has an empty array.) — zekel, Feb 26 '16 at 15:41

score 0 · Answer 3 · answered Feb 26 '16 at 16:50

0

You can use a ternary operator. An empty list evaluates to false.

table['price'] = (table['ex']['availableToBack'][0].get('price') 
                  if table['ex']['availableToBack'] 
                  else None)

answered Feb 26 '16 at 16:50

Alexander

105,104
32
201
196

score 0 · Answer 4 · answered Feb 26 '16 at 17:48

0

I found a solution and decided to post it. Basically maybe I did not explain well, but the problem is inconsistencies of having anything within the list.

table['availableToBack.price'] = table['ex'].apply(lambda x: x['availableToBack'][0].get('price') if (len(x['availableToBack']) != 0) else None)

answered Feb 26 '16 at 17:48

Arthur Zangiev

1,438
3
14
21

You can also do a list comprehension instead of apply/lambda.`[xx['availableToBack'][0].get('price') for xx in table['ex'] if len(xx['availableToBack']) else None]` – zekel Feb 27 '16 at 17:39

Python pandas extract values from dict within a list

4 Answers4