Question 111084 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Change dates to quarters in JSON file Python

I’m trying to convert the dates inside a JSON file to their respective quarter and year. My JSON file is formatted below:

{
    "lastDate": {
        "0": "11/22/2022",
        "1": "10/28/2022",
        "2": "10/17/2022",
        "7": "07/03/2022",
        "8": "07/03/2022",
        "9": "06/03/2022",
        "18": "05/17/2022",
        "19": "05/08/2022",
        "22": "02/03/2022",
        "24": "02/04/2022"
    }
}

The current code I’m using is an attempt of using the pandas.Series.dt.quarter as seen below:

import json
import pandas as pd

data = json.load(open("date_to_quarters.json"))

df = data['lastDate'] 
pd.to_datetime(df['lastDate'])
df['Quarter'] = df['Date'].dt.quarter

open("date_to_quarters.json", "w").write(
    json.dumps(data, indent=4))

The issue I face is that my code isn’t comprehending the object name "lastDate". My ideal output should have the dates ultimately replaced into their quarter, check below:

{
    "lastDate": {
        "0": "Q42022",
        "1": "Q42022",
        "2": "Q42022",
        "7": "Q32022",
        "8": "Q32022",
        "9": "Q22022",
        "18": "Q22022",
        "19": "Q22022",
        "22": "Q12022",
        "24": "Q12022"
    }
}
Tags:

2

Answers


  1. 0

    You can use this bit of code instead:

    import json
import pandas as pd

data = json.load(open("date_to_quarters.json"))

# convert json to df
df = pd.DataFrame.from_dict(data, orient="columns")

# convert last date to quarter
df['lastDate'] = pd.to_datetime(df['lastDate'])
df['lastDate'] = df['lastDate'].dt.to_period('Q')

# change type of lastDate to string
df['lastDate'] = df['lastDate'].astype(str)

# write to json file
df.to_json("date_to_quarters1.json", orient="columns", indent=4)

    json object is different than pd.DataFrame. You have to convert json to pd.DataFrame first using from_dict() function.

    Login or Signup to reply.
  2. 0

    Try:

    import json
import pandas as pd

with open("data.json", "r") as f_in:
    data = json.load(f_in)

x = pd.to_datetime(list(data["lastDate"].values()))


out = {
    "lastDate": dict(
        zip(data["lastDate"], (f"Q{q}{y}" for q, y in zip(x.quarter, x.year)))
    )
}
print(out)

    Prints:

    {
    "lastDate": {
        "0": "Q42022",
        "1": "Q42022",
        "2": "Q42022",
        "7": "Q32022",
        "8": "Q32022",
        "9": "Q22022",
        "18": "Q22022",
        "19": "Q22022",
        "22": "Q12022",
        "24": "Q12022",
    }
}

    To save out as Json:

    with open("out.json", "w") as f_out:
    json.dump(out, f_out, indent=4)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search