Search code examples
pythonjsonpandasdataframedatatables

How to pull data from Json and put it in a table format?

I have a Json file with the following (file name is double.JSON)

{"OUT":"074531@@@;1-1=29=0;1-2=193=0;1-3=199=0;1-4=403=0;1-5=334=0;1-6=295=0;1-7=999=0;1-8=57=0;1-9=77=0;1-10=166=0;1-11=116=0;1-12=999=0;2-1=9.3=1;2-2=89=0;2-3=203=0;2-4=346=0;2-5=150=0;2-6=54=0;2-7=466=0;2-8=12=0;2-9=34=0;2-10=115=0;2-11=88=0;2-12=629=0;3-1=87=0;3-2=510=0;3-3=845=0;3-4=873=0;3-5=773=0;3-6=563=0;3-7=999=0;3-8=197=0;3-9=260=0;3-10=601=0;3-11=933=0;3-12=999=0;4-1=60=0;4-2=458=0;4-3=644=0;4-4=540=0;4-5=243=0;4-6=351=0;4-7=999=0;4-8=117=0;4-9=201=0;4-10=256=0;4-11=693=0;4-12=999=0;5-1=16=0;5-2=138=0;5-3=174=0;5-4=674=0;5-5=337=0;5-6=152=0;5-7=731=0;5-8=43=0;5-9=53=0;5-10=181=0;5-11=141=0;5-12=999=0;6-1=80=0;6-2=466=0;6-3=552=0;6-4=999=0;6-5=436=0;6-6=416=0;6-7=999=0;6-8=116=0;6-9=257=0;6-10=676=0;6-11=356=0;6-12=873=0;7-1=35=0;7-2=219=0;7-3=257=0;7-4=483=0;7-5=287=0;7-6=220=0;7-7=474=0;7-8=74=0;7-9=77=0;7-10=186=0;7-11=121=0;7-12=999=0;8-1=52=0;8-2=360=0;8-3=410=0;8-4=820=0;8-5=474=0;8-6=410=0;8-7=999=0;8-8=94=0;8-9=145=0;8-10=326=0;8-11=169=0;8-12=999=0;9-1=34=0;9-2=231=0;9-3=325=0;9-4=492=0;9-5=218=0;9-6=318=0;9-7=999=0;9-8=51=0;9-9=59=0;9-10=187=0;9-11=141=0;9-12=999=0;10-1=59=0;10-2=304=0;10-3=575=0;10-4=902=0;10-5=365=0;10-6=492=0;10-7=999=0;10-8=122=0;10-9=208=0;10-10=246=0;10-11=307=0;10-12=999=0;11-1=78=0;11-2=520=0;11-3=902=0;11-4=999=0;11-5=873=0;11-6=712=0;11-7=999=0;11-8=154=0;11-9=273=0;11-10=458=0;11-11=239=0;11-12=999=0;12-1=55=0;12-2=307=0;12-3=314=0;12-4=751=0;12-5=458=0;12-6=436=0;12-7=999=0;12-8=101=0;12-9=157=0;12-10=300=0;12-11=221=0;12-12=483=0;13-1=11=0;13-2=138=0;13-3=181=0;13-4=410=0;13-5=178=0;13-6=133=0;13-7=721=0;13-8=25=0;13-9=28=0;13-10=94=0;13-11=73=0;13-12=999=0;14-1=15=0;14-2=161=0;14-3=208=0;14-4=257=0;14-5=199=0;14-6=165=0;14-7=552=0;14-8=27=0;14-9=42=0;14-10=131=0;14-11=51=0;14-12=999=0"}

and want to pull the table values and turn it into the following table in python.

enter image description here

I have tried using the following Pandas codes but it is giving me gibberish.

import json
import pandas as pd

with open("double.json") as f:
    data = json.load(f)

df = pd.DataFrame(data)

print(df)

Any help would be much appreciated!

Solution

  • You can use a defaultdict to simplify the generation of data for your dataframe, relying on regex to split out the row, column and value parts of your data:

    import re, json, pandas as pd
from collections import defaultdict

with open("double.json") as f:
    data = json.load(f)

dd = defaultdict(dict)
for r, c, v in re.findall(r'(\d+)-(\d+)=(\d+)', data['OUT']):
    # assuming we want integers:
    dd[int(r)].update({ int(c) : int(v) })
    # otherwise just use dd[r].update({c : v})

df = pd.DataFrame(dd)

    Output:

          1    2    3    4    5    6    7    8    9   10   11   12   13   14
1    29    9   87   60   16   80   35   52   34   59   78   55   11   15
2   193   89  510  458  138  466  219  360  231  304  520  307  138  161
3   199  203  845  644  174  552  257  410  325  575  902  314  181  208
4   403  346  873  540  674  999  483  820  492  902  999  751  410  257
5   334  150  773  243  337  436  287  474  218  365  873  458  178  199
6   295   54  563  351  152  416  220  410  318  492  712  436  133  165
7   999  466  999  999  731  999  474  999  999  999  999  999  721  552
8    57   12  197  117   43  116   74   94   51  122  154  101   25   27
9    77   34  260  201   53  257   77  145   59  208  273  157   28   42
10  166  115  601  256  181  676  186  326  187  246  458  300   94  131
11  116   88  933  693  141  356  121  169  141  307  239  221   73   51
12  999  629  999  999  999  873  999  999  999  999  999  483  999  999