pythonpython-2.7pandasodoolist-processing

List of dictionary in python as HTML Table format


I have list of dictionary as the below format

[{'duration': 0.7, 'project_id': 3, 'resource': u'Arya Stark', 'activity': u'Development'},
{'duration': 0.9, 'project_id': 4, 'resource': u'Ned Stark', 'activity': u'Development'},
{'duration': 2.88, 'project_id': 7, 'resource': u'Robb Stark', 'activity': u'Development'},
{'duration': 0.22, 'project_id': 9, 'resource': u'Jon Snow', 'activity': u'Support'},
{'duration': 0.3, 'project_id': 9, 'resource': u'Jon Snow', 'activity': u'Support'},
{'duration': 2.15, 'project_id': 3, 'resource': u'Arya Stark', 'activity': u'Practise'},
{'duration': 3.35, 'project_id': 4, 'resource': u'Sansa Stark', 'activity': u'Development'},
{'duration': 2.17, 'project_id': 9, 'resource': u'Rickon Stark', 'activity': u'Development'},
{'duration': 1.03, 'project_id': 4, 'resource': u'Benjan Stark', 'activity': u'Design'},
{'duration': 1.77, 'project_id': 4, 'resource': u'Bran Stark', 'activity': u'Testing'},
{'duration': 1.17, 'project_id': 4, 'resource': u'Ned Stark', 'activity': u'Development'},
{'duration': 0.17, 'project_id': 9, 'resource': u'Jon Snow', 'activity': u'Support'},
{'duration': 1.77, 'project_id': 3, 'resource': u'catelyn stark', 'activity': u'Development'},
{'duration': 0.3, 'project_id': 9, 'resource': u'Jon Snow', 'activity': u'Support'},
{'duration': 0.45, 'project_id': 9, 'resource': u'Jon Snow', 'activity': u'Support'}]

All I need is to create a Table in python like

report_table = """<table>
.........
...........
..........
</table>"""

So the table format is attached,

enter image description here 1) It is possible to Bring in the desired format

2) Can we do summation operation inside dictionary data


Solution

  • IIUC Here is a solution using pandas:

    import pandas as pd
    dict_data = [{'duration': 0.7, 'project_id': 3, 'resource': u'Arya Stark', 'activity': u'Development'},
    {'duration': 0.9, 'project_id': 4, 'resource': u'Ned Stark', 'activity': u'Development'},
    {'duration': 2.88, 'project_id': 7, 'resource': u'Robb Stark', 'activity': u'Development'},
    {'duration': 0.22, 'project_id': 9, 'resource': u'Jon Snow', 'activity': u'Support'},
    {'duration': 0.3, 'project_id': 9, 'resource': u'Jon Snow', 'activity': u'Support'},
    {'duration': 2.15, 'project_id': 3, 'resource': u'Arya Stark', 'activity': u'Practise'},
    {'duration': 3.35, 'project_id': 4, 'resource': u'Sansa Stark', 'activity': u'Development'},
    {'duration': 2.17, 'project_id': 9, 'resource': u'Rickon Stark', 'activity': u'Development'},
    {'duration': 1.03, 'project_id': 4, 'resource': u'Benjan Stark', 'activity': u'Design'},
    {'duration': 1.77, 'project_id': 4, 'resource': u'Bran Stark', 'activity': u'Testing'},
    {'duration': 1.17, 'project_id': 4, 'resource': u'Ned Stark', 'activity': u'Development'},
    {'duration': 0.17, 'project_id': 9, 'resource': u'Jon Snow', 'activity': u'Support'},
    {'duration': 1.77, 'project_id': 3, 'resource': u'catelyn stark', 'activity': u'Development'},
    {'duration': 0.3, 'project_id': 9, 'resource': u'Jon Snow', 'activity': u'Support'},
    {'duration': 0.45, 'project_id': 9, 'resource': u'Jon Snow', 'activity': u'Support'}]
    
    df = pd.DataFrame(dict_data)
    dfg = df.groupby(['project_id','resource','activity']).sum()
    dfg.to_html('result.html')
    

    Will produce a html like this:

    enter image description here

    And another solution using pandas pivot_table with a different result:

    import numpy as np
    
    pvt = pd.pivot_table(df, values=['duration'],index=['project_id','resource'], columns=['activity'], aggfunc=np.sum,margins=True, fill_value=0)
    pvt.to_html('result2.html')
    

    enter image description here

    Rearrange the total project wise: Rearrange the columns and index to get a desired rusult.

    import numpy as np
    pvt = pd.pivot_table(df, values=['duration'],index=['resource','activity'], columns=['project_id'], aggfunc=np.sum,margins=True, fill_value=0)
    

    enter image description here