Using numpy to convert a list of lists with headers into csv

Question

I have a list of lists which also contains headers. Headers don't match with all the lists and not all the lists have the same count. Something like this:

[
[['Row', '1'], ['header1', '1.23'], ['header2','5.67'], ['header3','6.55']],
[['Row', '2'], ['header2','19.67'], ['header3','9.55']],
[['Row', '3'], ['header2','19.67'], ['header3','9.55'], ['header4','16.88']]
]

I would like to convert it to a CSV with all header values filled and missing header values filled with "N/A"

like this:

Row, Header1, Header2, Header3, Header4
1, 1.23, 5.67, 6.55, N/A
2, N/A, 19.67, 9.55, N/A
3, N/A, 19.67, 9.55, 16.88

I was wondering how to go about doing this

Graipher · Accepted Answer · 2018-12-09 11:06:51Z

1

You can convert your datastructure into a list of dictionaries. Then you can use pandas.DataFrame.from_records:

import pandas as pd

data = [
[['Row', '1'], ['header1', '1.23'], ['header2','5.67'], ['header3','6.55']],
[['Row', '2'], ['header2','19.67'], ['header3','9.55']],
[['Row', '3'], ['header2','19.67'], ['header3','9.55'], ['header4','16.88']]
]

df = pd.DataFrame.from_records(map(dict, data)).set_index("Row").fillna("N/A")
#     header1 header2 header3 header4
# Row                                
# 1      1.23    5.67    6.55     N/A
# 2       N/A   19.67    9.55     N/A
# 3       N/A   19.67    9.55   16.88
df.to_csv("file.csv")

edited Dec 9, 2018 at 11:06

answered Dec 9, 2018 at 11:03

Graipher

7,24630 silver badges49 bronze badges

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

2 Comments

PuppyHeadedNinja Over a year ago

I actually have a list of lists. I made the error of separating values with a : when I posted the question.

Graipher Over a year ago

@PuppyHeadedNinja: Yeah, I fixed it so it first converts it to a list of dicts

Collectives™ on Stack Overflow

Using numpy to convert a list of lists with headers into csv

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