Convert Panda Row Index into Row Number for Google Sheet Formula in a Data Frame-CodePudding

I have a Pandas Data Frame indexed from 2 to 100 with stock symbols in the first column "Ticker". In the second column I want to create a formula that will do a price lookup when the df is exported to Google Sheets.

How do I explicitly refer to the df row index number in this example?

Basically, I want to insert a new df column called "Current Price" and then have the formula:

=GOOGLEFINANCE($a2, 'price')
=GOOGLEFINANCE($a3, 'price')
=GOOGLEFINANCE($a4, 'price')
=GOOGLEFINANCE($a5, 'price')

in the Rows of this second column beginning in row 2 and down through the end of the df.

EDIT - I have added some stand alone code to show what I want to do:

import pandas as pd
import numpy as np

# Create a Panda df of stock tickers
lst = ['MSFT', 'GOOG', 'APPL', 'BGFV', 'DD']
df = pd.DataFrame(lst)

# Change the index so it begins with 2 instead of 0
df.index = np.arange(2, len(df)   2)
df.rename(columns={0:'Ticker'}, inplace=True)

# Create a second Panda df of row numbers
row=[]
for i in range(len(df)):
  row.append(i 2)
row_df = pd.DataFrame(row)

# Change the index so it begins with 2 instead of 0
row_df.index = np.arange(2, len(df)   2)
row_df.rename(columns={0:'Row_Number'}, inplace=True)

row_df['Row_Number'].astype(str).astype(int)

# merge the Row Number into the first df
df['Row_Number'] = row_df['Row_Number']
df.head(3)

# create a new colum that will have the Google Finance formula
df['Current_Price'] = '=(GOOGLEFINANCE($a'   str(df['Row_Number'])  ', "price"))'
# Should read like "MSFT, 2, =(GOOGLEFINANCE)$a2, "price""

df.head(3)

CodePudding user response：

You could do:

df['index] = list(range(2, 101))

And then:

df['Current_Price'] = '=(GOOGLEFINANCE($a'   str(df['index'])  ', "price"))'

I don't know why you want to do this, but this will get you a index column from 2 to 100 in your df['Current_Prince'].

CodePudding user response：

It took me a while to figure this out, but the key is to use the dataframe.at command as in this example https://www.askpython.com/python-modules/pandas/update-the-value-of-a-row-dataframe

Here is the revised code:

import pandas as pd
import numpy as np

# Create a Panda df of stock tickers
lst = ['MSFT', 'GOOG', 'APPL', 'BGFV', 'DD']
df = pd.DataFrame(lst)

df["Current Price"] = ""

for i in range(len(df)):  #determines the number of rows in the df
  row = str(i   2)  # creates the offset for the google sheet row number
  df.at[i,'Current Price']='=(GOOGLEFINANCE($a'   row  ', "price"))'

df.head(3)