CODEWITHSUNDEEP
pythonpandascsvdataframe
Joe
Joe

Reputation: 311

Merging multi row csv file into one using python

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).Output of the code.

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

Upvotes: 1

Views: 77

Answers (2)

Scott Boston
Scott Boston

Reputation: 153460

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)

Upvotes: 3

cs95
cs95

Reputation: 402323

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.

Upvotes: 3

Related Questions