Load multiple Jason data in one Data Frame - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: Load multiple Jason data in one Data Frame (/thread-37943.html) |
Load multiple Jason data in one Data Frame - vijays3 - Aug-12-2022 Hi All I need your guidance : I have three Jason files which in my local drive which need to be loaded in one data frame so that I can do the further exploratory data analysis. I have written a code but this only loads last file in the data frame . Kindly please find the below code for reference. I have also attached the Json files and notebook code screen shot. import json import pandas as pd import os path_to_json ='C://JsonFiles//' def file_path(path_to_json): json_files = [path_to_json + pos_json for pos_json in os.listdir(path_to_json) if pos_json.endswith('.json')] return json_files for jf in file_path(path_to_json): with open(jf,'r') as f: data = json.loads(f.read()) # Flatten data df_nested_list = pd.json_normalize( data, record_path =['attributes'], meta=['ORDERNUMBER', 'PRODUCTCODE']) df_nested_list RE: Load multiple Jason data in one Data Frame - Larz60+ - Aug-12-2022 Try: import pandas as pd import os from pathlib import Path jpath = Path('C://JsonFiles//') filelist = [filename for filename in jpath.iterdir() if filename.is_file() and filename.suffix == '.json'] df = pd.DataFrame([pd.read_json(file, typ="series") for file in filelist]) print(df) RE: Load multiple Jason data in one Data Frame - vijays3 - Aug-12-2022 (Aug-12-2022, 09:11 AM)Larz60+ Wrote: Try: Thanks Larz, but it does not work what I am expecting. If you look at my json files and review my code, I am trying to load those into one single dataframe with correct column names (I have attached one more screen with the data frame columns). json_normalize function and meta columns are used to extract the data hierarchical data from the Json. My code works fine but it does not load all the given file's data in DF , only loads last one. Kindly please help to twick my code so that all files loaded in one DF. RE: Load multiple Jason data in one Data Frame - snippsat - Aug-12-2022 Try. import json import pandas as pd import os path_to_json ='C://JsonFiles//' def file_path(path_to_json): json_files = [path_to_json + pos_json for pos_json in os.listdir(path_to_json) if pos_json.endswith('.json')] return json_files df = pd.DataFrame() for jf in file_path(path_to_json): with open(jf,'r') as f: data = json.loads(f.read()) # Flatten data df_nested_list = pd.json_normalize( data, record_path =['attributes'], meta=['ORDERNUMBER', 'PRODUCTCODE']) df = df.append(df_nested_list) RE: Load multiple Jason data in one Data Frame - vijays3 - Aug-12-2022 (Aug-12-2022, 01:29 PM)snippsat Wrote: Try. Hi snippsat It does not load the desired data. I have attached the screen shot what I am looking for and the result from new code RE: Load multiple Jason data in one Data Frame - snippsat - Aug-12-2022 Test of code using your files. import json import os import pandas as pd pd.set_option('display.max_column', None) pd.set_option('display.max_rows', None) pd.set_option('display.max_seq_items', None) pd.set_option('display.max_colwidth', 500) pd.set_option('expand_frame_repr', False) path_to_json = r'G:\div_code\JsonFiles\\' def file_path(path_to_json): json_files = [path_to_json + pos_json for pos_json in os.listdir(path_to_json) if pos_json.endswith('.json')] return json_files df = pd.DataFrame() for jf in file_path(path_to_json): with open(jf,'r') as f: data = json.loads(f.read()) # Flatten data df_nested_list = pd.json_normalize( data, record_path =['attributes'], meta=['ORDERNUMBER', 'PRODUCTCODE']) df = df.append(df_nested_list)Look at 20 first and 20 last entries,look ok to me. >>> df.head(20) QUANTITYORDERED PRICEEACH SALES ORDERDATE STATUS PRODUCTLINE MSRP ORDERNUMBER PRODUCTCODE 0 50 67.80 3390.00 1/6/2003 0:00 Shipped Vintage Cars 60 10100 S18_2248 1 22 86.51 1903.22 1/6/2003 0:00 Shipped Vintage Cars 92 10100 S18_4409 2 49 34.47 1689.03 1/6/2003 0:00 Shipped Vintage Cars 41 10100 S24_3969 3 45 31.20 1404.00 1/9/2003 0:00 Shipped Vintage Cars 33 10101 S24_1937 4 46 53.76 2472.96 1/9/2003 0:00 Shipped Vintage Cars 44 10101 S24_2022 5 41 50.14 2055.74 1/10/2003 0:00 Shipped Vintage Cars 53 10102 S18_1367 6 22 54.09 1189.98 1/29/2003 0:00 Shipped Trucks and Buses 60 10103 S18_2432 7 27 83.07 2242.89 1/29/2003 0:00 Shipped Vintage Cars 101 10103 S18_2949 8 35 57.46 2011.10 1/29/2003 0:00 Shipped Vintage Cars 62 10103 S18_2957 9 41 47.29 1938.89 1/29/2003 0:00 Shipped Vintage Cars 50 10103 S18_4668 10 45 75.63 3403.35 1/29/2003 0:00 Shipped Trucks and Buses 64 10103 S32_3522 11 35 55.49 1942.15 1/31/2003 0:00 Shipped Classic Cars 57 10104 S24_1444 12 44 39.60 1742.40 1/31/2003 0:00 Shipped Classic Cars 35 10104 S24_2840 13 35 47.62 1666.70 1/31/2003 0:00 Shipped Trucks and Buses 54 10104 S32_2509 14 49 65.87 3227.63 1/31/2003 0:00 Shipped Trains 62 10104 S32_3207 15 32 53.31 1705.92 1/31/2003 0:00 Shipped Trains 58 10104 S50_1514 16 41 82.50 3382.50 2/11/2003 0:00 Shipped Vintage Cars 87 10105 S18_4522 17 44 72.58 3193.52 2/11/2003 0:00 Shipped Vintage Cars 88 10105 S24_3151 18 50 79.67 3983.50 2/11/2003 0:00 Shipped Vintage Cars 83 10105 S24_3816 19 41 70.67 2897.47 2/11/2003 0:00 Shipped Ships 66 10105 S700_1138 >>> >>> >>> df.tail(20) QUANTITYORDERED PRICEEACH SALES ORDERDATE STATUS PRODUCTLINE MSRP ORDERNUMBER PRODUCTCODE 244 55 96.30 5296.50 5/29/2005 0:00 In Process Classic Cars 117 10420 S24_2887 245 35 96.74 3385.90 5/29/2005 0:00 In Process Classic Cars 85 10420 S24_3191 246 15 43.49 652.35 5/29/2005 0:00 In Process Vintage Cars 41 10420 S24_3969 247 40 45.70 1828.00 5/29/2005 0:00 In Process Vintage Cars 44 10421 S24_2022 248 51 95.55 4873.05 5/30/2005 0:00 In Process Vintage Cars 102 10422 S18_1342 249 25 51.75 1293.75 5/30/2005 0:00 In Process Vintage Cars 53 10422 S18_1367 250 10 88.14 881.40 5/30/2005 0:00 In Process Vintage Cars 101 10423 S18_2949 251 31 53.72 1665.32 5/30/2005 0:00 In Process Vintage Cars 62 10423 S18_2957 252 21 84.82 1781.22 5/30/2005 0:00 In Process Vintage Cars 104 10423 S18_3136 253 21 89.29 1875.09 5/30/2005 0:00 In Process Vintage Cars 99 10423 S18_3320 254 28 78.89 2208.92 5/30/2005 0:00 In Process Vintage Cars 97 10423 S24_4258 255 26 59.87 1556.62 5/31/2005 0:00 In Process Vintage Cars 50 10424 S18_4668 256 44 61.41 2702.04 5/31/2005 0:00 In Process Trucks and Buses 64 10424 S32_3522 257 46 80.92 3722.32 5/31/2005 0:00 In Process Classic Cars 101 10424 S700_2824 258 38 99.41 3777.58 5/31/2005 0:00 In Process Trucks and Buses 122 10425 S18_2319 259 19 49.22 935.18 5/31/2005 0:00 In Process Trucks and Buses 60 10425 S18_2432 260 55 46.82 2575.10 5/31/2005 0:00 In Process Classic Cars 57 10425 S24_1444 261 31 33.24 1030.44 5/31/2005 0:00 In Process Classic Cars 35 10425 S24_2840 262 41 86.68 3553.88 5/31/2005 0:00 In Process Trucks and Buses 96 10425 S32_1268 263 11 43.83 482.13 5/31/2005 0:00 In Process Trucks and Buses 54 10425 S32_2509 RE: Load multiple Jason data in one Data Frame - vijays3 - Aug-12-2022 (Aug-12-2022, 04:15 PM)snippsat Wrote: Test of code using your files. Amazing.. Thank you very much snippsat. It has worked as expected. You have saved my day. |