I’m trying to convert the dates inside a JSON file to their respective quarter and year. My JSON file is formatted below:
JavaScript
x
15
15
1
{
2
"lastDate": {
3
"0": "11/22/2022",
4
"1": "10/28/2022",
5
"2": "10/17/2022",
6
"7": "07/03/2022",
7
"8": "07/03/2022",
8
"9": "06/03/2022",
9
"18": "05/17/2022",
10
"19": "05/08/2022",
11
"22": "02/03/2022",
12
"24": "02/04/2022"
13
}
14
}
15
The current code I’m using is an attempt of using the pandas.Series.dt.quarter
as seen below:
JavaScript
1
12
12
1
import json
2
import pandas as pd
3
4
data = json.load(open("date_to_quarters.json"))
5
6
df = data['lastDate']
7
pd.to_datetime(df['lastDate'])
8
df['Quarter'] = df['Date'].dt.quarter
9
10
open("date_to_quarters.json", "w").write(
11
json.dumps(data, indent=4))
12
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:
JavaScript
1
15
15
1
{
2
"lastDate": {
3
"0": "Q42022",
4
"1": "Q42022",
5
"2": "Q42022",
6
"7": "Q32022",
7
"8": "Q32022",
8
"9": "Q22022",
9
"18": "Q22022",
10
"19": "Q22022",
11
"22": "Q12022",
12
"24": "Q12022"
13
}
14
}
15
Advertisement
Answer
You can use this bit of code instead:
JavaScript
1
18
18
1
import json
2
import pandas as pd
3
4
data = json.load(open("date_to_quarters.json"))
5
6
# convert json to df
7
df = pd.DataFrame.from_dict(data, orient="columns")
8
9
# convert last date to quarter
10
df['lastDate'] = pd.to_datetime(df['lastDate'])
11
df['lastDate'] = df['lastDate'].dt.to_period('Q')
12
13
# change type of lastDate to string
14
df['lastDate'] = df['lastDate'].astype(str)
15
16
# write to json file
17
df.to_json("date_to_quarters1.json", orient="columns", indent=4)
18
json
object is different than pd.DataFrame
. You have to convert json
to pd.DataFrame
first using from_dict()
function.