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(
calculated_date=datetime(2022, 10, 1, 4, 18),
X1=1,
X2=4
),
B: Measurement(
calculated_date=datetime(2022, 10, 1, 4, 18),
X1=1.2,
X2=4.1
),
C: Measurement(
calculated_date=datetime(2022, 10, 1, 4, 18),
X1=1,
X2=4
)
},
{
A: Measurement(
calculated_date=datetime(2022, 10, 2, 3, 18),
X1=1.1,
X2=4
),
B: Measurement(
calculated_date=datetime(2022, 10, 2, 3, 18),
X1=1,
X2=4.2
),
C: Measurement(
calculated_date=datetime(2022, 10, 2, 3, 18),
X1=1,
X2=4
)
},
{
A: Measurement(
calculated_date=datetime(2022, 10, 3, 9, 48),
X1=1.3,
X2=4.1
),
B: Measurement(
calculated_date=datetime(2022, 10, 3, 9, 48),
X1=1,
X2=4.1
),
C: Measurement(
calculated_date=datetime(2022, 10, 3, 9, 48),
X1=1.2,
X2=3.9
)
}
]
This should get the desired output:
import openpyxl
from openpyxl.utils import get_column_letter
from datetime import datetime
import jsondef load_excel_data(file_path):workbook = openpyxl.load_workbook(file_path)sheet = workbook.activereturn sheetdef convert_excel_datetime(excel_datetime_str):return datetime.strptime(str(excel_datetime_str), '%Y-%m-%d %H:%M:%S')def create_measurement_dict(time, x1, x2):return {'calculated_date': time,'X1': x1,'X2': x2}def main():excel_file = 'test.xlsx'sheet = load_excel_data(excel_file)json_data = []current_date = Noneentry = {}for row in sheet.iter_rows(min_row=2, values_only=True):device_id, row_time, x1, x2 = rowparsed_time = convert_excel_datetime(row_time)if parsed_time.date() != current_date:if current_date:json_data.append(entry)current_date = parsed_time.date()entry = {}measurement = create_measurement_dict(parsed_time, x1, x2)entry[device_id] = 'Measurement(' + str(measurement) + ')'json_data.append(entry)print(json.dumps(json_data, indent=4))if __name__ == "__main__":main()
Output:
[{"A": "Measurement({'calculated_date': datetime.datetime(2022, 10, 1, 4, 18), 'X1': 1, 'X2': 4})", "B": "Measurement({'calculated_date': datetime.datetime(2022, 10, 1, 4, 18), 'X1': 1.2, 'X2': 4.1})","C": "Measurement({'calculated_date': datetime.datetime(2022, 10, 1, 4, 18), 'X1': 1, 'X2': 4})" },{"A": "Measurement({'calculated_date': datetime.datetime(2022, 10, 2, 3, 18), 'X1': 1.1, 'X2': 4})","B": "Measurement({'calculated_date': datetime.datetime(2022, 10, 2, 3, 18), 'X1': 1, 'X2': 4.2})","C": "Measurement({'calculated_date': datetime.datetime(2022, 10, 2, 3, 18), 'X1': 1, 'X2': 4})"},{"A": "Measurement({'calculated_date': datetime.datetime(2022, 10, 3, 9, 48), 'X1': 1.3, 'X2': 4.1})","B": "Measurement({'calculated_date': datetime.datetime(2022, 10, 3, 9, 48), 'X1': 1, 'X2': 4.1})","C": "Measurement({'calculated_date': datetime.datetime(2022, 10, 3, 9, 48), 'X1': 1.2, 'X2': 3.9})"}
]