ValueError: AAA is not a valid column name-CodePudding

What I am trying to do is that I read xlsm excel file to get a list of tickers(symbols) I inputted, using those to web crawl the corresponding value, and then export to the same xlsm file but a new sheet.

money = openpyxl.load_workbook(r'file_path', read_only=False, keep_vba=True)
allocation=money['Allocation']

df1 = pd.DataFrame (allocation.values)
df1.columns = df1.iloc[6]  #set header
df1 = df1[7:]
df2=df1.iloc[0:,1].dropna()
tickers = df2.drop(df2.tail(1).index).tolist()

From the list of tickers, I use that info to web crawl the value, and create a dictionary "closing_price"

for ticker in tickers:
closing_price[ticker]=sector_price_1

So far, things work fine. The problem is when I am trying to export the information to a new sheet created in the original workbook by:

price_data= money.create_sheet('price_data')
price_data.append(closing_price)
money.save(r'file_path')

For the second line of code, it says ValueError: AAPL is not a valid column name. I tried adding column head("AAA") by transforming dict to dataframe first by,

closing_price_df= pd.DataFrame(list(closing_price.items()),columns=['Ticker','Price'])

but append() doesn't accept dataframe. So I re-transform back to dict from dataframe, which I though should have a new header added already after what I just did, then it shows ValueError: Ticker is not a valid column name. What else can I do?

Thanks in advance.

CodePudding user response：

worksheet.append() will append one row at a time into an sheet. I think you are trying to write the whole dataframe. Instead try something like this...

## Just an example of closing_price dummy data
closing_price = [['AAPL','22nd Mar','1234'], ['AMZN','22nd Mar','1111']]
for row in closing_price:
    price_data.append(row)

CodePudding user response：

The problem is here, where append handles dicts:

        elif isinstance(iterable, dict):
        for col_idx, content in iterable.items():
            if isinstance(col_idx, str):
                col_idx = column_index_from_string(col_idx)

Since you have AAPL there, the column_index_from_string function expects to get letters or combination of letters such as B, E , AA, AF etc. and convert them to column numbers for you. Since you don't have that inside the dictionary but some ticker-data pairs, this of course fails. This would mean that you would have to alter the dictionary so as to have keys that represent column identifiers and values that represent lists of data that you would fill under that column.