Python Forum
Struggling with Juggling JSON Data
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Struggling with Juggling JSON Data
#1
I have worked through 300+ pages of Shovic & Simpson's Python for Dummies All in One but hit a snag in Book 3, Chapter 2.

The first issue is that the book first discusses converting CSV files to JSON using www.convertcsv.com/csv-to-json. This works well, but the results I get don't match the book in that the dates come through as strings like
Output:
"DateJoined": "2/3/2017"
instead of the serialized dates the book shows
Output:
"Date Joined": 37683
The next issue may be related to the first, in that the book talks about converting JSON dates (which it implies are always the serialized kind) to Python dates (with human-readable formatting) and that to do that you add this:
import xlrd
but my Python (from Anaconda3 controlled by VBS) rejects the xlrd module line:
Output:
ModuleNotFoundError: No module named 'xlrd'
which doesn't seem to matter as the JSON date from the conversion wasn't serialized to begin with.

The next issue in this chapter relates to getting JSON data from Google's Firebase Realtime Database that has these automatically added keys, "Those weird things like -LAOqOxg6kmP4jhnjQXS are all keys that the Firebase generates automatically for each item of data to guarantee uniqueness."

But, when I take JSON data that I've converted from a CVS file and load it into Firebase, I don't get automatically generated keys for each dictionary (or JSON object).

These problems recur when trying to convert "an Excel date to a JSON date" in that the dates that I have are pre-converted, as well as when "Looping through a keyed JSON file"...because the JSON file doesn't have any of the keys mentioned above.

I'd like to finish the chapter, but it seems this has become a road-block due to my lack of understanding.

Thanks for any illumination.
Reply
#2
Don't have access to that book, so no idea what it's saying. Hopefully it's at least on python3 and not something old that refers to python2.

There is no date format in JSON, just numbers, strings, and containers. So if you have a JSON with an integer date, that's because either the source or the converter put it that way. I would find that format odd.

You're talking about CSV to JSON, but mentioning xlrd makes me think something is trying to do EXCEL to JSON (or similar). xlrd is a library that reads excel files. It has no purpose on a CSV reader.

Now talking about excel, it does have an internal format that records dates (and times) as integral numbers of days since some epoch. As an example if I type in your date of 37683 into excel and change the cell to date format, it says that is March 3, 2003. *Maybe* there was an old version of excel that kept that number when saved as CSV, (but I don't think so, and current ones definitely don't do that).
SamWatt likes this post
Reply
#3
Thanks, that's what I was more or less thinking. I'd like to be able to have a forum with the authors to ask them questions. I'll just move on to the next chapter and do more to find out about JSON and CSV dates and Firebase Realtime Databases elsewhere. At least I've been introduced to the outskirts of the subjects.

I do appreciate your taking the time to answer.
Reply
#4
(May-07-2022, 02:25 AM)SamWatt Wrote: ModuleNotFoundError: No module named 'xlrd'
You most install xlrd.
pip install xlrd
As mention bye bowlofred when use xldr so then working with data from Excel.
Think usage can be xlrd.xldate_as_tuple to convert 37683(Excel date format to Python datetime).
Something like this.
>>> import xlrd
>>> import datetime as dt
>>> 
>>> d = 37683
>>> xlrd.xldate_as_tuple(d, 0)
(2003, 3, 3, 0, 0, 0)
>>> y, m, d, *rest = xlrd.xldate_as_tuple(d, 0)
>>> date = dt.date(y, m, d)
>>> date
datetime.date(2003, 3, 3)
# Now that is datetime object can format to wanted result 
>>> print(f"{date:%m-%d-%Y}")
03-03-2003
>>> print(f"{date:%d %B, %Y}")
03 March, 2003
Reply
#5
When I try the pip command, I get:
Output:
pip install xlrd Requirement already satisfied: xlrd in c:\users\<user>\anaconda3a\lib\site-packages (2.0.1)
Then. when import xlrd is used in the code, the output is:
Output:
Traceback (most recent call last): File "c:\Users\<user>\Documents\Python Scripts\Workspaces\AIO Python\VBS\Book3\Ch2c.py", line 2, in <module> import xlrd ModuleNotFoundError: No module named 'xlrd'
Is there something amiss with my installation?
Reply
#6
When use Anaconda you most remember to activate (base) environment.
Before older version of Anaconda could get away not working from a environment,not any more.
Anaconda prompt dos this automatically.
Or if it do it manually it will be like this,i use cmder here but commands is same in cmd or Anaconda promp
G:\miniconda3
λ cd Scripts\
G:\miniconda3\Scripts
λ activate.bat
(base) G:\miniconda3\Scripts
λ cd ..

(base) G:\miniconda3
λ pip install xlrd
Collecting xlrd
  Using cached xlrd-2.0.1-py2.py3-none-any.whl (96 kB)
Installing collected packages: xlrd
Successfully installed xlrd-2.0.1

(base) G:\miniconda3
λ python
Python 3.9.1 (default, Dec 11 2020, 09:29:25) [MSC v.1916 64 bit (AMD64)] :: Anaconda, Inc. on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import xlrd
>>>
>>> xlrd.__version__
'2.0.1'
>>> exit()

(base) G:\miniconda3
λ spyder 
So if you use eg Spyder start it from (base) environment as you should then it will find xlrd.
Look longer down in link i gave you Managing environments,so this is important when using Anacoda.
I never use (base) always make my own environment to get updated Python version and packages that i need..
G:\div_code
λ G:\miniconda3\Scripts\activate.bat tom_env

(tom_env) G:\div_code
λ python --version
Python 3.10.4 # See that here i have newest version of Python and (base) has Python 3.9.1

(tom_env) G:\div_code
Reply
#7
I'm not quite sure how to go forward with the (base) environment change. I don't know what minconda3 is.

I think I'm just going to do a full uninstall of Anaconda3 and reinstall it, and see if there were any choices I made initially (out of ignorance) that may have been mistakes.

I'll post if it works.

Thanks for your input.
Reply
#8
(May-09-2022, 02:21 AM)SamWatt Wrote: I'm not quite sure how to go forward with the (base) environment change.
You most learn how to use command line Starting conda so when use Anaconda Prompt it activate (base) automatically.
Anaconda Promp is just a version of cmd tutorial that activate base environment automatically,I did show how to this manually.

(May-09-2022, 02:21 AM)SamWatt Wrote: I don't know what minconda3 is.
minconda3 is version of Anaconda without all the packages.
Quote:I think I'm just going to do a full uninstall of Anaconda3 and reinstall it
Also a point is that you can make your environment(this will be like new installation),no new installation of Anaconda is needed
Look at this post
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  encrypt data in json file help jacksfrustration 1 21 1 hour ago
Last Post: deanhystad
  Read nested data from JSON - Getting an error marlonbown 5 1,309 Nov-23-2022, 03:51 PM
Last Post: snippsat
  Reading Data from JSON tpolim008 2 1,031 Sep-27-2022, 06:34 PM
Last Post: Larz60+
  Convert nested sample json api data into csv in python shantanu97 3 2,725 May-21-2022, 01:30 PM
Last Post: deanhystad
  json api data parsing elvis 0 902 Apr-21-2022, 11:59 PM
Last Post: elvis
Question I am struggling with basic json library + sql alchemy w/ mariadb-connector json ->sql BrandonKastning 0 1,483 Mar-04-2022, 09:26 PM
Last Post: BrandonKastning
  Capture json data JohnnyCoffee 0 1,174 Nov-18-2021, 03:19 PM
Last Post: JohnnyCoffee
  Serializing Python data Correctly (JSON) JgKSuperstar 4 2,039 Nov-04-2021, 07:31 PM
Last Post: JgKSuperstar
  Syntax errors: Struggling to setup enviroment and load packages AH56 5 2,724 Jun-30-2021, 01:01 PM
Last Post: AH56
  How to save json data in a dataframe shantanu97 1 2,121 Apr-15-2021, 02:44 PM
Last Post: klllmmm

Forum Jump:

User Panel Messages

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