nested dictionaries to CSV

[mart79]

Hi all,

I am new to Python and one of my challenges is to write a nested dictionary to csv file.
Can someone help me with this? The idea is that the csv file includes the headers also.
The headers of each nested dictionary are the identical, so they only need to be presented once.
Can somebody help me with this?

Thanks,
Martin

[ichabod801]

We can help you with this, but note that we are not going to write the code for you.

My guess is that you should flatten the nested dictionary first, and then write out the flattened dictionary. How you do the flattening is going to depend on the structure of the nested dictionary. To retain the information currently in the nesting structure, I would generally go with a unique id for each entry and a parent for each entry showing how it is nested.

So we would need to see a large enough subset of the nested dictionary to understand it's structure, and the code you have written so far.

[mart79]

Hi,

I used the following code:

def write_data(file, data_file):

    fields = ['LC_ID', 'DIR', 'FILE', 'ID', 'VSL', 'RAD', 'PROP', 'WD', 'ROT', 'HS', 'TZ', 'WEA', 'NTT', 'NBT',
              'MX_OBS', 'MX_SBS', 'MN_VTS', 'MX_VTS', 'AST', 'LB_OBA', 'LB_OBB', 'LB_SBA', 'LB_SBB', 'XTDP', 'FDR',
              'FST', 'FPL', 'FATP', 'FATC', 'NO_OBS', 'NO_SBS', 'NO_VTS', 'VTL', 'HTL', 'CHK']

    with open(file,'w') as f:
        w = csv.DictWriter(f, fields)
        w.writeheader()
        for k in data_file:
            w.writerow({field: data_file[k].get(field) or k for field in fields})
    f.close()

The dictionary looks as follows:

{'LCST_Static.tst_-99_69.00_3048.90': {'DIR': 'C:/Users/---/Desktop/Test ---', 'FILE': 'Static.tst', 'ID': 'ST', 'VSL': 'Static.tst', 'RAD': -99, 'PROP': -99, 'WD': -99, 'ROT': -99, 'HS': -99, 'TZ': -99, 'WEA': -99, 'NTT': '3048.90', 'NBT': '2533.50', 'MX_OBS': -99, 'MX_SBS': -99, 'MN_VTS': -99, 'MX_VTS': -99, 'AST': -99, 'LB_OBA': '0.892', 'LB_OBB': '0.832', 'LB_SBA': '0.243', 'LB_SBB': '0.243', 'XTDP': '350.70', 'FDR': -99, 'FST': -99, 'FPL': -99, 'FATP': -99, 'FATC': -99, 'NO_OBS': '.2432', 'NO_SBS': '.2432', 'NO_VTS': 10.036, 'VTL': 0, 'HTL': -99, 'CHK': None}, 'LCST_Static.tst_-99_101.00_2872.90': {'DIR': 'C:/Users/---/Desktop/Test ---', 'FILE': 'Static.tst', 'ID': 'ST', 'VSL': 'Static.tst', 'RAD': -99, 'PROP': -99, 'WD': -99, 'ROT': -99, 'HS': -99, 'TZ': -99, 'WEA': -99, 'NTT': '2872.90', 'NBT': '2215.00', 'MX_OBS': -99, 'MX_SBS': -99, 'MN_VTS': -99, 'MX_VTS': -99, 'AST': -99, 'LB_OBA': '0.910', 'LB_OBB': '0.851', 'LB_SBA': '0.338', 'LB_SBB': '0.338', 'XTDP': '399.50', 'FDR': -99, 'FST': -99, 'FPL': -99, 'FATP': -99, 'FATC': -99, 'NO_OBS': '.2423', 'NO_SBS': '.2423', 'NO_VTS': 4.094, 'VTL': 0, 'HTL': -99, 'CHK': None}, 'LCST_Static.tst_-99_64.00_4006.90': {'DIR': 'C:/Users/---/Desktop/Test ---', 'FILE': 'Static.tst', 'ID': 'ST', 'VSL': 'Static.tst', 'RAD': -99, 'PROP': -99, 'WD': -99, 'ROT': -99, 'HS': -99, 'TZ': -99, 'WEA': -99, 'NTT': '4006.90', 'NBT': '3443.80', 'MX_OBS': -99, 'MX_SBS': -99, 'MN_VTS': -99, 'MX_VTS': -99, 'AST': -99, 'LB_OBA': '0.917', 'LB_OBB': '0.856', 'LB_SBA': '0.205', 'LB_SBB': '0.205', 'XTDP': '348.60', 'FDR': -99, 'FST': -99, 'FPL': -99, 'FATP': -99, 'FATC': -99, 'NO_OBS': '.2526', 'NO_SBS': '.2526', 'NO_VTS': 11.684, 'VTL': 0, 'HTL': -99, 'CHK': None}, 'LCST_Static.tst_-99_82.00_3983.00': {'DIR': 'C:/Users/---/Desktop/Test ---', 'FILE': 'Static.tst', 'ID': 'ST', 'VSL': 'Static.tst', 'RAD': -99, 'PROP': -99, 'WD': -99, 'ROT': -99, 'HS': -99, 'TZ': -99, 'WEA': -99, 'NTT': '3983.00', 'NBT': '3324.00', 'MX_OBS': -99, 'MX_SBS': -99, 'MN_VTS': -99, 'MX_VTS': -99, 'AST': -99, 'LB_OBA': '0.923', 'LB_OBB': '0.862', 'LB_SBA': '0.236', 'LB_SBB': '0.236', 'XTDP': '385.50', 'FDR': -99, 'FST': -99, 'FPL': -99, 'FATP': -99, 'FATC': -99, 'NO_OBS': '.2525', 'NO_SBS': '.2525', 'NO_VTS': 7.871, 'VTL': 0, 'HTL': -99, 'CHK': None}, 'LCST_Static.tst_-99_59.00_3470.00': {'DIR': 'C:/Users/---/Desktop/Test ---', 'FILE': 'Static.tst', 'ID': 'ST', 'VSL': 'Static.tst', 'RAD': -99, 'PROP': -99, 'WD': -99, 'ROT': -99, 'HS': -99, 'TZ': -99, 'WEA': -99, 'NTT': '3470.00', 'NBT': '2933.40', 'MX_OBS': -99, 'MX_SBS': -99, 'MN_VTS': -99, 'MX_VTS': -99, 'AST': -99, 'LB_OBA': '0.901', 'LB_OBB': '0.841', 'LB_SBA': '0.251', 'LB_SBB': '0.251', 'XTDP': '328.90', 'FDR': -99, 'FST': -99, 'FPL': -99, 'FATP': -99, 'FATC': -99, 'NO_OBS': '.2468', 'NO_SBS': '.2468', 'NO_VTS': 8.732, 'VTL': 0, 'HTL': -99, 'CHK': None}, 'LCST_Static.tst_-99_75.00_4210.90': {'DIR': 'C:/Users/---/Desktop/Test ---', 'FILE': 'Static.tst', 'ID': 'ST', 'VSL': 'Static.tst', 'RAD': -99, 'PROP': -99, 'WD': -99, 'ROT': -99, 'HS': -99, 'TZ': -99, 'WEA': -99, 'NTT': '4210.90', 'NBT': '3589.20', 'MX_OBS': -99, 'MX_SBS': -99, 'MN_VTS': -99, 'MX_VTS': -99, 'AST': -99, 'LB_OBA': '0.938', 'LB_OBB': '0.876', 'LB_SBA': '0.205', 'LB_SBB': '0.205', 'XTDP': '387.90', 'FDR': -99, 'FST': -99, 'FPL': -99, 'FATP': -99, 'FATC': -99, 'NO_OBS': '.2507', 'NO_SBS': '.2507', 'NO_VTS': 6.926, 'VTL': 0, 'HTL': -99, 'CHK': None}, 'LCST_Static.tst_-99_56.00_2983.90': {'DIR': 'C:/Users/---/Desktop/Test ---', 'FILE': 'Static.tst', 'ID': 'ST', 'VSL': 'Static.tst', 'RAD': -99, 'PROP': -99, 'WD': -99, 'ROT': -99, 'HS': -99, 'TZ': -99, 'WEA': -99, 'NTT': '2983.90', 'NBT': '2463.30', 'MX_OBS': -99, 'MX_SBS': -99, 'MN_VTS': -99, 'MX_VTS': -99, 'AST': -99, 'LB_OBA': '0.923', 'LB_OBB': '0.861', 'LB_SBA': '0.313', 'LB_SBB': '0.313', 'XTDP': '312.60', 'FDR': -99, 'FST': -99, 'FPL': -99, 'FATP': -99, 'FATC': -99, 'NO_OBS': '.2463', 'NO_SBS': '.2463', 'NO_VTS': 6.903, 'VTL': 0, 'HTL': -99, 'CHK': None}, 'LCST_Static.tst_-99_70.00_4186.90': {'DIR': 'C:/Users/---/Desktop/Test ---', 'FILE': 'Static.tst', 'ID': 'ST', 'VSL': 'Static.tst', 'RAD': -99, 'PROP': -99, 'WD': -99, 'ROT': -99, 'HS': -99, 'TZ': -99, 'WEA': -99, 'NTT': '4186.90', 'NBT': '3592.00', 'MX_OBS': -99, 'MX_SBS': -99, 'MN_VTS': -99, 'MX_VTS': -99, 'AST': -99, 'LB_OBA': '0.941', 'LB_OBB': '0.878', 'LB_SBA': '0.200', 'LB_SBB': '0.200', 'XTDP': '381.50', 'FDR': -99, 'FST': -99, 'FPL': -99, 'FATP': -99, 'FATC': -99, 'NO_OBS': '.2525', 'NO_SBS': '.2525', 'NO_VTS': 6.288, 'VTL': 0, 'HTL': -99, 'CHK': None}, 'LCST_Static.tst_-99_49.00_3534.90': {'DIR': 'C:/Users/---/Desktop/Test ---', 'FILE': 'Static.tst', 'ID': 'ST', 'VSL': 'Static.tst', 'RAD': -99, 'PROP': -99, 'WD': -99, 'ROT': -99, 'HS': -99, 'TZ': -99, 'WEA': -99, 'NTT': '3534.90', 'NBT': '3051.70', 'MX_OBS': -99, 'MX_SBS': -99, 'MN_VTS': -99, 'MX_VTS': -99, 'AST': -99, 'LB_OBA': '0.924', 'LB_OBB': '0.861', 'LB_SBA': '0.226', 'LB_SBB': '0.226', 'XTDP': '325.40', 'FDR': -99, 'FST': -99, 'FPL': -99, 'FATP': -99, 'FATC': -99, 'NO_OBS': '.2157', 'NO_SBS': '.2157', 'NO_VTS': 5.982, 'VTL': 0, 'HTL': -99, 'CHK': None}, 'LCST_Static.tst_-99_70.00_4177.90': {'DIR': 'C:/Users/---/Desktop/Test ---', 'FILE': 'Static.tst', 'ID': 'ST', 'VSL': 'Static.tst', 'RAD': -99, 'PROP': -99, 'WD': -99, 'ROT': -99, 'HS': -99, 'TZ': -99, 'WEA': -99, 'NTT': '4177.90', 'NBT': '3582.80', 'MX_OBS': -99, 'MX_SBS': -99, 'MN_VTS': -99, 'MX_VTS': -99, 'AST': -99, 'LB_OBA': '0.922', 'LB_OBB': '0.860', 'LB_SBA': '0.200', 'LB_SBB': '0.200', 'XTDP': '394.60', 'FDR': -99, 'FST': -99, 'FPL': -99, 'FATP': -99, 'FATC': -99, 'NO_OBS': '.2204', 'NO_SBS': '.2204', 'NO_VTS': 3.514, 'VTL': 0, 'HTL': -99, 'CHK': None}, 'LCST_Static.tst_-99_45.00_3387.90': {'DIR': 'C:/Users/---/Desktop/Test ---', 'FILE': 'Static.tst', 'ID': 'ST', 'VSL': 'Static.tst', 'RAD': -99, 'PROP': -99, 'WD': -99, 'ROT': -99, 'HS': -99, 'TZ': -99, 'WEA': -99, 'NTT': '3387.90', 'NBT': '2926.00', 'MX_OBS': -99, 'MX_SBS': -99, 'MN_VTS': -99, 'MX_VTS': -99, 'AST': -99, 'LB_OBA': '0.918', 'LB_OBB': '0.856', 'LB_SBA': '0.235', 'LB_SBB': '0.235', 'XTDP': '319.70', 'FDR': -99, 'FST': -99, 'FPL': -99, 'FATP': -99, 'FATC': -99, 'NO_OBS': '.1815', 'NO_SBS': '.1815', 'NO_VTS': 4.863, 'VTL': 0, 'HTL': -99, 'CHK': None}, 'LCST_Static.tst_-99_62.00_4096.90': {'DIR': 'C:/Users/---/Desktop/Test ---', 'FILE': 'Static.tst', 'ID': 'ST', 'VSL': 'Static.tst', 'RAD': -99, 'PROP': -99, 'WD': -99, 'ROT': -99, 'HS': -99, 'TZ': -99, 'WEA': -99, 'NTT': '4096.90', 'NBT': '3544.60', 'MX_OBS': -99, 'MX_SBS': -99, 'MN_VTS': -99, 'MX_VTS': -99, 'AST': -99, 'LB_OBA': '0.923', 'LB_OBB': '0.861', 'LB_SBA': '0.195', 'LB_SBB': '0.195', 'XTDP': '382.10', 'FDR': -99, 'FST': -99, 'FPL': -99, 'FATP': -99, 'FATC': -99, 'NO_OBS': '.1862', 'NO_SBS': '.1862', 'NO_VTS': 3.105, 'VTL': 0, 'HTL': -99, 'CHK': None}} 

The output is as follows. But, looking at the output, it seems that the values for each key of the nested dictionary are not correctly presented in each column. Note that I added 'LC-ID' as additional key to pass the name of the nested dictionary to the csv.

Output:
LC_ID,DIR,FILE,ID,VSL,RAD,PROP,WD,ROT,HS,TZ,WEA,NTT,NBT,MX_OBS,MX_SBS,MN_VTS,MX_VTS,AST,LB_OBA,LB_OBB,LB_SBA,LB_SBB,XTDP,FDR,FST,FPL,FATP,FATC,NO_OBS,NO_SBS,NO_VTS,VTL,HTL,CHK
LCST_Static.tst_-99_69.00_3048.90,C:/Users/---/Desktop/Test ---,Static.tst,ST,Static.tst,-99,-99,-99,-99,-99,-99,-99,3048.90,2533.50,-99,-99,-99,-99,-99,0.892,0.832,0.243,0.243,350.70,-99,-99,-99,-99,-99,.2432,.2432,10.036,LCST_Static.tst_-99_69.00_3048.90,-99,LCST_Static.tst_-99_69.00_3048.90
LCST_Static.tst_-99_101.00_2872.90,C:/Users/---/Desktop/Test ---,Static.tst,ST,Static.tst,-99,-99,-99,-99,-99,-99,-99,2872.90,2215.00,-99,-99,-99,-99,-99,0.910,0.851,0.338,0.338,399.50,-99,-99,-99,-99,-99,.2423,.2423,4.094,LCST_Static.tst_-99_101.00_2872.90,-99,LCST_Static.tst_-99_101.00_2872.90
LCST_Static.tst_-99_64.00_4006.90,C:/Users/---/Desktop/Test ---,Static.tst,ST,Static.tst,-99,-99,-99,-99,-99,-99,-99,4006.90,3443.80,-99,-99,-99,-99,-99,0.917,0.856,0.205,0.205,348.60,-99,-99,-99,-99,-99,.2526,.2526,11.684,LCST_Static.tst_-99_64.00_4006.90,-99,LCST_Static.tst_-99_64.00_4006.90
LCST_Static.tst_-99_82.00_3983.00,C:/Users/---/Desktop/Test ---,Static.tst,ST,Static.tst,-99,-99,-99,-99,-99,-99,-99,3983.00,3324.00,-99,-99,-99,-99,-99,0.923,0.862,0.236,0.236,385.50,-99,-99,-99,-99,-99,.2525,.2525,7.871,LCST_Static.tst_-99_82.00_3983.00,-99,LCST_Static.tst_-99_82.00_3983.00
LCST_Static.tst_-99_59.00_3470.00,C:/Users/---/Desktop/Test ---,Static.tst,ST,Static.tst,-99,-99,-99,-99,-99,-99,-99,3470.00,2933.40,-99,-99,-99,-99,-99,0.901,0.841,0.251,0.251,328.90,-99,-99,-99,-99,-99,.2468,.2468,8.732,LCST_Static.tst_-99_59.00_3470.00,-99,LCST_Static.tst_-99_59.00_3470.00
LCST_Static.tst_-99_75.00_4210.90,C:/Users/---/Desktop/Test ---,Static.tst,ST,Static.tst,-99,-99,-99,-99,-99,-99,-99,4210.90,3589.20,-99,-99,-99,-99,-99,0.938,0.876,0.205,0.205,387.90,-99,-99,-99,-99,-99,.2507,.2507,6.926,LCST_Static.tst_-99_75.00_4210.90,-99,LCST_Static.tst_-99_75.00_4210.90
LCST_Static.tst_-99_56.00_2983.90,C:/Users/---/Desktop/Test ---,Static.tst,ST,Static.tst,-99,-99,-99,-99,-99,-99,-99,2983.90,2463.30,-99,-99,-99,-99,-99,0.923,0.861,0.313,0.313,312.60,-99,-99,-99,-99,-99,.2463,.2463,6.903,LCST_Static.tst_-99_56.00_2983.90,-99,LCST_Static.tst_-99_56.00_2983.90
LCST_Static.tst_-99_70.00_4186.90,C:/Users/---/Desktop/Test ---,Static.tst,ST,Static.tst,-99,-99,-99,-99,-99,-99,-99,4186.90,3592.00,-99,-99,-99,-99,-99,0.941,0.878,0.200,0.200,381.50,-99,-99,-99,-99,-99,.2525,.2525,6.288,LCST_Static.tst_-99_70.00_4186.90,-99,LCST_Static.tst_-99_70.00_4186.90
LCST_Static.tst_-99_49.00_3534.90,C:/Users/---/Desktop/Test ---,Static.tst,ST,Static.tst,-99,-99,-99,-99,-99,-99,-99,3534.90,3051.70,-99,-99,-99,-99,-99,0.924,0.861,0.226,0.226,325.40,-99,-99,-99,-99,-99,.2157,.2157,5.982,LCST_Static.tst_-99_49.00_3534.90,-99,LCST_Static.tst_-99_49.00_3534.90
LCST_Static.tst_-99_70.00_4177.90,C:/Users/---/Desktop/Test ---,Static.tst,ST,Static.tst,-99,-99,-99,-99,-99,-99,-99,4177.90,3582.80,-99,-99,-99,-99,-99,0.922,0.860,0.200,0.200,394.60,-99,-99,-99,-99,-99,.2204,.2204,3.514,LCST_Static.tst_-99_70.00_4177.90,-99,LCST_Static.tst_-99_70.00_4177.90
LCST_Static.tst_-99_45.00_3387.90,C:/Users/---/Desktop/Test ---,Static.tst,ST,Static.tst,-99,-99,-99,-99,-99,-99,-99,3387.90,2926.00,-99,-99,-99,-99,-99,0.918,0.856,0.235,0.235,319.70,-99,-99,-99,-99,-99,.1815,.1815,4.863,LCST_Static.tst_-99_45.00_3387.90,-99,LCST_Static.tst_-99_45.00_3387.90
LCST_Static.tst_-99_62.00_4096.90,C:/Users/---/Desktop/Test ---,Static.tst,ST,Static.tst,-99,-99,-99,-99,-99,-99,-99,4096.90,3544.60,-99,-99,-99,-99,-99,0.923,0.861,0.195,0.195,382.10,-99,-99,-99,-99,-99,.1862,.1862,3.105,LCST_Static.tst_-99_62.00_4096.90,-99,LCST_Static.tst_-99_62.00_4096.90

[mart79]

Hi all,

I did a small check against one key only to try finding out the reason for the mismatch in data when parsing the dictionary to the csv:

import csv

fields = ['CHK']
a = {'LCDY_PS-OT_PSC_46.90_-6.5_0.0': {'DIR': 'C:/Users/Usr/Desktop/Test/Test 1', 'FILE': 'PS-OT;R300;PSC;WD47;ROT-6.5;SEA2.0-6.0;DIR000-180.ofw', 'ID': 'DY', 'VSL': 'PS-OT', 'RAD': '300', 'PROP': 'PSC', 'WD': '47', 'ROT': '-6.5', 'HS': '2.0', 'TZ': '6.0', 'WEA': [0.0], 'NTT': '1200.00', 'NBT': '844.10', 'MX_OBS': '.2023', 'MX_SBS': '.2031', 'MN_VTS': 4.06, 'MX_VTS': 5.51, 'AST': '51.52', 'LB_OBA': '0.645', 'LB_OBB': '0.606', 'LB_SBA': '0.547', 'LB_SBB': '0.547', 'XTDP': '289.90', 'FDR': '0.017', 'FST': '9.25', 'FPL': '24.4', 'FATP': '5.04856863478451E-0005', 'FATC': '1.27143382878945E-0004', 'NO_OBS': '.1988', 'NO_SBS': '.1988', 'NO_VTS': 4.7, 'VTL': 0.0, 'HTL': 0.0, 'CHK': None}}


with open("C:\\Users\\MBo\\Desktop\\test_output.csv", "w") as f:
    w = csv.DictWriter(f, fields)
    w.writeheader()
    for k in a:
        w.writerow({field: a[k].get(field) or k for field in fields})

This returns:

Output:
CHK

LCDY_PS-OT_PSC_46.90_-6.5_0.0

Which I do not understand. Why is it returning this when it should return "None".
If I check it against:

fields = ['VSL']  

It returns:

Output:
VSL

PS-OT

[ichabod801]

Why is it returning this when it should return "None".

Because you have an 'or' instead of a 'for' on line 11. It seems you have the same error in the code you used to output the original csv. That makes it output the key instead of the field value whenever the field value evaluates to False.

You don't need to extract the values from the dicionaries as you write them, the DictWriter will do that for you. You can just do w.writerow(data_file[k]). The restval and extrasection parameters of the DictWriter can be used to control the behavior for missing/extra keys.

[mart79]

Why is it returning this when it should return "None".

Because you have an 'or' instead of a 'for' on line 11. It seems you have the same error in the code you used to output the original csv. That makes it output the key instead of the field value whenever the field value evaluates to False.

You don't need to extract the values from the dicionaries as you write them, the DictWriter will do that for you. You can just do w.writerow(data_file[k]). The restval and extrasection parameters of the DictWriter can be used to control the behavior for missing/extra keys.

Thanks! However, how do I get the 'LC-ID', so the first entry which is the name of the nested dictionary, in the csv?
Now it stays blank if I run the script.

[ichabod801]

Before you writerow, do data_file[k]['LC-ID'] = k.

[mart79]

Doing this gives an error:

Error:
ValueError: dict contains fields not in fieldnames: 'LC-ID'

I did the following:

for k in data_file:
            data_file[k]['LC-ID'] = k

            w.writerow(data_file[k])

[ichabod801]

Whoops, that should be LC_ID (underscore not dash) to match the field list you are passing to DictWriter.

[mart79]

Thanks! Could have figured that out myself .