Python - Pandas - Write Dataframe to CSV

Question

I'm trying to write a 4 table, 3 column, and 50 row dataframe file to a csv using pandas. I'm getting the following error AttributeError: 'dict' object has no attribute 'to_csv'. I believe I'm writing the syntax correctly, but could anyone point out where my syntax is incorrect in trying to write a dataframe to a csv?

'dict' object has no attribute 'to_csv'

import pandas as pd
import numpy as np

df = pd.read_excel("filelocation.xlsx",
    sheetname=['pnl1 Data ','pnl2 Data','pnl3 Data','pnl4 Data'],
    skiprows=8, parse_cols="B:D", keep_default_na='FALSE', na_values=['NULL'])

df.to_csv('filelocation.csv', line_terminator=',', index=False, header=False) #error occurs on this line

as you are reading a xlsx with multiple sheets, you get a dict of dataframes, with keys as the sheetname, and data as a dataframe. You will need to turn this into a single dataframe (concat, or merge based on your requirements), or write csvs in a loop from the dict. — jeremycg
– jeremycg, Commented Mar 9, 2016 at 1:06
Do you know what syntax I could use to call a sheetname key? — pHorseSpec
– pHorseSpec, Commented Mar 9, 2016 at 1:38

Daniel · Accepted Answer · 2016-04-29 07:14:34Z

Your intuition is right; there's nothing wrong with the syntax in your code.

You are receiving the AttributeError because you are reading data from multiple sheets within your workbook, generating a dictionary of DataFrames (instead of one DataFrame), from which you attempt to_csv (a method only available to a DataFrame).

As your code is written, the keys of the dictionary you generate correspond to the names of the worksheets, and the values are the respective DataFrames. It's all explained in the docs for the read_excel() method.

To write a csv file containing the aggregate data from all the worksheets, you could loop through the worksheets and append each DataFrame to your file (this works if your sheets have the same structure and dimensions):

import pandas as pd
import numpy as np

sheets = ['pnl1 Data ','pnl2 Data','pnl3 Data','pnl4 Data']

for sheet in sheets:
    df = pd.read_excel("filelocation.xlsx",
        sheetname=sheet,
        skiprows=8, 
        parse_cols="B:D", 
        keep_default_na='FALSE', 
        na_values=['NULL'])

    with open('filelocation.csv', 'a') as f:
        df.to_csv(f, line_terminator=',', index=False, header=False)

Collectives™ on Stack Overflow

Python - Pandas - Write Dataframe to CSV

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related