How to save python list(dictionary) to csv file

Question

I have the following list(dictionary):

db = [{'Cat1':    DB_Field1 DB_Field2 DB_Field3
  4        1F1       1F2       1F3
  5        2F1       2F2       2F3
  6        3F1       3F2       3F3
  8        NaN       NaN       NaN
  11       NaN       NaN       NaN
  14       NaN       NaN       NaN},
 {'Cat2':    DB_Field4 DB_Field5
  4        1F4       1F5
  5        2F4       2F5
  6        3F4       3F5
  8        NaN       NaN
  11       NaN       NaN
  14       NaN       NaN},
 {'Cat3':    DB_Field6
  4        1F6
  5        2F6
  6        3F6
  8        NaN
  11       NaN
  14       NaN},
 {'Cat4':    DB_Field7 DB_Field7 DB_Field8 DB_Field9
  4        1F7       1F7       1F8       1F9
  5        2F7       2F7       2F8       2F9
  6        3F6       3F6       3F8       3F9
  8        NaN       NaN       NaN       NaN
  11       NaN       NaN       NaN       NaN
  14       NaN       NaN       NaN       NaN},
 {'Cat5':    DB_Field10 DB_Field11 DB_Field12 DB_Field13 DB_Field13 DB_Field13  \
  4        1F10       1F11       1F12          1          1          1   
  5        2F10       2F11       2F12          2          2          2   
  6        3F10       3F11       3F12          3          3          3   
  8         NaN        NaN        NaN        NaN        NaN        NaN   
  11        NaN        NaN        NaN        NaN        NaN        NaN   
  14        NaN        NaN        NaN        NaN        NaN        NaN   
  
     DB_Field13 DB_Field13  
  4           1          1  
  5           2          2  
  6           3          3  
  8         NaN        NaN  
  11        NaN        NaN  
  14        NaN        NaN  }]

I would like to save it in the following format:

How do I do that?

What I tried so far?

pd.DataFrame(db).to_csv('test.csv')

This does not save entire dictionary only the keys are saved like the following:

jezrael · Accepted Answer · 2021-04-08 10:33:56Z

1

One idea is use concat with DataFrame.dropna and DataFrame.to_csv, but get repeated first values of data in csv, because is written MultiIndex:

pd.concat(db, axis=1).dropna().to_csv('test.csv', index=False)

EDIT: If there are nested DataFrames use first concat for join them to list of DataFrames and then use second concat for one DataFrame:

(pd.concat([pd.concat(x, axis=1) for x in db], axis=1)
              .dropna().to_csv('test.csv', index=False))

edited Apr 8, 2021 at 10:33

answered Apr 8, 2021 at 10:24

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MuSu18 Over a year ago

It is a pandas.DataFrame output as list. Hence, it produces the following error: 'TypeError: cannot concatenate object of type '<class 'dict'>'; only Series and DataFrame objs are valid'

jezrael Over a year ago

@MuSu18 - How working pd.concat([pd.concat(x, axis=1) for x in db], axis=1) ?

Collectives™ on Stack Overflow

How to save python list(dictionary) to csv file

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related