Change dates to quarters in JSON file Python-CodePudding

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"
    }
}

CodePudding user response：

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)

CodePudding user response：

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.