Merging multi row csv file into one using python

Question

I have the following dictionary:

details = {"Primary_key" : [{'key_1': 'val',  'key_3': "val", 'key_5': ['val_1', 'val_2', 'val_3'], 'key_6': 'val'}, {'key_2': 'val', 'key_3': 'val', 'key_5': ['val_1','val_2'], 'key_6': 'val'}, {'key_1': 'val', 'key_2': 'val', 'key_3': 'val', 'key_4': 'val', 'key_5': ['val_1', 'val_2'], 'key_6': 'val'}] }

I have the following code which converts this into a csv file.

    import pandas as pd
    for name,val in details.items():
        df = pd.DataFrame.from_dict(details[name])
        df.index = [name]*len(df)
        print df.index
        with open("my_file.csv",'a') as f:
            df.to_csv(f)

The key_x being the header, primary_key being the name and val as the text, I got the following output(example of the output)..

Is there a way I can get the csv file in the following format?

Zero · Accepted Answer · 2017-08-06 05:57:11Z

3

This is a good use case for pd.concat (vertical concatenation):

import pandas as pd
df_list = []
for name,val in details.items():
    df = pd.DataFrame.from_dict(details[name])
    df.index = [name] * len(df)
    df_list.append(df)

pd.concat(df_list).fillna('').to_csv('my_file.csv')

This also involves the use of df.fillna('') to replace NaN with empty string, so it looks cleaner.

edited Aug 6, 2017 at 5:57

Zero

77.4k22 gold badges153 silver badges153 bronze badges

answered Aug 6, 2017 at 3:54

cs95

406k106 gold badges744 silver badges797 bronze badges

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

2 Comments

Scott Boston Over a year ago

I like your answer! +1

cs95 Over a year ago

@ScottBoston Thank you. We have the same answer almost, so I will return the favour :) Also, I made the mistake of removing OP's name index, that I've added back.

Zero · Accepted Answer · 2017-08-06 05:58:23Z

3

IIUC, you could do something like this create a list of dataframes then use pd.concat to concatenate them vertically, Pandas does intrinsic data alignment on indexes, so it the columns will match as you intend.

list_of_dfs = {}
for name,val in details.items():
       df = pd.DataFrame.from_dict(details[name])
       df.index = [name]*len(df)
       list_of_dfs['name'] = df

df_out = pd.concat(list_of_dfs)
df_out.to_csv(f)

edited Aug 6, 2017 at 5:58

Zero

77.4k22 gold badges153 silver badges153 bronze badges

answered Aug 6, 2017 at 3:53

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

Collectives™ on Stack Overflow

Merging multi row csv file into one using python

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related