Home > Software design >  create csv from dictionaries of dataframes and drop header
create csv from dictionaries of dataframes and drop header

Time:04-12

I want to create/save new csv dataframes from two dictionaries. The new dataframe should have the values of each dictionary according to the dict keys. Here is an example of my dictionaries:

dict1 = {
     '1': 3.84,
     '45': 4.9,
     '135': 6.7
}

dict2 = {
    '1': pd.DataFrame([[101,        105,        106 ],
                        [245,        134,        96  ],
                        [175,        105,        200 ]], 
                    columns=['AST_0-1'   ,'AST_10-1'   , 'AST_110-1']),

    '45': pd.DataFrame([[101,        105,        106 ],
                        [245,        134,        96  ],
                        [175,        105,        200 ]],
                    columns=['AST_0-45'  ,'AST_10-45'  , 'AST_110-45']),

    '135': pd.DataFrame([[101,      105,        106   ],
                        [245,      134,        96    ],
                        [175,      105,        200   ]],
                    columns=['AST_0-135' ,'AST_10-135' , 'AST_110-135'])
}

My final dataframes should have the values from each dictionaries key. This would be an example of one of the dataframes (key =1 from dict1 and dict2):

3.84

101        105        106 

245        134        96

175        105        200

I was able to "group" both dictionaries by key using this code:

dict3 = defaultdict(list)

for d in (dict1,dict2):

   for key, value in d.items():

       dict3[key].append(value)

However my dict3 has the headers of the dataframes in dict 2.

   defaultdict(list,
     {'1': [3.84,
        AST_0-1   AST_10-1    AST_110-1 \
     1    101        105        106 
     2    245        134        96
     3    175        105        200 ],

   '45': [4.9,
       AST_0-45  AST_10-45  AST_110-45 \
     1    101        105        106 
     2    245        134        96
     3    175        105        200 ],

   '135': [6.7,
       AST_0-135 AST_10-135 AST_110-135 \
     1      101      105        106 
     2      245      134        96
     3      175      105        200 ]})

Is there a way to group both dictionaries without the header and then save each key as new csv dataframe?

CodePudding user response:

You seem to have some misunderstanding of what a CSV file is -- The output you show is simply the repr() of your dictionary, which in turn shows the repr() of everything it contains, i.e. you see the keys (e.g. '1'), and the values of the keys (the lists). Inside the list, you have a single number (e.g. 3.84) and the dataframe.

If you want a csv file, you need to create one. To do this, let's create a function that writes a CSV file given a file handle, a dataframe, and the number using pandas's inbuilt to_csv() with header=False and index=False to suppress the header row and index columns, and sep='\t' to separate columns by a tab:

def write_to_csv(file_handle, df, number):
    file_handle.write(f"{number}\n")
    df.to_csv(file_handle, sep='\t', header=False, index=False)

Now, loop over the keys in one of your dicts, and call the function:

for k in dict1:
    file_name = f"{k}.csv"
    with open(file_name, "w") as out_file:
        write_to_csv(out_file, dict2[k], dict1[k])

Which will write three files with the following contents:
1.csv:

3.84
101 105 106
245 134 96
175 105 200

45.csv

4.9
101 105 106
245 134 96
175 105 200

135.csv

6.7
101 105 106
245 134 96
175 105 200
  • Related