Splitting a csv into multiple csv files by column

Question

Trying to learn Python and managed to create a script that takes a csv, turns it into a data frame, changes the columns and then outputs a csv to the desired style.

Well what I need to do now is to be able to output multiple csvs based on the contents of my second column (first is the index, which I remove for output)

I have set up a parameter for unique data values and then a FOR loop to create filenames and output paths based on the unique data value.

But when I output the csv (data.to_csv), all 4 files are the same and unfiltered.

Here is my code

unique_code = data.import_code.unique() 
for importcode in unique_import_codes:     
    #print("%s" % importcode)             
    filename = importcode.replace(".","") + ".csv"   
    #print("%s" % filename)                
    path = r"C:/myrequiredpath/"     
    #print("%s" % path)                    
    data.to_csv(path+filename, index=False)

my data frame is called data import_code is my second column (not an index)

any ideas welcome!

MaxU - stand with Ukraine · Accepted Answer · 2017-09-19 20:17:59Z

2

I'd do it this way:

filename =  r"C:/myrequiredpath/{}.csv"

data.groupby('import_code') \
    .apply(lambda g: g.to_csv(filename.format(g.name), index=False))

answered Sep 19, 2017 at 20:17

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

Comments

zipa · Accepted Answer · 2017-09-19 19:55:17Z

0

You can use loc to filter data:

unique_code = data.import_code.unique() 
for importcode in unique_import_codes:
    filename = importcode.replace(".","") + ".csv"
    path = r"C:/myrequiredpath/"
    data.loc[data.import_code==importcode].to_csv(path+filename, index=False)

answered Sep 19, 2017 at 19:55

zipa

28k6 gold badges45 silver badges62 bronze badges

Comments

kdopen · Accepted Answer · 2017-09-19 19:52:33Z

0

Nowhere in your loop do you do anything to select a subset of data. The last line

data.to_csv(path+filename, index=False)

Just writes out the unchanged data frame with a different filename each time.

answered Sep 19, 2017 at 19:52

kdopen

8,2357 gold badges50 silver badges57 bronze badges

Comments

user6142489 · Accepted Answer · 2017-09-19 20:00:03Z

0

If your goal is to just export the file, each being a copy of the original dataframe with the unique values as a name, then I would go this route.

unique_values = set(data['column_of_interest'])

for value in unique_values:
    filename = value + ".csv"         
    path = r"C:/myrequiredpath/"                        
    data.to_csv(path+filename, index=False)

If you want each file to be an export of that subset of the data, then add this to your loop:

data[data['column of interest']==value]

answered Sep 19, 2017 at 20:00

user6142489

5522 gold badges12 silver badges28 bronze badges

Collectives™ on Stack Overflow

Splitting a csv into multiple csv files by column

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related