0

So in order to refresh my powerBI dashboard I need to write queries to Excel. Otherwise I have to run every single query and do it myself.

I now build the following Python code:

import pandas as pd
from pathlib import Path

data_folder = Path("PATH")

file_to_open = data_folder / "excelfile.xlsx"

df = pd.read_excel(file_to_open)

query_1 = 5
query_2 = 3
query_3 = 12

df.loc[df.iloc[-1,-1]+1,['A']] = query_1

df.loc[df.iloc[-1,-1]+1,['B']] = query_2

df.loc[df.iloc[-1,-1]+1,['C']] = query_3

print(df) #for testing#

df.to_excel(file_to_open, index = False)

It somehow puts query_1 in the right spot (right after the last value in column A) But query_2 and query_3 both skip one cell. They should all fill in the next empty cell in my excelsheet. My columns are A, B and C.

Can someone help me out?

dannyboots
  • 3
  • 2

1 Answers1

0

I think this should work:

df.loc[df.A.count(), 'A'] = query_1
df.loc[df.B.count(), 'B'] = query_2
df.loc[df.C.count(), 'C'] = query_3

If you are curious, here is a good answer regarding different ways to count rows/columns: https://stackoverflow.com/a/55435185/11537601

Peter Schindler
  • 276
  • 1
  • 10
  • I still get the same 'skipping a cell' problem. – dannyboots Aug 17 '20 at 11:15
  • Mhm, strange. How does your table look like before adding query_1, 2, and 3? – Peter Schindler Aug 19 '20 at 06:35
  • Each column has 2-11 values so, A: 1,2,3,4. B: 1,2. C: 1,2,3,4,5,6,7,8,9,10,11 – dannyboots Aug 20 '20 at 07:14
  • @dannyboots I just created a dummy xlsx sheet with the example that you gave and I use the 3 lines of code that I provided above and it works without problems (no empty cells). Can you test your code again with a fresh excel sheet? Not sure what could have gone wrong. You're using a fairly new version of pandas? – Peter Schindler Aug 20 '20 at 08:23
  • Awesome! It works now. Probably something wrong with the excel file. Thanks man :) – dannyboots Aug 20 '20 at 09:08
  • @dannyboots Awesome! Glad it worked! Btw, on stackoverflow you can select an answer to have solved your problem (that way other people having a similar problem will see that it has been solved). Good luck with your coding project! – Peter Schindler Aug 20 '20 at 20:54
  • Ok Peter, I applied this function on your answer. Thanks again. – dannyboots Sep 03 '20 at 13:51