I have the Excel data with the format shown in the image preview. How can I convert it into a JSON using Python?
Expected Output:
file_name = [
{
A: Measurement(
time=10,
X1=1,
X2=4
),
B: Measurement(
time=10,
X1=1.2,
X2=4.1
),
C: Measurement(
time=10,
X1=1,
X2=4
)
},
{
A: Measurement(
time=11,
X1=1.1,
X2=4
),
B: Measurement(
time=11,
X1=1,
X2=4.2
),
C: Measurement(
time=11,
X1=1,
X2=4
)
},
{
A: Measurement(
time=12,
X1=1.3,
X2=4.1
),
B: Measurement(
time=12,
X1=1,
X2=4.1
),
C: Measurement(
time=12,
X1=1.2,
X2=3.9
)
}
]
The desired output can be achieved using nested dictionary objects.
import openpyxl
import json
workbook = openpyxl.load_workbook(r'd:/temp/data.xlsx')
sheet = workbook.active
data_dict = {}
# skipping header
for row in sheet.iter_rows(min_row=2, values_only=True):device_id, time, X1, X2 = rowhour = time.hour # Extract hour from the timeif hour not in data_dict:data_dict[hour] = {}data_dict[hour][device_id] = (hour, X1, X2)
# dictionary to JSON
json_data = []
for hour, measurements in data_dict.items():json_data.append({device_id: f"Measurement( time={hour}, X1={X1}, X2={X2} )" for device_id, (hour, X1, X2) in measurements.items()})
file_name = json.dumps(json_data, indent=2)
print(file_name)
Output
[ { "A": "Measurement( time=10, X1=1, X2=4 )", "B": "Measurement( time=10, X1=1.2, X2=4.1 )", "C": "Measurement( time=10, X1=1, X2=4 )" }, { "A": "Measurement( time=11, X1=1, X2=4 )", "B": "Measurement( time=11, X1=1.2, X2=4.1 )", "C": "Measurement( time=11, X1=1, X2=4 )" }
]
Update
Revised the code for new requirement in comment.
import json
import pandas as pd
from datetime import datetime
data = pd.read_excel(r'd:\temp\data.xlsx')
data['time_str'] = data['time'].apply(lambda t: t.strftime("%Y, %#m, %#d, %H, %M"))
grouped = data.groupby('time_str').apply(lambda g: g.to_dict(orient='records')).tolist()
output = []
for measurements in grouped:mea_dict = {}for meamnt in measurements:device_id = meamnt['Device_id']x1 = meamnt['X1']x2 = meamnt['X2']time_str = meamnt['time_str']mea_str = f"Measurement( calculated_date=datetime({time_str}), X1={x1}, X2={x2} )"mea_dict[device_id] = mea_stroutput.append(mea_dict)
fname = json.dumps(output, indent=2)
print(fname)
Output
[ { "A": "Measurement( calculated_date=datetime(2023, 1, 1, 10, 00), X1=1.0, X2=4.0 )", "B": "Measurement( calculated_date=datetime(2023, 1, 1, 10, 00), X1=1.2, X2=4.1 )", "C": "Measurement( calculated_date=datetime(2023, 1, 1, 10, 00), X1=1.0, X2=4.0 )" }, { "A": "Measurement( calculated_date=datetime(2023, 2, 1, 10, 10), X1=1.0, X2=4.0 )", "B": "Measurement( calculated_date=datetime(2023, 2, 1, 10, 10), X1=1.2, X2=4.1 )", "C": "Measurement( calculated_date=datetime(2023, 2, 1, 10, 10), X1=1.0, X2=4.0 )" }
]