I am currently working on a data engineering project where I have a list of nested dictionaries representing user profiles, including nested 'address' and 'contact' keys. I need to export this data into a standard CSV format for a business report. How can I flatten these nested keys into columns like 'address_city' or 'contact_email' using the CSV module or Pandas? Is there a built-in function to handle this, or do I need to write a custom recursive function to prepare the data for the writer?
3 answers
The most efficient "Pythonic" way to handle this is using the Pandas library, specifically the json_normalize function. Since a CSV file is a 2D table, it cannot natively store nested structures. json_normalize takes your list of nested dictionaries and automatically creates flattened column names using a separator (like a dot or underscore). For example, pd.json_normalize(data, sep='_').to_csv('output.csv') will convert { "user": { "id": 1 } } into a column named user_id. If you prefer not to use Pandas, you would need to manually iterate through the dictionary and "flatten" it by merging keys before passing the flat dictionary to csv.DictWriter.
Are you dealing with a single level of nesting, or is your dictionary structure deeply recursive with varying depths that might require a more complex flattening algorithm?
You should definitely use Pandas. It turns a complex task into just two lines of code. Just import pandas, use json_normalize, and then to_csv.
I agree with Nancy. Why reinvent the wheel with manual loops? Using a proven library like Pandas reduces the chance of bugs, especially when you have to deal with complex encoding issues in the final CSV.
Steven, for deeply recursive data, I’ve found that a custom recursive function is sometimes safer than json_normalize, especially if the keys overlap at different levels. However, for 90% of the automation tasks we do here at iCertGlobal, the Pandas approach is far superior because it handles missing keys gracefully by inserting NaN values. This prevents the DictWriter from throwing errors when one dictionary in your list has more fields than another. It's all about ensuring data integrity before the export happens.