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

Solution

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)