Python Forum
Make a table from a json output
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Make a table from a json output
#1
Hi all

Hope someone can point me in the right direction here.
Im using api to call a website, where i get this response. (cleaned for a few things)

So below i got the data. I would like to make a table from that data. First just a simple table inside python, later on a table i can put in a browser. (using flask or others)

Task1:
But how do i do it?
Would like the headers listed in top: Date | Day | Week | Season .... | points | o:points etc.
Then the data from groups below that. 20110911| Sunday | 1 | 2011 .... | 30 | 13 etc.
20110918| Sunday | 2 | 2011 .... | 12 | 30 etc.

Task2:

Add a header so after the total header there is a another one called: SUM
And below that "column" i would like to make a calculation from the points and o:points columns. like below

Date | Day | Week | Season .... | points | o:points | SUM etc.
20110911| Sunday | 1 | 2011 .... | 30 | 13 | 43 etc.
20110918| Sunday | 2 | 2011 .... | 12 | 30 | 42 etc.

Its my first post in here, and im pretty new to these part of python, but hoping i have described it good enough.
Thanks in advance for all inputs.

/ Carsten

data = json.loads(temp)
pprint(data)
{'groups': [{'columns': [[20110911, 20110918],
                         ['Sunday', 'Sunday'],
                         [1, 2],
                         [2011, 2011],
                         ['Bears', 'Bears'],
                         ['Falcons', 'Saints'],
                         ['home', 'away'],
                         [30, 13],
                         [12, 30],
                         [2.0, 6.0],
                         [40.5, 47.0]],
             'sdql': 'team = Bears and season = 2011 and week < 3'}],
 'headers': ['date',
             'day',
             'week',
             'season',
             'team',
             'o:team',
             'site',
             'points',
             'o:points',
             'line',
             'total']}
Reply
#2
If I understand it well, then you ask how to separately parse particular data from JSON objects.

So when you need to extract a particular variable then you can construct it like:
your_variable = data["groups"][0]["columns"]... and so far.
When you parse JSON, you just create a crew/line.

Do you mean a beautiful HTML + CSS table on the client-side languages? Maybe, it's gonna take a time which could be spent in a better way (than a free work) and you would like to hire someone. If you have just another question on how you can work on it by yourself then here with it and you will get an answer.

Best regards
Reply
#3
(Dec-29-2018, 10:17 PM)Unisoftdev Wrote: If I understand it well, then you ask how to separately parse particular data from JSON objects.

So when you need to extract a particular variable then you can construct it like:
your_variable = data["groups"][0]["columns"]... and so far.
When you parse JSON, you just create a crew/line.

Do you mean a beautiful HTML + CSS table on the client-side languages? Maybe, it's gonna take a time which could be spent in a better way (than a free work) and you would like to hire someone. If you have just another question on how you can work on it by yourself then here with it and you will get an answer.

Best regards

Thanks for input. Didnt know i had to parse out each line. (Still learnig python expressions, and not beeing native english language i miss some of them, but do know parse now :) )

So what i did now is i made a new line for each data set i need:

# First making the headers for the table
title = data['headers']
# Then the data
date = data['groups'][0]['columns'][0]
day = data['groups'][0]['columns'][1]
week = data['groups'][0]['columns'][2]
season = data['groups'][0]['columns'][3]
team = data['groups'][0]['columns'][4]
oteam = data['groups'][0]['columns'][5]
site = data['groups'][0]['columns'][6]
points = data['groups'][0]['columns'][7]
opoints = data['groups'][0]['columns'][8]
line = data['groups'][0]['columns'][9]
total = data['groups'][0]['columns'][10]
Google a bit and found prettytable. That looks fine for me in python. Dont know what to pick from a webpage. Totally newbie there, so dont know anything about it.

so:
from prettytable import prettytable
# Starts the prettytable with the headings for all columns
t = PrettyTable(title)
# Adds all the data to the table, 1 row at a time
t.add_row([date[0], day[0], week[0], season[0], team[0], oteam[0], site[0], points[0], opoints[0], line[0], total[0]])
t.add_row([date[1], day[1], week[1], season[1], team[1], oteam[1], site[1], points[1], opoints[1], line[1], total[1]])

print[t]
+----------+--------+------+--------+-------+---------+------+--------+----------+------+-------+
|   date   |  day   | week | season |  team |  o:team | site | points | o:points | line | total |
+----------+--------+------+--------+-------+---------+------+--------+----------+------+-------+
| 20110911 | Sunday |  1   |  2011  | Bears | Falcons | home |   30   |    12    | 2.0  |  40.5 |
| 20110918 | Sunday |  2   |  2011  | Bears |  Saints | away |   13   |    30    | 6.0  |  47.0 |
+----------+--------+------+--------+-------+---------+------+--------+----------+------+-------+
So far so good :)
Now i would like to add SUM next to total.

title.append('SUM')
Then i would like to sum up the numbers from points and o:points columns. So i below sum gets 42 and 43 accordingly.

Also bonus question.
I never know if my api returns 1 line of data or 200 lines.
So how to write the t.add_row part when lines is dynamic.

Also if there is 200 lines, i only need to print out 10 lines or so. How to add that part.

Thanks

/ Carsten
Reply
#4
You can process this data more efficiently:

def pivot_table(data):
	output = []
    length = len(data[0])
	for i in range(length):
		output.append([datapoint[i] for datapoint in data])
	return output

pivoted_data = pivot_table(data["groups"][0]["columns"])

for row in pivoted_data:
    t.add_row(row)
The output of the function is:
Output:
[[20110911, 'Sunday', 1, 2011, 'Bears', 'Falcons', 'home', 30, 12, 2.0, 40.5], [20110918, 'Sunday', 2, 2011, 'Bears', 'Saints', 'away', 13, 30, 6.0, 47.0]]
To retrieve only the first ten rows, you can modify the function above to only iterate over ten items. Although, it sounds like you may be more interested in doing aggregate calculations on the data set. Have you looked at namedtuple() in the collections module? That creates a data structure that's optimized for aggregate functions like sum, average, etc.
Reply
#5
Thanks for input stullis.
Have read the post 10 times or so, and still dont understand whats happening. And pasting it in, just gives me an error.
So not sure what to get out of this at this moment. :(

/ Carsten

(Dec-30-2018, 12:36 AM)stullis Wrote: You can process this data more efficiently:

def pivot_table(data):
	output = []
    length = len(data[0])
	for i in range(length):
		output.append([datapoint[i] for datapoint in data])
	return output

pivoted_data = pivot_table(data["groups"][0]["columns"])

for row in pivoted_data:
    t.add_row(row)
The output of the function is:
Output:
[[20110911, 'Sunday', 1, 2011, 'Bears', 'Falcons', 'home', 30, 12, 2.0, 40.5], [20110918, 'Sunday', 2, 2011, 'Bears', 'Saints', 'away', 13, 30, 6.0, 47.0]]
To retrieve only the first ten rows, you can modify the function above to only iterate over ten items. Although, it sounds like you may be more interested in doing aggregate calculations on the data set. Have you looked at namedtuple() in the collections module? That creates a data structure that's optimized for aggregate functions like sum, average, etc.
Reply
#6
What error are you getting?

As for an explanation, the function accepts a list of sub-lists and transforms it. The transformation process (in the loop) takes the data in each sub-list for a specified index and aggregates them into a single list. Like so:

[[1,2,3],[1,2,3]] => [[1,1],[2,2],[3,3]]

This saves a lot of effort to change your data set into rows for the table.
Reply
#7
(Dec-30-2018, 08:43 PM)stullis Wrote: What error are you getting?

As for an explanation, the function accepts a list of sub-lists and transforms it. The transformation process (in the loop) takes the data in each sub-list for a specified index and aggregates them into a single list. Like so:

[[1,2,3],[1,2,3]] => [[1,1],[2,2],[3,3]]

This saves a lot of effort to change your data set into rows for the table.

Since i got that error from your code i tried a bit my self, and got below. But only gives me the first line, and since your code is working for me now, i stick to that :)

entries = len(date)

t = PrettyTable(title)

count = 1
while count <= entries:
    t.add_row(
        [date[0], day[0], week[0], season[0], team[0], oteam[0], site[0], points[0], opoints[0], line[0], total[0]])
    if count == entries:
        break
    count +=1
Just tried running your code again. And it worked perfect.. Have no idea what i did earlier today. :D
It looks great. Does exactly what i needed :)

How do i add a header column? Like sum.. Where i can add the points and opoints

Thanks for input stullis. Taking it all in and learning for future stuff :)
Reply
#8
(Dec-30-2018, 09:35 PM)carstenlp Wrote: How do i add a header column? Like sum.. Where i can add the points and opoints
pandas is also an good option,make it easier to do stuff like calculate,sum,plot..ect.
Also when have a Dataframe it's easier to put on web as i done several times with Flask.

As your JSON(json.loads) output give back a dictionary.
Can clean up a little and make and take into pandas with pd.DataFrame.from_dict(d).
Can also read JSON directly with pandas.read_json().
i just add d = in front of data output in first post.
>>> record = {}
>>> for i in zip(d['headers'], d['groups'][0]['columns']):
...     record[i[0]]  = i[1] 
...     
>>> record
{'date': [20110911, 20110918],
 'day': ['Sunday', 'Sunday'],
 'line': [2.0, 6.0],
 'o:points': [12, 30],
 'o:team': ['Falcons', 'Saints'],
 'points': [30, 13],
 'season': [2011, 2011],
 'site': ['home', 'away'],
 'team': ['Bears', 'Bears'],
 'total': [40.5, 47.0],
 'week': [1, 2]} 
Then in pandas:
[Image: 7Rs4O3.jpg]
Reply
#9
(Dec-30-2018, 11:04 PM)snippsat Wrote: ...

Hi snippsat

Thx for input. Have heard about panda before, and its on my to do list to look more into. Think i can use it to a lot.

But when i print(df) i get this:
       date     day  line  o:points ...   site   team  total week
0  20110911  Sunday   2.0        12 ...   home  Bears   40.5    1
1  20110918  Sunday   6.0        30 ...   away  Bears   47.0    2

[2 rows x 11 columns]
How do i get it to print all 11 columns ?

Also i problably didnt explain it good enough.
For row 0 i want points and o:points to be added, and that showed in new column 12 named Sum.
Same with 1 etc.

I got ideas for about 5 columns more, all data for those columns is from calculations, so think if i just learn to do one of them i can always add on.

/ Carsten
Reply
#10
(Jan-01-2019, 08:30 PM)carstenlp Wrote: How do i get it to print all 11 columns ?
It can be better to work in Jupyter Notebook with pandas DataFrame.
All the data is still there,it's just dos it for easier display of long DataFames.
Will force it to display any number of columns.
pandas.set_option('display.max_columns', None)
Also a great editor like VS Code can run Notebook Cell(#%%) inline in Editor.
Also no need for print().
VS Code from start

[Image: umXNJj.jpg]
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  write json into a table herobpv 4 1,424 Jan-22-2023, 04:36 AM
Last Post: herobpv
  Convert Json to table format python_student 2 5,063 Sep-28-2022, 12:48 PM
Last Post: python_student
  geojson to json --missing multiple row output yoshi 9 2,654 Mar-06-2022, 08:34 PM
Last Post: snippsat
  Python script to summarize excel tables, then output a composite table? i'm a total n surfer349 1 2,290 Feb-05-2021, 04:37 PM
Last Post: nilamo
  sports Stats > table output loop problems paulfearn100 3 2,445 Jul-22-2020, 03:21 AM
Last Post: c_rutherford
  Save output into a Excel Sheet with Format Table skaailet 1 2,455 Apr-17-2020, 11:56 PM
Last Post: thirteendec
  json.dumps list output qurr 12 5,072 Apr-08-2020, 10:13 PM
Last Post: micseydel
  json.dumps output error in python3 prayuktibid 2 2,614 Jan-21-2020, 06:41 AM
Last Post: prayuktibid
  Output to a json file problem Netcode 3 3,663 Nov-22-2019, 01:44 AM
Last Post: Skaperen
  Problem Table Output Phil 4 2,573 May-19-2019, 12:17 PM
Last Post: Phil

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020