Mailing List Archive

On 6/26/2023 1:46 PM, small marcc via Python-list wrote:
> pandas.ExcelWriter
>
> import pandas
>
> This code creates the path to the Excel file where the data will be written. It checks if the file already exists, and if so, reads the existing data into a DataFrame. Otherwise, it creates a new empty DataFrame. Then it concatenates the existing data with the new data and writes the result to the Excel file. Finally, it displays a message that the backup was successful and closes the information window.
>
> I do not understand why it does not work, the new data is not displayed in excel when I am in python tkinter, my data in python must be displayed in excel.
> I use the following code to write data to an excel file using pandas:
>
> please help meHowever, when I run this code, the new data is not showing in the excel file.

Do you meant the modified data is not displayed in a linked Excel
window, or that the Excel file itself does not get changed?

> I don't understand why this isn't working. Can you help me solve this problem?
>
> data = {'Numero du client': [numero_du_client],
> 'Prénom': [prenom],
> 'Nom': [nom],
> 'Adresse': [adresse],
> 'Numero de téléphone': [numero_de_tel],
> 'Longueur de cour': [longueur_de_cour],
> 'Largeur de cour': [largeur_de_cour],
> "Unite(p ou m)":[boutonPieds, boutonMetres] #edit
> "Prix_coutant:[prix_coutant], #new add he asks me to define when I haven't done for others
> "Prix soumission":[prix_soumission], # new add he asks me to define when I haven't done for others
> "MargeProfit":[marge_profit]} # new dd he asks me to define when I haven't done for others
>
>
> #my link with live excel valid for any move as long as the script and excel are in the same folder
>
>
> chemin_script = os.path.abspath(__file__)
> dossier_script = os.path.dirname(chemin_script)
> nom_fichier_excel = "testpython.xlsx"
> chemin_fichier_excel = os.path.join(dossier_script, nom_fichier_excel)
> df = pd.DataFrame(data)
> if os.path.exists(chemin_fichier_excel):
> df_existant = pd.read_excel(chemin_fichier_excel)
> else:
> df_existant = pd.DataFrame()
> df_concat = pd.concat([df_existant, df], ignore_index=True)
> df_concat.to_excel(chemin_fichier_excel, index=False)
> with pd.ExcelWriter(path, engine='xlsxwriter') as writer:
> df.to_excel(writer, index=False, sheet_name='Clients')
> print("sauvegarde reussi")
> fenetre_info.destroy()
>

--
https://mail.python.org/mailman/listinfo/python-list

Marc,

Apologies for writing in English (the language of this Discussion List).
I have attempted to keep the code in French...

Thanks for this question. If it's OK with you, I would like to use it as
an example-problem (and 'solution') challenge for some future Python
Users' Group meeting (suitably translated, etc)...


On 27/06/2023 05.46, small marcc via Python-list wrote:

> This code creates the path to the Excel file where the data will be written. It checks if the file already exists, and if so, reads the existing data into a DataFrame. Otherwise, it creates a new empty DataFrame. Then it concatenates the existing data with the new data and writes the result to the Excel file. Finally, it displays a message that the backup was successful and closes the information window.
>
> I do not understand why it does not work, the new data is not displayed in excel when I am in python tkinter, my data in python must be displayed in excel.
> I use the following code to write data to an excel file using pandas:
>
> please help meHowever, when I run this code, the new data is not showing in the excel file. I don't understand why this isn't working. Can you help me solve this problem?

Is this a Python problem, or a problem with the way the solution has
been coded?

There are several steps in the solution-description. It is troubling (to
both of us) that you are unable to locate the source of the error. Why?
Because it is not noticed until the final stage "new data is not displayed"!

NB am ignoring that the line defining "df" and everything thereafter,
appears to be incorrectly indented - when viewed in my email-client.
That said, the sequence of events seems muddled.


Please review the code. Compare it to the verbal/English-language
description of the solution. Can each sentence in the solution be easily
identified in the code?

Words like "and", "otherwise", "then", and "finally" indicate that there
are multiple steps in the process. Let's split the separate sentences
and phrases:

- creates the path to the Excel file where the data will be written
- checks if the file already exists
[establish DataFrame]
- if so, reads the existing data into a DataFrame
- Otherwise, it creates a new empty DataFrame
- concatenates the existing data with the new data
- writes the result to the Excel file
- displays a message that the backup was successful
- closes the information window.

NB addition of a task-label summarising the two DataFrame alternative
situations.


Now, we can outline the Python code by writing a bunch of function
definitions:

def create_data_path():
def check_for_file():
def establish_data_frame():
def read_existing_data():
def create_new_data_frame():
def accept_new_data():
def save_to_file():
def report():
def terminate():

NB my IDE would grumble and complain if I did this 'for real'. In a 'new
code' situation, you may like to add a pass-statement inside each, and
vertical-spacing to make the distracting-complaints 'go away'.
Alternately and assuming the function-name doesn't say it all, add a
docstring (you're going to add one anyway, right?) and expand upon the
function-name with some explanation of what the function will do. In
this situation, you could add the existing code.

NBB the two sub-tasks have been indented here for continuity of flow.
They may/may not be nested within establish_data_frame() when you detail
the content of each function - not important to this outline.

NBBB I've quickly drafted function-names for the sake of this
discussion. I find it easier to name things when it is time to use them
(in this case, call the function). Fortunately, a competent IDE will
facilitate the refactoring of names ("symbols"). Yes, doing it that way
costs (me) little extra effort - but it saves straining my brain
up-front. That said, investing time in 'designing' a good name, will
also tend to clarify and sharpen one's thinking. So, apparently I follow
both paths... YMMV!


Now that the solution has been outlined, one can consider what output
you expect from each function, what input it will require to produce
that, and the transformation in-between. For example:

def create_data_path( nom_fichier ):
* copy existing code into here
return chemin_fichier_excel

(pausing at the one function, for now...)

[.for non-French speakers:
nom_fichier = file_name
chemin_fichier_excel = data path including file-name
]


How has this design-process improved things?

1 clearer thinking: we have a simple and more straightforward idea of
what should happen
2 "separation of concerns": each function does one job (and should do it
well!)
3 testability: we can now ensure that the (each) function performs
correctly (in isolation)
4 the problem of locating the source of an error is simplified (in fact,
the "testability" advantage may solve this problem - or in a new-design
situation, avoid it*)


Testing the function is carried-out by calling the function with a
variety of example input-data, and ensuring the result is what you
expect/want/require. Thus:

chemin = create_data_path( "testpython.xlsx" )
assert "chemin\testpython.xlsx" == chemin, "Not equal, but should be"

[for non-French speakers:
chemin = path
]


(an assert statement says: if the condition is False, print an optional
message, and raise an exception/stop processing)

similarly:

chemin = create_data_path( "testpython.xlsx" )
assert "path_to_project\niaiserie.xlsx" != chemin, "Should not be equal"

[for non-French speakers:
niaiserie = some random name/silliness
]

Such tests should not be written as one-off code (the way one might add
debug-printing - and then take it out again). If you ever have need to
copy such functions into other projects (eg create_data_path(), then
you'll want to copy the tests as well, to ensure everything works in the
new scenario!

Accordingly, create a new .py file, with "test_" plus the name of the
existing code-file. Inside that, import the code-file. The code the
tests in the test file, to run each function in-turn (and several times,
with different data), to assure the function in the code-file.
(yes, if you see virtue in this approach, there are some very helpful
libraries to ease testing and automate it (better to have the machine do
the work, rather than you have to (keep) doing it!), eg pytest (py.test)
- worth some investigation time?)


What has been achieved? By the time you've tested the first few
functions, you will know whether or not existing files are being
(correctly) used as 'base data'. Continuing 'down', you will discover if
new data is being correctly added/updating existing data. Further on,
you will find if the output is being correctly written to that file.

Sooner or later, you will have answered the "where is the error?"
problem 'here'! You will also have noticed any similar errors which may
be 'hiding' in the code, unnoticed (so far!)...
(and all without needing to ask someone-else for help)


* not entirely true. In this manner, each function can be
relatively-well proven with "unit tests". There is however, an
additional source of error: do the functions 'play well together'?
Ensuring this happens is the objective of another type of test:
"integration testing". For example, does check_for_file() work with the
output from the preceding stage: create_data_path()? Yes, these sorts of
tests could be included at the bottom of the 'unit test' script, or
written in a separate test-file...


Not forgetting, the "mainline" of the production program[me] needs to be
written! It will call each function in-turn, feeding specific data into
(at least) the first, eg which Excel file? Thereafter, likely feeding
output from earlier functions into later processing. Until, 'the end'. Fin!

NB the "mainline" will be a collection and compression of the
imperatives which could be copied from any "integration testing".

NBB what I call the "mainline" (a throw-back to my training, so long ago
it is lost in the mists of time...) fits into a Python idiom:

if __name__ == "__main__":
print( "\nMy wonderful program\n" ) # you'll do this in the GUI
intermediate_result_1 = function1()
intermediate_result_1 = function2( intermediate_result_1 )
...

NB it seems long-winded (and my IDE often grumbles about this too!) but
I often prefer creating 'intermediate results' rather than writing
'earlier' functions as parameters to 'later' functions. If any thing
goes wrong (how could it, after all that testing?) it's easier to answer
that famous question: "where is the error?".

That said: there are exceptions to every 'rule' - and YMMV!

--
Regards,
=dn
--
https://mail.python.org/mailman/listinfo/python-list