Openpyxl and json round 2

Posted on Thursday, February 6, 2020









I wrote a recent article going over how to convert json to xlsx and back again using openpyxl in python. See http://www.whiteboardcoder.com/2020/02/openpyxl-and-json.html [1]

Now I want to up the ante and do the same thing but with a more complex json that will convert into multiple sheets in the excel file.




Simple test


Let me create a simple json file that represents a budget where it also has years that will be converted into sheets in Excel


{
   "2018":{
        "January": {
                "food": 240.5,
                "heating": 89.2,
                "rent": 1709.10
        },
        "February": {
                "food": 202.5,
                "heating": 112.2,
                "rent": 1709.10
        },
        "March": {
                "food": 320.5,
                "heating": 45.2,
                "rent": 1709.10
        }
   },
   "2019":{
        "January": {
                "food": 120.5,
                "heating": 88.2,
                "rent": 1809.10
        },
        "February": {
                "food": 102.5,
                "heating": 122.2,
                "rent": 1809.10
        },
        "March": {
                "food": 120.5,
                "heating": 35.2,
                "rent": 1809.10
        }
   },
   "2020":{
        "January": {
                "food": 220.5,
                "heating": 18.2,
                "rent": 1909.10
        },
        "February": {
                "food": 223.5,
                "heating": 12.2,
                "rent": 1909.10
        },
        "March": {
                "food": 120.5,
                "heating": 25.2,
                "rent": 1909.10
        }
   }
}


Save this in a file called original.jsonYou can use a tool like https://jsonlint.com/

To confirm it is in the correct format.




Or you can use jq from the command line




  > jq . original.json







Simple python script to create xls file from json




  > vi createxls_from_json_multiple_sheets.py


And place the following in it


#!/usr/bin/env python3

import openpyxl
import json
from openpyxl import Workbook


def populate_sheet(json_data, sheet):
   sheet.cell(1,1, "Month")
   sheet.cell(1,2, "food")
   sheet.cell(1,3, "heating")
   sheet.cell(1,4, "rent")

   row = 1
   for month in json_data.keys():
     row+=1
     sheet.cell(row,1,month)
     sheet.cell(row,2,float(json_data[month]["food"]))
     sheet.cell(row,3,float(json_data[month]["heating"]))
     sheet.cell(row,4,float(json_data[month]["rent"]))


#############################################
#  MAIN
#############################################
if __name__ == '__main__':

   json_data = {}

   with open("original.json") as json_file:
     json_data = json.load(json_file)

   wb = Workbook()
   #When you make a new workbook you get a new blank active sheet
   #We need to delete it since we do not want it
   wb.remove(wb.active)

   for year in json_data.keys():
     sheet = wb.create_sheet(title=year)
     populate_sheet(json_data[year], sheet)


   #Save it to excel
   wb.save("test_json.xlsx")





Now chmod it and run it



  > chmod u+x createxls_from_json_multiple_sheets.py
  >  ./createxls_from_json_multiple_sheets.py


Now open it up









Boom



Now let me create another program to convert the xls file into a json file.


  > vi createjson_from_xslx_multiple_sheet.py


And place the following in it


#!/usr/bin/env python3

import openpyxl
import json
from openpyxl import load_workbook


def get_sheet_dict(ws):
  year_dict = {}
  column_headers = {}

  #Returns a Tuple that are cell type
  first_row = ws[1]

  #Skip the first column
  for cell in first_row[1:]:
    column_headers[cell.column]=cell.internal_value

  for row in ws.iter_rows(min_row=2):
    month = row[0].internal_value
    temp_cell_dict = {}
    #Skip the month cell
    for cell in row[1:]:
      #store the data in a dict with keys as column names
      col_name = column_headers[cell.column]
      temp_cell_dict[col_name]=cell.internal_value

    #Set the month key to be contents of row
    year_dict[month] = temp_cell_dict

  return year_dict

#############################################
#  MAIN
#############################################
if __name__ == '__main__':

   wb = load_workbook(filename="test_json.xlsx")

   budget_dict = {}

   for ws in wb:
     year_dict = get_sheet_dict(ws)
     budget_dict[ws.title] = year_dict




   #Convert dict to JSON
   data_json = json.dumps(budget_dict)

   #Write json file
   with open("new.json", "w") as f:
     f.write(data_json)





  > chmod u+x createjson_from_xslx_multiple_sheet.py
  >  ./createjson_from_xslx_multiple_sheet.py



Now use the jq tool to see it


  > jq . new.json





Use diff to see it is identical to the original json


  > diff original.json new.json




The files are not exactly identical…
But is the json itself a match we can use jq to help us figure this out.



  > diff <(jq -S . original.json) <(jq -S . new.json)



And if it returns nothing then it is a match.

Let me change one thing by hand in the new.json and see if it catches it.



  > diff <(jq -S . original.json) <(jq -S . new.json)




Yep J


References


[1]        openpyxl and json


39 comments:

  1. Very interesting article with unique content and helpful information thank you.
    Data Science Course in Hyderabad 360DigiTMG

    ReplyDelete
  2. Happy to chat on your blog, I feel like I can't wait to read more reliable posts and think we all want to thank many blog posts to share with us.

    Artificial Intelligence Course in Bangalore

    ReplyDelete
  3. Make video how you do it and post on youtube. If you want to post your video on tiktok too, you will need to buy tiktok likes from this site https://soclikes.com/buy-tiktok-likes

    ReplyDelete
  4. Thank you for sharing such a useful post with us, it will useful for everybody, so keep it up that is decent work.data science training in Hyderabad

    ReplyDelete
  5. me up with some sort of fake birth certificate because as we know robots are not born. An artificial intelligence system could indeed have a better chance of winning elections by merely surfing the news, finding out what was on data science course in india

    ReplyDelete

  6. Impressive. Your story always brings hope and new energy. Keep up the good work.
    business analytics course

    ReplyDelete
  7. You actually make it seem like it's really easy with your acting, but I think it's something I think I would never understand. I find that too complicated and extremely broad. I look forward to your next message. I'll try to figure it out!
    You actually make it seem like it's really easy with your acting, but I think it's something I think I would never understand. I find that too complicated and extremely broad. I look forward to your next message. I'll try to figure it out!

    ReplyDelete
  8. wow ... what a great blog, this writer who wrote this article is really a great blogger, this article inspires me so much to be a better person.
    Digital Marketing Course in Bangalore

    ReplyDelete
  9. This is a very nice one and gives in-depth information. I am really happy with the quality and presentation of the article. I’d really like to appreciate the efforts you get with writing this post. Thanks for sharing.
    Python classes in Ahmednagar

    ReplyDelete
  10. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    business analytics course


    ReplyDelete
  11. I am glad to discover this page. I have to thank you for the time I spent on this especially great reading !! I really liked each part and also bookmarked you for new information on your site.
    artificial intellingence training in chennai

    ReplyDelete
  12. After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article.
    digital marketing courses in hyderabad with placement

    ReplyDelete
  13. I was actually browsing the internet for certain information, accidentally came across your blog found it to be very impressive. I am elated to go with the information you have provided on this blog, eventually, it helps the readers whoever goes through this blog. Hoping you continue the spirit to inspire the readers and amaze them with your fabulous content.
    th
    Data Science Course in Faridabad

    ReplyDelete
  14. It took a while to understand all the comments, but I really enjoyed the article. It turned out to be really helpful for me and I'm positive for all the reviewers here! It's always nice to be able to not only be informed, but also entertained! I'm sure you enjoyed writing this article.
    Best Data Science Courses in Bangalore

    ReplyDelete
  15. It fully emerged to crown Singapore's southern shores and has undoubtedly put it on the world residential monument map. Still, I scored more points than I have in one season for GS. I think it would be hard to find someone with the same consistency that I have had over the years, so I'm happy.
    Data Analytics Course in Bangalore

    ReplyDelete
  16. A good blog always comes-up with new and exciting information and while reading I have feel that this blog is really have all those quality that qualify a blog to be a one.

    ReplyDelete
  17. am sure it will help many people. Keep up the good work. It's very compelling and I enjoyed browsing the entire blog.
    servicenow training in Bangalore

    ReplyDelete
  18. I am a new user of this site, so here I saw several articles and posts published on this site, I am more interested in some of them, will provide more information on these topics in future articles.


    Data Scientist Course in Bangalore

    ReplyDelete
  19. After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article.
    servicenow training in Bangalore

    ReplyDelete
  20. I am a new user of this site, so here I saw several articles and posts published on this site, I am more interested in some of them, will provide more information on these topics in future articles.


    Best Cyber Security Training Institute in Bangalore

    ReplyDelete
  21. Superbly written article, if only all bloggers offered the same content as you, the internet would be a far better place.
    ServiceNow Training in Chennai

    ReplyDelete
  22. Truly, this article is really one of the very best in the history of articles. I am a antique ’Article’ collector and I sometimes read some new articles if I find them interesting. And I found this one pretty fascinating and it should go into my collection. Very good work!
    ai course in hyderabad

    ReplyDelete
  23. Very informative Blog! There is so much information here that can help thank you for sharing.
    servicenow training and placement in hyderabad

    ReplyDelete
  24. I have read your article, it is very informative and helpful for me.I admire the valuable information you offer in your articles. Thanks for posting it..
    cloud computing online training in hyderabad


    ReplyDelete
  25. Nice blog. Found this while searching through ai course in delhi

    ReplyDelete
  26. Hey there! Do you know if they make any plugins to help with Search Engine Optimization? I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very good gains. If you know of any please share. Thank you!| cyber security course in delhi

    ReplyDelete
  27. After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article.
    Mulesoft training in hyderabad

    ReplyDelete
  28. Very informative message! There is so much information here that can help any business get started with a successful social media campaign!

    Data Science Training Institutes in Bangalore

    ReplyDelete
  29. I truly like you're composing style, incredible data, thankyou for posting.
    cyber security training malaysia

    ReplyDelete
  30. Through this post, I realize that your great information in playing with all the pieces was exceptionally useful. I advise this is the primary spot where I discover issues I've been scanning for. You have a smart yet alluring method of composing.

    ReplyDelete
  31. Pleasant data, important and incredible structure, as offer great stuff with smart thoughts and ideas, loads of extraordinary data and motivation, the two of which I need, because of offer such an accommodating data here.

    ReplyDelete
  32. 360DigiTMG, the top-rated organisation among the most prestigious industries around the world, is an educational destination for those looking to pursue their dreams around the globe. The company is changing careers of many people through constant improvement, 360DigiTMG provides an outstanding learning experience and distinguishes itself from the pack. 360DigiTMG is a prominent global presence by offering world-class training. Its main office is in India and subsidiaries across Malaysia, USA, East Asia, Australia, Uk, Netherlands, and the Middle East.

    ReplyDelete
  33. Thank you so much for this amazing blog. Keep sharing this type of content with us. If anyone wants to learn DATA SCIENCE in Delhi, I will recommend High Technologies Solutions training institute.
    For any further information please call +919311002620 or you can visit website
    https://htsindia.com/Courses/python/python-with-data-science-training-course

    ReplyDelete
  34. Superb Information, I really appreciated with it, This is fine to read and valuable pro potential, I really bookmark it, pro broaden read. Appreciation pro sharing. I like it.Data Analytics Course in Dombivli

    ReplyDelete
  35. It took me a while to read all the reviews, but I really enjoyed the article. This has proven to be very helpful to me and I'm sure all the reviewers here! It's always nice to be able to not only be informed, but also have fun!

    Data Science Course in Ernakulam

    ReplyDelete
  36. http://www.whiteboardcoder.com/2020/02/openpyxl-and-json-round-2.html

    ReplyDelete
  37. We appreciate you sharing this helpful information with us. do continue doing this good work as it will be helpful to everyone. Do Visit at python training course in bhopal

    ReplyDelete
  38. I found the tips and best practices shared in this article to be helpful for improving data science workflows. Best Data Science Training Institute In Chennai

    ReplyDelete