Converting pandas dataframe to csv

Question

I have the dataframe above and I wish to convert it into a csv file.
I am currently using df.to_csv('my_file.csv') to convert it but I want to leave 3 blanks columns. For the rows of the file above I have the following procedure.

dirname = os.path.dirname(os.path.abspath(__file__))
csvfilename = os.path.join(dirname, 'MainFile.csv')
with open(csvfilename, 'wb') as output_file:
    writer = csv.writer(output_file, delimiter=',')
    writer.writerow([])
    writer.writerow(["","Amazon","Weekday","Weekend"])
    writer.writerow(["","Ebay",wdvad,wevad])
    writer.writerow(["","Kindle",wdmpv,wempv])
    writer.writerow([])

I want to incorporate the data frame right after the blanks space with three blank columns. How can I add the dataframe in continuation to the existing csv file so that I can also add more rows with data after the dataframe.

maybe I misunderstand the question but is there a reason why you cannot create a single dataframe with the format you want and then write that? — elelias
– elelias, Commented Oct 14, 2016 at 11:20
@elelias No I cannot, it contains several other lines that have different format each. Plus I spent a lot of time putting them in this format. Only the dataframe is left to input to the csv file and I can't figure out how to do it. Can you please suggest something? — amazingCodingExperience
– amazingCodingExperience, Commented Oct 14, 2016 at 11:27
What can't you just add those columns to your df with a default value and then write to csv? — EdChum
– EdChum, Commented Oct 14, 2016 at 12:08

Parfait · Accepted Answer · 2016-10-14 17:07:33Z

Consider outputting data frame initially as is to a temp file. Then, during creation of the MainCSV, read in temp file, iteratively writing lines, then destroy temp file. Also, prior to writing dataframe to csv, create the three blank columns.

Below assumes you want two tasks: 1) three blank columns and 2) writing dataframe values below Amazon/Ebay/Kindle row headers. Example data uses random normal values and scalar values for wdvad, wevad, wdmpv, wempv are the string literals of their names:

import csv, os
import pandas as pd

# TEMP DF CSV
dirname = os.path.dirname(os.path.abspath(__file__))    
df = pd.DataFrame([np.random.normal(loc=3.0, scale=1.0, size=24)*1000 for i in range(7)],
                  index=['Monday', 'Tuesday', 'Wednesday', 'Thursday', 
                         'Friday', 'Saturday', 'Sunday'])
df['Blank1'], df['Blank2'], df['Blank3'] = None, None, None   

df.to_csv(os.path.join(dirname, 'temp.csv'))       # OUTPUT TEMP DF CSV

# MAIN CSV
csvfilename = os.path.join(dirname, 'MainFile.csv')
tempfile = os.path.join(dirname, 'temp.csv')

wdvad = 'wdvad'; wevad = 'wevad'; wdmpv = 'wdmpv'; wempv = 'wempv'

with open(csvfilename, 'w', newline='') as output_file:
    writer = csv.writer(output_file)
    writer.writerow([""])
    writer.writerow(["","Amazon","Weekday","Weekend"])
    writer.writerow(["","Ebay",wdvad,wevad])
    writer.writerow(["","Kindle",wdmpv,wempv])
    writer.writerow([""])

    with open(tempfile, 'r') as data_file:
        for line in data_file:
            line = line.replace('\n', '')
            row = line.split(",")            
            writer.writerow(row)

os.remove(tempfile)                               # DESTROY TEMP DF CSV

Output

Collectives™ on Stack Overflow

Converting pandas dataframe to csv

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related