CODEWITHSUNDEEP
pythonpandasdataframegoogle-sheets
Yvonne
Yvonne

Reputation: 77

Push data to google sheet from dataframe

I'm trying to push data into my google sheet with the following code, how can i change the code so that it will print in the 2nd row at the correct column base on the header that I've created.

First code:

class Header:
  def __init__(self):
    self.No_DOB_Y=1
    self.No_DOB_M=2
    self.No_DOB_D=3
    self.Paid_too_much_little=4
    self.No_number_of_ins=5
    self.No_gender=6
    self.No_first_login=7
    self.No_last_login=8
    self.Too_young_old=9
  def __repr__(self):
    return str(self.__dict__)

  def add_col(self,name):
    setattr(self,name,max(anomali_header.__dict__.values())+1)


anomali_header=Header()

2nd part of code (NEW):

# No_gender
a = list(df.loc[df['gender'].isnull()]['id'])
#print(a)

cells=sh3.range(1,1,len(a),1)
for i,cell in enumerate(cells):
  cell.value=a[i]

sh3.update_cells(cells)

At the moment it updates into A1 cell....

enter image description here

This is what I essentially want to

enter image description here

As you can see, the code writes the results onto the first available cell which is A1, i essentially want it to appear at the bottom of my anomali_header of "No_gender" but I'm not sure how to link my 1st part of the code to the 2nd part of the code...

Thanks to v25, the code below works, but rather than going through the code one by one, i wanted to create a loop which goes through all the function

I'm trying to run the code below, but it seems I get an error when I use the loop.

Error:

TypeError: 'list' object cannot be interpreted as an integer

Code:

# No_DOB_Y
a = list(df.loc[df['Year'].isnull()]['id'])

# No number of ins
b = list(df.loc[df['number of ins'].isnull()]['id'])

# No_gender
c = list(df.loc[df['gender'].isnull()]['id'])

# Updating anomalies to sheet
 condition = [a,b,c]
 column = [1,2,3]

for j in range(column,condition):
   cells=sh3.range(2,column,len(condition)+1,column)
   for i,cell in enumerate(cells):
     cell.value=condition[i]
print('end of check')

sh3.update_cells(cells)

Upvotes: 2

Views: 207

Answers (1)

v25
v25

Reputation: 7641

You need to change the range() parameters:

first_row (int) – Row number

first_col (int) – Row number

last_row (int) – Row number

last_col (int) – Row number

So something like:

cells=sh3.range(2, 6, len(a)+1, 6)

Or you could issue the range as a string:

cells=sh3.range('F2:F' + str(len(a)+1))

These numbers may not be perfect, but this should change the positioning. You might need to tweak the digits slightly ;)

UPDATE:

I've encountered an error use a loop, updated my original post TypeError: 'list' object cannot be interpreted as an integer

This is happneing because the function range which you use in the for loop (not to be confused with sh3.range which is a different function altogether) expects integers, but you're passing it lists.

However, a simpler way to implement this would be to create a list of tuples which map the strings to column integers, then loop based on this. Something like:

col_map = [ ('Year', 1),
      ('number of ins', 5),
      ('gender', 6)
    ]

for col_tup in col_map:
    df_list = list(df.loc[df[col_tup[0]].isnull()]['id'])

    cells = sh3.range(2, col_tup[1], len(df_list)+1, col_tup[1])

    for i, cell in enumerate(cells)
        cell.value=df_list[i]

    sh3.update_cells(cells)

Upvotes: 2

Related Questions