Pandas DataFrames in a loop, df.to_csv()

Question

I am trying to write a df to a csv from a loop, each line represents a df, but I am finding some difficulties once the headers are not equal for all dfs, some of them have values for all dates and others no.

I am writing the df using a function similar to this one:

def write_csv():
    for name, df in data.items():
        df.to_csv(meal+'mydf.csv', mode='a')

and it creates a csv for each meal (lunch an dinner) each df is similar to this:

Name    Meal    22-03-18    23-03-18    25-03-18        
Peter   Lunch   12          10          9

or:

Name    Meal    22-03-18    23-03-18    25-03-18        
Peter   Dinner  12          10          9

I was trying to use pandas concatenate, but I am not finding a way to implement this in the function. My goal is to have the headers with all the dates (as the example of desired output), independent if the DataFrame appended to the csv have or not values in all dates.

Actual output:
Name    Meal    22-03-18    23-03-18    25-03-18        
Peter   Lunch   12          10          9       
Mathew  Lunch   12          11          11         10     9
Ruth    Lunch   9           9           8          9    
Anna    Lunch   10          12          11         13     10


output with headers:
Name    Meal    22-03-18    23-03-18    25-03-18           
Peter   Lunch   12          10          9       
Name    Meal    21-03-18    22-03-18    23-03-18    24-03-18    25-03-18
Mathew  Lunch   12          11          11          10          9
Name    Meal    21-03-18    22-03-18    24-03-18    25-03-18    
Ruth    Lunch   9           9           8           9   
Name    Meal    21-03-18    22-03-18    23-03-18    24-03-18    25-03-18
Anna    Lunch   10          12          11          13          10



Output desired:
Name    Meal    21-03-18    22-03-18    23-03-18    24-03-18    25-03-18
Peter   Lunch   12          10          9   
Mathew  Lunch               12          11          11           10
Ruth    Lunch   9           9           8           9
Anna    Lunch   10          12          11          13           10

@Djokester at the moment is tve Actual output. I need to have the desired output. I am trying to create a Main_df an then write in the end of the loop, but I have some constrains because my dfs are a df for each person with lunch or dinner, and the dates. — AmiB
– AmiB, Commented Mar 21, 2018 at 10:29

LogCapy · Accepted Answer · 2018-03-21 00:12:07Z

2

You could use the header = False flag for to_csv after the first iteration.

def write_csv():
    for i, (name, df) in enumerate(data.items()):
        df.to_csv('mydf.csv', mode='a', header=(i==0))

answered Mar 21, 2018 at 0:12

LogCapy

4677 silver badges21 bronze badges

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

Comments

saucoide · Accepted Answer · 2018-03-20 22:47:05Z

1

can you try something like this? not sure if is exactly what you want, but it will concatenate dataframes without fully overlapping columns

def write_csv():
    df2 = pd.DataFrame()
    for name, df in data.items():
        df2 = df2.append(df)
    df2.to_csv('mydf.csv')

answered Mar 20, 2018 at 22:47

saucoide

112 bronze badges

Comments

AmiB · Accepted Answer · 2018-03-21 12:22:40Z

0

Using the following logic(@saucoide) I get my desired output.

it was necessary to create an empty df, than populate it, then groupby meal and print to csv.

main_df= pd.DataFrame()

    for name, df in data.items():
        main_df = pd.concat([main_df, df])  

    main_df_group = main_df.groupby('Meal')
    for name, group in main_df_group:
        mydf_group = group

        mydf_group.to_csv(meal+ ...)

answered Mar 21, 2018 at 12:22

AmiB

411 gold badge1 silver badge4 bronze badges

Collectives™ on Stack Overflow

Pandas DataFrames in a loop, df.to_csv()

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related