Recently, I have had the opportunity to automate my work. As for the content,
--Use the gspread library to extract schedules on Google Sheets --Perform operations including simple aggregation and relocation, and reassemble into pandas data frame --Reprint the collected data frame to Google Spreadsheet
It was a relatively simple thing. The current degree of perfection is about 80%. It was more difficult than I expected and I got a lot of things, so I am writing this article so that I can summarize it for the future. It may be a little messy, but I hope you can see it with warm eyes.
The following is a summary of knowledge-related learning, mainly in the library and program specifications.
I think this is a matter of course for those who are familiar with data frames. I made the same mistake when dealing with lists before, but I didn't think of it and made it again.
The situation is that you are trying to initialize a dictionary with a datetime type as a key with an empty data frame as shown below. I made it like "I want to group people who use sections by date, so register an empty data frame for the time being!"
python
import pandas as pd
import datetime
empty_user = dict(Section1=['', '', ''], Section2=['', '', ''], Section3=['', '', ''])
hour = ['9:00', '10:00', '11:00']
df = pd.DataFrame(data=empty_user, index=hour)
five_days_list = [(datetime.datetime(2020, 9, 1) + datetime.timedelta(days=1) * i) for i in range(5)]
dict_of_dataframe = {date_key : df for date_key in five_days_list}
The completed dictionary looks like this.
date_key : 2020-09-01 00:00:00
value :
Section1 Section2 Section3
9:00
10:00
11:00
date_key : 2020-09-02 00:00:00
#Omitted below
About a week ago, I didn't have any doubts about this and proceeded to the next task. However, when I start registering the aggregated data, it doesn't work at all. Of the one-month schedule, the third and subsequent days are messed up.
The reason is simple when I think about it now. This method of creation simply reuses the same data frame over and over again. It will be more noticeable if you output using id ().
python
for date_key, dataframe in dict_of_dataframe.items():
print(f"date_key : {date_key}")
print(f"dataframe_id : {id(dataframe)}")
date_key : 2020-09-01 00:00:00
dataframe_id : 2124838088520
date_key : 2020-09-02 00:00:00
dataframe_id : 2124838088520
date_key : 2020-09-03 00:00:00
#Omitted below
Correctly, I had to use the pandas.DataFrame method copy ().
python
dict_of_dataframe = {date_key : df.copy() for date_key in five_days_list}
date_key : 2020-09-01 00:00:00
dataframe_id : 2124838588936
date_key : 2020-09-02 00:00:00
dataframe_id : 2124838590152
date_key : 2020-09-03 00:00:00
Pandas official documentation has a good description, but a copy of the data frame The () method defaults to deep = True, a deep copy. If you just want to use it as a template, you have to use it as an object. When I noticed it, I felt the shock of being hit.
As will be described later, this work was accompanied by the karma of a fairly deep data structure and the accompanying multiple loop structure. At first, I was only concerned about that person, so I think that I was suffering for about two days due to the struggle of that and this, such as outputting the progress, reading the pandas document, writing the loop structure on paper, etc. I will.
I thought I knew a little about the basic grammar of Python, but I first met this child during development. Or I knew it but forgot it. If you use an else statement together with a for statement, the else statement will be executed ** only if you cannot break the loop of the for statement **.
I think there are countless uses, but I used it to get a log notification when there is no free section as below.
python
client_salesman_dict = {datetime.datetime(2020, 9, 1, 9, 0) : [('Mr. Yamada', 'Takahashi'), ('Mr. Yoshizawa', 'Ito')],
datetime.datetime(2020, 9, 1, 10, 0) : [('Mr. Sasaki', 'Momoyama')],
datetime.datetime(2020, 9, 1, 11, 0) : [('Mr. Yokota', 'Takahashi'), ('Fukuchi', 'large tree'), ('Mr. Nakayama', 'Ito'), ('Mr. Gonda', 'Ozawa')],}
section_list = ['Section1', 'Section2', 'Section3',]
for date_dt, client_salesman_tuples in client_salesman_dict.items():
date_str = f"{date_dt.hour}:{date_dt.minute:02}"
for client_salesman_tuple in client_salesman_tuples:
client = client_salesman_tuple[0]
salesman = client_salesman_tuple[1]
for section in section_list:
section_status = df.loc[date_str, section]
print(f"client : {client}, salesman : {salesman}")
print(f"time is {date_str}")
print(f"section is {section}")
print(f"section_status is {section_status}")
if section_status:
print(f"bool of section_status is {bool(section_status)}.")
print("I will skip writing phase.")
continue
print(f"I have applied {client},{salesman} to {section}")
df.loc[date_str, section] = f"{client} {salesman}"
break
else:
print(f"There is no empty section for{client}, {salesman}.Please recheck schedule.")
client :Mr. Yamada, salesman :Takahashi
time is 9:00
section is Section1
section_status is
I have applied Mr. Yamada,Takahashi to Section1
client :Mr. Yoshizawa, salesman :Ito
time is 9:00
section is Section1
section_status is Mr. Yamada Takahashi
bool of section_status is True.
I will skip writing phase.
#Omission
There is no empty section for Gonda,Ozawa.Please recheck schedule.
Once upon a time, when I was doing similar processing in C language or Java, I think I was doing my best with a flag set inside, but it seems unnecessary for Python. It's sober, but there are many opportunities to use the for sentence itself, so I would like to continue using it as appropriate.
I knew it existed for a long time, but I didn't use it myself with particular consciousness. If you test the behavior properly, you'll see something like this.
python
class TestClass:
def __init__(self):
self.hoge = 1
self._fuga = 2
self.__monge = 3
def _foo1(self):
print("_foo1 is called")
def __foo2(self):
print("__foo2 is called")
t = TestClass()
#Instance variables
print(t.hoge)
print(t._fuga)
# print(t.__monge)← I can't call
print(t._TestClass__monge)
#Class method
t._foo1()
# t.__foo2()← I can't call
t._TestClass__foo2()
1
2
3
_foo1 is called
__foo2 is called
If you add two underscores at the beginning, you will not be able to call instance variables or class methods as usual. However, it is not a rugged Private attribute as in Java,
python
instance.__ClassName_AttributeName
It is possible to call it with.
Also, if you put one underscore at the beginning ... you can also call this. Moreover, normally. When I wondered, "Well, what is it for?", I found the following site.
[Python] How to use underscore (_) (special attribute, dunders)
According to this, apparently
――When it is one, it only suggests that it is for internal use, and the operation does not change in particular. However, it will not be loaded only when called with wildcards as a module. --When there are two, the name will be mangled (name mangling), so you will not be able to access it as it is. However, it is not intended to be private, but is used to avoid name conflicts between parent-child relationships.
It seems. In the first place, it was strange to recognize that it was for making private. This time, I created the program without inheriting the class, so using one underscore was enough.
I somehow knew that it also existed, but this was the first time I used it. I refer to the following articles when creating.
[Python] Learn how to write a docstring to improve readability (NumPy style)
Since this program was developed for myself, I wrote it while thinking "Is it necessary?", But as a result, "what to use", "for what", and "what to do" It was an opportunity to think firmly. Up until now, it was an ad hoc way to start writing somehow, move it once, and then fix it, but I think writing a docstring has made it a little better.
The following is the point that I learned empirically, "I think this is better" than knowledge.
When I first started putting together my schedule, I was putting together my data in a dictionary like this:
python
from datetime import datetime
from datetime import datetime
schedule_dict = {'1week': {datetime(2020, 9, 1) : {datetime(2020, 9, 1, 9, 0): [('Mr. Yamada', 'Terada'),('Mr. Yoshiki', 'Endo'),],
datetime(2020, 9, 1, 10, 0): [('Mr. Kudo', 'Yamashita'),],},
datetime(2020, 9, 2) : {datetime(2020, 9, 2, 10, 0): [('Mr. Tsurukawa', 'Honda'),],
datetime(2020, 9, 2, 11, 0): [('Mr. Endo', 'Aizawa'),],},
datetime(2020, 9, 2) : {datetime(2020, 9, 3, 9, 0): [('Mr. Shimoda', 'Terada'), ('Mr. Yoshikawa', 'Goda')],
}
}
'2week': ....}
It looks like this when accessing.
python
schedule_dict['2week'][datetime(2020, 9, 8)][datetime(2020, 9, 8, 10, 0)]
Since the data on the spreadsheet side was arranged side by side every week, I did not think about anything in particular, but it was uselessly deep. It's still cool to access, and the loop hierarchy gets deeper and deeper when expanding with a for statement when using it.
After all, I took root in the middle of the work, lost the week key, and made it shallower by one level. When I review it again, I feel that I don't need the date key at 0 o'clock. If you need it, you can access each of the year, month, and day attributes of datetime.datetime type and recreate them.
It is often said that "uselessly deep loops should be avoided", but I learned that "it should also be avoided to create uselessly deep layers of data" that causes it. I feel that the burden per process is doubled ** each time the hierarchy is deepened. The memory of the brain is sucked hard ...
At the beginning of development, the name when testing the partial part was used as it is for the variable name. Since the processing was classified according to the target, there is no name conflict. I tried to write as short as possible, such as df for data frames, date for dates, and dct for dictionaries.
However, I immediately hit the wall. It's more about the memory of the brain than the problem with the program specifications. As the process becomes more complicated, "Oh, what's in this dictionary?" "I'm throwing an error, but isn't this date a string type?" "Index out of range ??? There were many problems such as "Isn't the list of the number of elements that I had?"
Until now, I wrote only a sample program to understand the specifications lightly, so I didn't care so much, but ** the name should be given so that it can be understood no matter where it flies **. .. It seems natural in words, and I hadn't really thought about it.
date_dt for datetime type dates, date_str for str type dates. After that, name it client_salesman_tuples_list to indicate what is stored, and use the name used when expanding in the for statement, such as for client_salesman_tuple in client_salesman_tuples_list :, considering the singular and plural forms. I did it.
Thanks to you, it's a little easier to understand than it was in the beginning. In the first place, it would be ideal not to perform processing that would confuse the brain, but if the skill for that is not enough, it will be insufficient, and I would like to keep in mind that the name will be devised.
I'm not sure if this is really correct. However, about two weeks ago, when I put it together from the part to the whole, I wrote about 100 lines flat and returned to myself. It would be a big deal if it was in this condition. I got a petite revelation from the following site that came out after a little research.
I feel that object-oriented is the task of properly dividing the namespace self through the class.
I was dumbfounded for a while when I saw the word "appropriate division", but suddenly I came up with the idea "** Isn't this the same as a government office? **".
Some time ago, I had to go to the city hall to get a resident card and wait for a while. While I was waiting, I was looking at the site of the government office without any reason, but it was really finely divided. Citizens, business and industry tourism, construction, city planning etc etc ... At first glance, some people may think, "Isn't construction and city planning the same?", But in reality, they are divided by the △△ section of the ○○ department. I don't know the contents of the work, but it seems that they are well shared. This is the same as "** Classification **".
Occasionally, departmental collaboration may be required, but not all information will be passed. Paper and data will be flooded. It makes sense for the person in charge to bring and discuss as much information as is needed for the job. This is the same as "** Synthesize from inheritance **".
Furthermore, "I" who applied for the acquisition of a resident's card does not know what kind of processing is being done inside. I can imagine that the person in charge fills out the documents by inputting various things on the computer, but all I do is write the application form and pay for it. Then you can receive the resident card without any problem. Perhaps even those who work at the counter do not know everything about what the specifically written documents and the entered data are used for and how they are stored. This is the same as "** Information hiding **".
With that kind of feeling, when I imagined a government office in my head, I managed to classify it. Even if I write it myself, I'm not sure if it really fits this understanding, but I was able to write it in a curved manner, so I'm tentatively OK.
I thought, Qiita had a timely article. I think this is a much easier explanation, so please refer to it.
Finally, I will write that I had some knowledge and ideas, but in the end I could not use it well.
I didn't know it again, but there is a library called itertools in the Python standard library. For an easy-to-understand explanation, refer to this post, but what I focused on here is the product method in itertools.
This product produces output similar to a typical multiple loop, as described in the Official Documentation (https://docs.python.org/3/library/itertools.html#itertools.product). Will give you.
python
import itertools
section_list = ['Section1', 'Section2', 'Section3']
time_list = ['9:00', '10:00', '11:00']
for section in section_list:
for time in time_list:
print(section, time)
print("------------")
for section, time in list(itertools.product(section_list, time_list)):
print(section, time)
The output is the same.
Section1 9:00
Section1 10:00
Section1 11:00
Section2 9:00
Section2 10:00
Section2 11:00
Section3 9:00
Section3 10:00
Section3 11:00
------------
Section1 9:00
Section1 10:00
Section1 11:00
Section2 9:00
Section2 10:00
Section2 11:00
Section3 9:00
Section3 10:00
Section3 11:00
You can also receive it as a tuple.
python
for tpl in list(itertools.product(section_list, time_list)):
print(tpl)
('Section1', '9:00')
('Section1', '10:00')
('Section1', '11:00')
('Section2', '9:00')
('Section2', '10:00')
('Section2', '11:00')
('Section3', '9:00')
('Section3', '10:00')
('Section3', '11:00')
I really wanted to use this to reduce the loop hierarchy, but it didn't work. As mentioned above, I painfully understood that increasing the hierarchy of loops directly leads to an increase in the burden on the brain, so I would like to use itertools as well as the idea of data structures in the future.
As an advice for beginners in programming, I often hear "Why don't you try making something for the time being?", But I understand that this is really reasonable advice. I had a hard time about 10 times as much as I imagined, but I feel that I got a lot of things.
If you have any opinions or advice, please leave them in the comments section.
Recommended Posts