import json raw_data = """{ "App":"yohBLAHBsCN51LcZZhsn", "Table":"native-table-4ZzEgTrklqd0GHwcHspu", "RowID":"FdvGKiVSeNAY2dw9pQlB", "Centre":{ "App":"yohBLAHBsCN51LcZZhsn", "Table":"native-table-laBA6Lxho5caOvjXqbZQ", "RowID":"O6aTLdffQQG5pxqO1JJIuw", "CentreID":"PT6940", "CentreName":"Sunflower Childcare & Development Centre Pte Ltd", "CentreAddress":"670 Bedok North Street 3 #03-01 Kaki Bukit Community Centre S469627", "CentrePostalCode":"469627", "CentreWebsite":"https://www.sunflowerkid.com/", "CentreLoginURL":"https://happychinese.ichild.app/", "PICName":"Natasha", "PICEmail":"", "PICWA":"+6597351025", "SupportWA":"120363425448850927@g.us", "HQWA":"120363425448850927@g.us", "TotalStudent":"14", "ActiveStudent":"14", "HQ":"SUN", "Report":{ "Folder":"1RWmw97qdl9Nw0m6LtIfbtvEn7B87edPv", "MthActiveGSID":"1QaCDY57ln2SeUrCTHC9inzLEdmRqg7elLMBK1Cr75bo" } }, "Report":{ "Report":"Active", "ReportMonth":"Mar 2026", "ReportStudentNo":"14", "ReportDate":"30 Mar 2026", "ReportDocID":"", "ReportLink":"", "ReportLevelBreakdown":"N1: 2 N2: 4 K1: 5 K2: 3", "PGCount":"0", "PNCount":"0", "NCount":"0", "N1Count":"2", "N2Count":"4", "K1Count":"5", "K2Count":"3" }, "Students": [{ "StudentID":"PT6940M2", "StudentName":"Ngern Jeng Lib Janos 鄞礽立", "JoinedSince":"30 Mar 26", "GroupName":"N1", "GroupLevel":"N1", "JoinedGroupDate":"09 Mar 26", "Remark":"", "LatestMaterial":"", "IssueDate":"" }, { "StudentID":"PT6940M1", "StudentName":"Eusoff Ekhsan Bin Muhammad Asyraf", "JoinedSince":"30 Mar 26", "GroupName":"N1", "GroupLevel":"N1", "JoinedGroupDate":"09 Mar 26", "Remark":"", "LatestMaterial":"", "IssueDate":"" }, { "StudentID":"PT6940M6", "StudentName":"Yim Cheng Yu Jonah 严城宇", "JoinedSince":"30 Mar 26", "GroupName":"N2", "GroupLevel":"N2", "JoinedGroupDate":"09 Mar 26", "Remark":"", "LatestMaterial":"", "IssueDate":"" }, { "StudentID":"PT6940M5", "StudentName":"Yong Zhen Wei 翁振玮", "JoinedSince":"30 Mar 26", "GroupName":"N2", "GroupLevel":"N2", "JoinedGroupDate":"09 Mar 26", "Remark":"", "LatestMaterial":"", "IssueDate":"" }, { "StudentID":"PT6940M4", "StudentName":"Chia Zhi Jun Caleb 谢誌峻", "JoinedSince":"30 Mar 26", "GroupName":"N2", "GroupLevel":"N2", "JoinedGroupDate":"09 Mar 26", "Remark":"", "LatestMaterial":"", "IssueDate":"" }, { "StudentID":"PT6940M3", "StudentName":"Aurora Claribelle Kum 甘宸睿", "JoinedSince":"30 Mar 26", "GroupName":"N2", "GroupLevel":"N2", "JoinedGroupDate":"09 Mar 26", "Remark":"", "LatestMaterial":"", "IssueDate":"" }, { "StudentID":"PT6940M11", "StudentName":"Clarendon Chrisyler Chua 蔡宇翔", "JoinedSince":"30 Mar 26", "GroupName":"K1", "GroupLevel":"K1", "JoinedGroupDate":"09 Mar 26", "Remark":"", "LatestMaterial":"", "IssueDate":"" }, { "StudentID":"PT6940M10", "StudentName":"Heng Xiao En Emeryn 王筱恩", "JoinedSince":"30 Mar 26", "GroupName":"K1", "GroupLevel":"K1", "JoinedGroupDate":"09 Mar 26", "Remark":"", "LatestMaterial":"", "IssueDate":"" }, { "StudentID":"PT6940M9", "StudentName":"Javier Nye Zheng Rui 赖正瑞", "JoinedSince":"30 Mar 26", "GroupName":"K1", "GroupLevel":"K1", "JoinedGroupDate":"09 Mar 26", "Remark":"", "LatestMaterial":"", "IssueDate":"" }, { "StudentID":"PT6940M8", "StudentName":"Sim Jia Xi Laurel 沈佳希", "JoinedSince":"30 Mar 26", "GroupName":"K1", "GroupLevel":"K1", "JoinedGroupDate":"09 Mar 26", "Remark":"", "LatestMaterial":"", "IssueDate":"" }, { "StudentID":"PT6940M7", "StudentName":"Pang Yan Ming Enoch 冯衍铭", "JoinedSince":"30 Mar 26", "GroupName":"K1", "GroupLevel":"K1", "JoinedGroupDate":"09 Mar 26", "Remark":"", "LatestMaterial":"", "IssueDate":"" }, { "StudentID":"PT6940M14", "StudentName":"Tan Yi Xin 陈艺欣", "JoinedSince":"30 Mar 26", "GroupName":"K2", "GroupLevel":"K2", "JoinedGroupDate":"09 Mar 26", "Remark":"", "LatestMaterial":"", "IssueDate":"" }, { "StudentID":"PT6940M13", "StudentName":"Valerian Leck 陆铂升", "JoinedSince":"30 Mar 26", "GroupName":"K2", "GroupLevel":"K2", "JoinedGroupDate":"09 Mar 26", "Remark":"", "LatestMaterial":"", "IssueDate":"" }, { "StudentID":"PT6940M12", "StudentName":"Cheoh En Ting Caelya 石恩婷", "JoinedSince":"30 Mar 26", "GroupName":"K2", "GroupLevel":"K2", "JoinedGroupDate":"09 Mar 26", "Remark":"", "LatestMaterial":"", "IssueDate":"" }], "User": { "RowID":"dJBy3nyvQwWQFaRxBBudcA", "UserName":"Happy Admin", "UserWA":"+6591999633", "UserEmail":"ichildmarketing@gmail.com", "UserCentreID":"ICHILD", "UserCentreName":"ICHILD", "UserRole":"" }, "Route": "Create PDF Only" }""" report_month = "Mar 2026" # 1) Slice JSON safely start = raw_data.find("{") end = raw_data.rfind("}") + 1 cleaned = raw_data[start:end] # 2) FIX: escape newline characters for JSON cleaned = cleaned.replace(" ", " ") # 3) Parse JSON try: data = json.loads(cleaned, strict=False) except Exception as e: raise Exception("JSON parsing failed: " + str(e)) # 4) Get students students = data.get("Students", []) if not students: raise Exception("No students found in data") # 5) Build result result = {key: [] for key in students[0].keys()} for student in students: for key in result: result[key].append(str(student.get(key, ""))) # 6) Add MonthReport result["MonthReport"] = ", ".join([report_month] * len(students)) # 7) Convert lists to strings for key in result: if isinstance(result[key], list): result[key] = ", ".join(result[key]) print(json.dumps(result, indent=2))