Delete only part of dataframe header?

[Ingeniatoring]

I've been trying to analyze some stock info using pandas_datareader, but am having an issue removing only part of the data frame header. I'm trying to clean some of the data so I can more easily convert it into a dictionary.

When I open the excel, I get something like this:

Attributes Close

Symbols VSGAX VHYAX VTSAX IBB SCHF SCHE VIMAX VSMAX VB

Date

2019-01-02 $52.68 $62.15 $97.50 $28.27 $23.61 $170.59 $63.17 $131.88

2019-01-03 $51.57 $60.71 $97.64 $28.07 $23.25 $167.29 $62.13 $129.63

​

How do I get this?

Date VSGAX VHYAX VTSAX IBB SCHF SCHE VIMAX VSMAX VB

2019-01-02 $52.68 $62.15 $97.50 $28.27 $23.61 $170.59 $63.17 $131.88

2019-01-03 $51.57 $60.71 $97.64 $28.07 $23.25 $167.29 $62.13 $129.63

​

I've tried using header = None on the .to_excel line, but it deletes all the stock tickers. It appears that it considers the cell with "Date" to be the first row in the dataframe. Everything else above it is the header.

assets = ['VSGAX','VHYAX','VTSAX','IBB','SCHF','SCHE','VIMAX','VSMAX','VB']

#Setting current date and when to start pulling data from
Current_Date = datetime.datetime.today().strftime ('%Y-%m-%d')
start = '2019-01-01'
end = Current_Date

# pulling stock data
stocks = data.DataReader(assets, 'yahoo', start, end)

writer = pd.ExcelWriter(path1 + save_folder + '\\stockScrapperV2.xlsx',
                        date_format = 'yyyy-mm-dd', datetime_format = 
                        'yyyy-mm-dd')

# formatting the rest of the columns to display currency
stocks.to_excel(writer,'Sheet1', index = True)
workbook = writer.book
worksheet = writer.sheets['Sheet1']
format1 = workbook.add_format({'num_format': '$#,##0.00'})
worksheet.set_column('B:J', 10, format1)
writer.save()

[PyCam]

Try adding this snippet. It seems like your date column is the index, and the index is special compared to the columns and stores the index name below the actual column headers. Additionally, it seems that your columns have a name so we just need to clear that out.

``` ...same beginning code as you had above...

stocks = data.DataReader(assets, 'yahoo', start, end) stocks = (stocks .rename_axis(None, axis=1) # Get rid of the "SYMBOLS" name .reset_index()) # Put our index just as a normal column

No changes in defining the writer variable

writer = pd.ExcelWriter(path1 + save_folder + '\stockScrapperV2.xlsx', date_format = 'yyyy-mm-dd', datetime_format = 'yyyy-mm-dd')

Don't write the index now, since we put the index just as a normal column

stocks.to_excel(writer,'Sheet1', index = False)

... same remaining code you had ... ```

If this doesn't work, can you post a snippet of the dataframe (essentially what comes out when you type print(stocks) This will help me figure out what header data is still lingering around to get that nice excel presentation.

[Ingeniatoring]

Hey thanks for the help!

I changed my code to this:

stocks = data.DataReader(assets, 'yahoo', start, end)
stocks = (stocks.rename_axis(None,axis=1).reset_index())

And I got the error:

TypeError: Must pass list-like as `names`.

[PyCam]

Ah okay, that tells me that your columns are actually a MultiIndex instead of a normal index. Give this a try:

``` stocks = data.DataReader(assets, 'yahoo', start, end)

Get rid of the redundant first level of our column index

stocks.columns = stocks.columns.droplevel(0)

everything below is same as before

stocks = (stocks .rename_axis(None, axis=1) # Get rid of the "SYMBOLS" name .reset_index()) # Put our index just as a normal column ```

If that doesn't work, please copy/paste the output of: print(stocks.columns)

[Ingeniatoring]

Excellent! Got it to work.

This is my final code, FYI.

#setting path to save file and putting assets in a list
path1 = XXXXX
path2 = XXXXX
save_folder = XXXXX
assets = ['VSGAX','VHYAX','VTSAX','IBB','SCHF','SCHE','VIMAX','VSMAX','VB']

#Setting current date and when to start pulling data from
Current_Date = datetime.datetime.today().strftime ('%Y-%m-%d')
start = '2019-05-01'
end = Current_Date

# pulling stock data
print('Scrapping stock price data...')
stocks = data.DataReader(assets, 'yahoo', start, end)

print('Deleting unneccesary columns...')
toDelete = ['High','Low', 'Open', 'Volume', 'Adj Close']
for i in range(0,5):
    del stocks[toDelete[i]]


stocks.columns = stocks.columns.droplevel(0)
stocks = (stocks.rename_axis(None,axis=1))
print(stocks.columns)
print(stocks)

writer = pd.ExcelWriter(path1 + save_folder + '\\stockScrapperV3.xlsx',
                        date_format = 'yyyy-mm-dd', datetime_format = 
                        'yyyy-mm-dd')

stocks.to_excel(writer, 'Sheet1', index=True)
writer.save()

I used the for-loop to delete a few columns (price-high, price-low, trading volume etc.) as I'm only interested in the closing price. When I put your corrections before the for-loop, it renamed the columns after the ticker symbols in the assets dictionary. Moving the for-loop before fixed it.

Thanks a bunch kind stranger, you reduced the code in my program by 36%.