Error with shape of passed value to pandas dataframe

Question

I parsed a json object obtained from hidden api on website; after some trouble i get finally what i wanted but got an error creatinf the dataframe. Here's the code:

import pandas as pd
import requests
url = "https://ero.betfair.com/www/sports/exchange/readonly/v1/bymarket?_ak=nzIFcwyWhrlwYMrh&alt=json&currencyCode=USD&locale=en_GB&marketIds=1.203214047,1.203214188,1.203257683,1.203632485,1.203355173,1.203417246,1.203329951,1.203642227,1.203641848,1.203632042,1.203642486,1.203641589,1.203643135&rollupLimit=25&rollupModel=STAKE&types=MARKET_STATE,RUNNER_STATE,RUNNER_EXCHANGE_PRICES_BEST"
r = requests.get(url).json()


eventid = []
marketid = []
odds1 = []
odds2 = []
oddsx = []

for matches in r['eventTypes'][0]['eventNodes']:

    eventid.append(matches['eventId'])
    marketid.append(matches['marketNodes'][0]['marketId'])
    odds1.append(matches['marketNodes'][0]['runners'][0]['exchange']['availableToBack'][0]['price'])
    odds2.append(matches['marketNodes'][0]['runners'][1]['exchange']['availableToBack'][0]['price'])
    oddsx.append(matches['marketNodes'][0]['runners'][2]['exchange']['availableToBack'][0]['price'])

df = pd.DataFrame(eventid, marketid, odds1, odds2, oddsx)

`The error i got is: "Shape of passed values is (13, 1), indices imply (13, 13)" In fact if i run for example odds1 i got this:

Out[261]: [1.45, 2.46, 1.82, 1.84, 1.41, 1.16, 1.84, 3.25, 4.1, 2.18, 2.96, 2.72, 3.0]

Expected is  [1.45
              2.46
              1.82
              ...]

like other eventid and marketid data, don't know why because i did the append the same way...any idea why and ho to get data as expected? Thanks!

This should be the first part of the dicitonary: {"currencyCode":"USD","eventTypes":[{"eventTypeId":1,"eventNodes":[{"eventId":31733655,"marketNodes":[{"marketId":"1.203214047","isMarketDataDelayed":true,"state":{"betDelay":5,"bspReconciled":false,"complete":true,"inplay":true,"numberOfWinners":1,"numberOfRunners":3,"numberOfActiveRunners":0,"totalMatched":0.0,"totalAvailable":0.0,"crossMatching":false,"runnersVoidable":false,"version":4812185735,"status":"CLOSED"},"runners":[{"selectionId":47999,"handicap":0.0,"state":{"sortPriority":1,"status":"WINNER"},"exchange":{}},{"selectionId":48351,"handicap":0.0,"state":{"sortPriority":2,"status":"LOSER"},"exchange":{}},{"selectionId":58805,"handicap":0.0,"state":{"sortPriority":3,"status":"LOSER"},"exchange":{}}],"isMarketDataVirtual":false}]},{"eventId":31733656,"marketNodes":[{"marketId":"1.203214188","isMarketDataDelayed":true,"state":{"betDelay":5,"bspReconciled":false,"complete":true,"inplay":true,"numberOfWinners":1,"numberOfRunners":3,"numberOfActiveRunners":0,"totalMatched":0.0,"totalAvailable":0.0,"crossMatching":false,"runnersVoidable":false,"version":4813089879,"status":"CLOSED"},"runners":[{"selectionId":48317,"handicap":0.0,"state":{"sortPriority":1,"status":"LOSER"},"exchange":{}},{"selectionId":63908,"handicap":0.0,"state":{"sortPriority":2,"status":"LOSER"},"exchange":{}},{"selectionId":58805,"handicap":0.0,"state":{"sortPriority":3,"status":"WINNER"},"exchange":{}}],"isMarketDataVirtual":false}]},{"eventId":31735848,"marketNodes":[{"marketId":"1.203257683","isMarketDataDelayed":true,"state":{"betDelay":0,"bspReconciled":false,"complete":true,"inplay":false,"numberOfWinners":1,"numberOfRunners":3,"numberOfActiveRunners":3,"lastMatchTime":"2022-10-02T19:38:11.620Z","totalMatched":49380.28488825905,"totalAvailable":336698.8504071475,"crossMatching":true,"runnersVoidable":false,"version":4813316847,"status":"OPEN"},"runners":[{"selectionId":48461,"handicap":0.0,"state":{"sortPriority":1,"lastPriceTraded":1.83,"totalMatched":0.0,"status":"ACTIVE"},"exchange":{"availableToBack":[{"price":1.81,"size":385.8},{"price":1.8,"size":4509.05},{"price":1.79,"size":358.73}],"availableToLay":[{"price":1.83,"size":944.49},{"price":1.84,"size":1130.96},{"price":1.85,"size":340.52}]}},{"selectionId":39674645,"handicap":0.0,"state":{"sortPriority":2,"lastPriceTraded":4.7,"totalMatched":0.0,"status":"ACTIVE"},"exchange":{"availableToBack":[{"price":4.6,"size":421.05},{"price":4.5,"size":1157.23},{"price":4.4,"size":523.69}],"availableToLay":[{"price":4.7,"size":939.14},{"price":4.8,"size":1347.8},{"price":4.9,"size":2327.07}]}},{"selectionId":58805,"handicap":0.0,"state":{"sortPriority":3,"lastPriceTraded":4.3,"totalMatched":0.0,"status":"ACTIVE"},"exchange":{"availableToBack":[{"price":4.2,"size":1208.24},{"price":4.1,"size":3977.22},{"price":4.0,"size":3579.21}],"availableToLay":[{"price":4.3,"size":4990.36},{"price":4.4,"size":5192.82},{"price":4.5,"size":6148.02}]}}],"isMarketDataVirtual":true}]}

Could you please paste the value of `r` (i.e. the json) in the description? I cannot access the website due to country restrictions. — theodosis, Oct 02 '22 at 19:26
Yes in any case is almost changed cause some matches are played, but anyway the json is 17050 character (i've a new one but don't know if i can post here), i attached the viewer where you can see the structure...hope this help! — eestlane, Oct 02 '22 at 20:44
Do all of your five lists have the same size? Are they just 1D and all have the same length? If so, all you want is for each list to be a column in your final dataframe? — theodosis, Oct 02 '22 at 20:53
Thanks for the screenshot by the way! Could you maybe just post the first dictionary in the description? I suppose that the rest of the dicts will be the same either way. I am referring to `r['eventTypes'][0]['eventNodes'][0]` which should probably be the first dict if I am not mistaken! — theodosis, Oct 02 '22 at 20:55
Added the dictionary even if putting it on the json viewer gives me an error, and yes for your answer, they have all the same lenght and i need each of them to be column in my db — eestlane, Oct 02 '22 at 21:16

score 1 · Accepted Answer · answered Oct 02 '22 at 21:38

1

You could try initializing the dataframe in the following way:

df = pd.DataFrame({
    'eventid': eventid,
    'market_id': marketid,
    'odds1': odds1,
    'odds2': odds2,
    'oddsx': oddsx
})

answered Oct 02 '22 at 21:38

theodosis

894
5
15

Thank you, it worked! Even if i don't get why :-) – eestlane Oct 02 '22 at 21:54
1

It's better to wrap the lists that you pass in the constructor. Otherwise, it cannot understand that you want each passed list to be a column and as a result, messy things happen. You could also see [here](https://stackoverflow.com/a/59871204/14594208) a solution that makes use of a list (of lists) instead of a dict! This is a bit more complex, because each list inside the outer list is passed as a row (instead of column) and then you'd have to transpose the matrix (that's why `T` is being used in the link above). Either way, you could always go for the dict-oriented solution that I provided! – theodosis Oct 02 '22 at 22:10
Got it, thank you for clarifying! – eestlane Oct 03 '22 at 06:53

Error with shape of passed value to pandas dataframe

1 Answers1