Python - how to loop through unique values in column, create dataframe and output to csv for each on-CodePudding

I’m new to python and trying to get a loop working. I’m trying to iterate through a dataframe – for each unique value in the “Acct” column I want to create a csv file, with the account name. So there should be an Account1.csv, Account2.csv and Account3.csv created.

When I run this code, only Account3.csv is being created (I’m guessing the others are being created but overwritten somehow?)

data = [('Account1', 'ibm', 20),
('Account1', 'aapl', 30),
('Account1', 'googl', 15),
('Account1', 'cvs', 18),
('Account2', 'wal', 24),
('Account2', 'abc', 65),
('Account2', 'bmw', 98),
('Account2', 'deo', 70),
('Account2', 'glen', 40),
('Account3', 'aapl', 50),
('Account3', 'googl', 68),
('Account3', 'orcl', 95)]

data_df = pd.DataFrame(data, columns = ['Acct', 'ticker', 'qty'])

acctlist = data_df["Acct"].drop_duplicates()

save_to = "c:/temp/csv/"
save_as = Acct   ".csv"

for Acct in acctlist:
    #print(data_df[data_df["Acct"] == Acct])
    Acct_df = (data_df[data_df["Acct"] == Acct])
    Acct_df.to_csv(save_to   save_as)

CodePudding user response：

You never rename save_as in the loop. Try this:

for Acct in acctlist:
    save_as = Acct   ".csv"
    Acct_df = (data_df[data_df["Acct"] == Acct])
    Acct_df.to_csv(save_to   save_as)

Note that looping over a pandas dataframe is generally a bad idea as it is inefficient. It might work fine in this case especially with a small dataframe but in general you should try to avoid it.

You can accomplish this same task using apply:

import os
data_df.groupby('Acct').apply(
    lambda group: pd.DataFrame.to_csv(group, os.path.join(save_to, f'{group.name}.csv'))
)

CodePudding user response：

An alternative way to split apart and write out your CSVs:

for acct, frame in df_out.groupby('Acct'):
    frame.to_csv(os.path.join(save_to, f'{acct}.csv'))