Try searching for a million character profile in Python
Introduction
This time I tried to make Excel search possible with Python, so I will put it as a memo. I'm glad if you can use it as a reference.
Sites that were greatly helped
https://gammasoft.jp/blog/excel-vlookup-by-python/ ↑ This time, referring to this site, I tried to omit or modify unnecessary parts.
Module import
#Module that can operate Excel files
import openpyxl
#Module that can format and display lists and dictionaries
from pprint import pprint
This time, I will use openpyxl, which allows you to operate Excel files, and a module called pprint, which can format and display the list. It is possible to output what can be output by print without pprint, but it is very difficult to see. Let's compare. Output result when using print
[['Ritsuko Akizuki','Fairy', 19, 43,'" 6/23"', '85 / 57/85', 156,' Qualification, Volunteer',' A'], ['Chihaya Kisaragi ',' Fairy', 16, 41,'"2/25"', '72/55/78', 162,'Music Appreciation, Photography',' A'], ['Shiho Kitazawa',' Fairy', 14, 46,'"1/18"', '83 / 56/84', 161,'Find your favorite picture book',' A'], ['Shijo Takane','Fairy', 18, 49 ,'"1/21"', '90 / 62/92', 169,'Astronomical Observation, History',' B'], ['Julia','Fairy', 16, 43,'" 9/26 " ', '79 / 54/80', 157,'None',' O'], ['Shiraishi Tsumugi','Fairy', 17, 45,'" 5/29"', '82 / 56/83' , 160,'Garden / Sweet Place Tour',' AB'], ['Momoko Suo','Fairy', 11, 35,'"11/6"', '73 / 53/74', 140,'Cute Collecting sounds',' B'], ['Tomoka Tenkubashi','Fairy', 15, 41,'"11/11"', '80 / 56/79', 156,'Make fans happy Koto',' AB'], ['Emi Tokoro','Fairy', 16, 47,'" 4/15 "', '88 / 56/85', 159,'Karaoke',' O'], [ 'Nagayoshi','Fairy', 15, 41,'"9/20"', '79 / 59/78', 154,'Baseball','B'], ['Nikaido Chizuru','Fairy', 21, 50,'"10/21"', '85 / 58/86', 165,'Check apparel magazines at the cafe',' B'], ['Roko','Fairy', 15, 42,'" 3/1 "', '78 / 57/77', 154,'Making things',' AB'], ['Maihama Ayumu','Fairy', 19, 49.9,'" 7/23 "', '86 / 60/88', 158,'Shopping',' A'], ['Mizuki Makabe','Fairy', 17, 43,'"1/23"', '73 / 54/77', 160 ,'Magic',' B'], ['Iori Minase',' F airy', 15, 40,'"5/5"', '77 / 54/79', 153,'Overseas travel, eating out',' AB'], ['Shizuka Mogami','Fairy', 14, 44,'"9/14"', '76 / 53/77', 162,'Tennis','A'], ['Momose Rio','Fairy', 23, 46,'"11/21"' , '84 / 57/84', 168,'Yoga Exercise',' A']]
Output result when using pprint
[['Ritsuko Akizuki','Fairy', 19, 43,'" 6/23 "', '85 / 57/85', 156,' Qualification, Volunteer',' A'], ['Chihaya Kisaragi','Fairy', 16, 41,'"2/25"', '72/55/78', 162,'Music Appreciation, Photography',' A'], ['Shiho Kitazawa',' Fairy', 14, 46,'"1/18"', '83 / 56/84', 161,'Find your favorite picture book',' A'], ['Takane Shijou','Fairy', 18, 49,'"1/21"', '90 / 62/92', 169,'Astronomical Observation, History',' B'], ['Julia',' Fairy', 16, 43,'" 9/26"', '79 / 54/80', 157,'None',' O'], ['Tsumugi Shiraishi','Fairy', 17, 45,'"5/29"', '82 / 56/83', 160,'Garden / Sweetness Tour',' AB'], ['Momoko Suou','Fairy', 11, 35,'"11/6"', '73 / 53/74', 140,'Cute Stickers','B'], ['Tomoka Tenkubashi','Fairy', 15, 41,'"11/11"', '80 / 56/79', 156,'To make fans happy',' AB'], ['Emi Tokoro','Fairy', 16, 47,'" 4/15"', '88 / 56/85', 159,'Karaoke',' O'], ['Subaru Nagayoshi','Fairy', 15, 41,'"9/20"', '79 / 59/78', 154,'Baseball','B'], ['Nikaido Chizuru','Fairy', 21, 50,'"10/21 "', '85 / 58/86', 165,'Check apparel magazine at cafe','B'], ['Loko',' Fairy', 15, 42,'"3/1"', '78 / 57/77', 154,'Making things',' AB'], ['Ayumu Maihama','Fairy', 19, 49.9,'" 7/23 "', '86 / 60/88', 158,'Shopping',' A'], ['Mizuki Makabe','Fairy', 17, 43,'"1/23"', '73 / 54/77', 160,'Magic','B'], ['Iori Minase','Fairy', 15, 40,'" 5/5"', '77 / 54/79', 153,'Overseas travel, eating out',' AB'], ['Shizuka Mogami',' Fairy', 14, 44,'" 9/14"', '76 / 53/77', 162,'Tennis',' A'], ['Momose Rio','Fairy', 23, 46,'"11/21"', '84 / 57/84', 168,'Yoga Exercise','A']]
Of course, all you have to do is output the data! In such a situation, print is sufficient, but this time the purpose is "I want to search" and "I want to easily see the list that matches the search content", so I will use pprint.
Read Excel file
Since the purpose of this time is to search the Excel file, it is necessary to read the original data.
#Specify the file path and load it. Of the file path"\"Is interpreted as an escape character"r"To avoid errors by prefixing with to prevent escaping.
wb = openpyxl.load_workbook(r"C:\Users\(Where the file is)\Idolmastermillionlive_data.xlsx")
#Specify the index number of the worksheet to be read. Numbers start from 0.
ws = wb.worksheets[0]
character_list = []
for row in ws["A2:I53"]:
values = []
for col in row:
values.append(col.value)
character_list.append(values)
The second line asks Openpyxl to read the file you want it to search for this time. The content of () is the path, but if you are not careful, you will get the error "'unicode escape' codec can't decode bytes in position 2-3: truncated \ UXXXXXXXX escape". I didn't understand the meaning of the error at all because the path was correct and the characters weren't flawed. This error occurs when the computer misunderstands the delimiter "" as an "escape character" when specifying the path. So I used the raw character to cancel this escape character. That's right, the "r" in front of the path. It's not a typo.
Oops, you wrote something in the for statement below that. It means to read the list of the specified range of cells, process it vertically and put it in an empty list called values.
To explain with an image
I want to search this range first, so I specified the cell to read here. In this case, I specified from No. 2 of A to cell 53 of I (because the top title is unnecessary). As mentioned in the reference site mentioned above, this specification is okay as long as there is no data loss. However, if the data is missing teeth, another process is required. But that's another opportunity.
So this time
We asked them to read vertically as a group horizontally.
And finally, the data of Values, which contains the data of each person as a list, is put into an empty variable called "character_list". ~~ FOO ^ ~! It feels good! ~~
Create the search part
Let's make the search part that everyone has been waiting for.
#Infinite loop execution
while True:
settargets = input("Which of "attribute", "name", "height", "3 sizes", "birthday", "age", "hobby", "blood type", and "weight" should be searched?:")
#Search starts based on the value entered in set targets
if settargets == "attribute":
attribute = input("Fairy,Princess,Enter the attribute with one of the Angels:")
#Extract the rows that match the conditions and attribute_Put in list
attribute_list = [d for d in character_list if d[1] == attribute]
#Result display
pprint(attribute_list)
#Confirm if you want to continue the search
cont = input("Do you want to continue the search?(Y/N):")
if cont == "Y" or cont == "y":
continue
elif cont == "N" or cont == "n":
break
else:
continue
#Perform the same process below
if settargets == "name":
name = input("Please enter your name:")
name_list = [d for d in character_list if d[0] == name]
pprint(name_list)
cont = input("Do you want to continue the search?(Y/N):")
if cont == "Y" or cont =="y":
continue
elif cont == "N" or cont == "n":
break
else:
continue
if settargets == "height":
hight = input("Please enter your height:")
hight_list = [d for d in character_list if d[6] == hight]
pprint(hight_list)
cont = input("Do you want to continue the search?(Y/N):")
if cont =="Y" or cont == "y":
continue
elif cont == "N" or cont == "n":
break
else:
continue
if settargets == "3 sizes":
threesize = input("3 Please enter the size:")
threesize_list = [d for d in character_list if d[5] == threesize]
pprint(threesize_list)
cont = input("Do you want to continue the search?(Y/N):")
if cont == "Y" or cont == "y":
continue
elif cont == "N" or cont == "n":
break
else:
continue
if settargets =="birthday":
birthday = input("Please enter your birth date:")
birthday_list = [d for d in character_list if d[4] == birthday]
pprint(birthday_list)
cont = input("Do you want to continue the search?(Y/N):")
if cont == "Y" or cont == "y":
continue
elif cont == "N" or cont == "n":
break
else:
continue
if settargets == "age":
age = input("Please enter your age:")
age_list = [d for d in character_list if d[2] == age]
pprint(age_list)
cont = input("Do you want to continue the search?(Y/N):")
if cont == "Y" or cont == "y":
continue
elif cont == "N" or cont =="n":
break
else:
continue
if settargets == "hobby":
hobby = input("Please enter your hobby:")
hobby_list = [d for d in character_list if d[7] == hobby]
pprint(hobby_list)
cont = input("Do you want to continue the search?(Y/N):")
if cont == "Y" or cont == "y":
continue
elif cont == "N" or cont == "n":
break
else:
continue
if settargets == "blood type":
bloodtype = input("Please enter your name:")
bloodtype_list = [d for d in character_list if d[8] == bloodtype]
pprint(bloodtype_list)
cont = input("Do you want to continue the search?(Y/N):")
if cont == "Y" or cont =="y":
continue
elif cont == "N" or cont == "n":
break
else:
continue
if settargets == "body weight":
weight = input("Please enter your weight:")
weight_list = [d for d in character_list if d[3] == weight]
pprint(weight_list)
cont = input("Do you want to continue the search?(Y/N):")
if cont == "Y" or cont == "y":
continue
elif cont == "N" or cont =="n":
break
else:
continue
else:
print('Please change the keyword and try again.(Example:Birthday December 29 →"12/29")')
break
It's been a long time, but once you write it, you only have to copy and paste as much as you need, so it's easy. This time I wanted to be able to search as many times as I wanted, so I decided to use an infinite loop.
First, set targets at the beginning asks the user "what data do you want to search for?"
Then, the search is performed based on the search conditions specified by the set targets.
For example, to explain in the first block, if you are asked to search by "attribute", ask "which attribute idol you want to see" and put the result in attribute.
After that, "The attribute (attribute entered by the user) is the same as the one in the first place of the data, and it is instructed to put it in the list as attribute_list.
The first of the data is
It's here. If you count it normally, it will be the second. Therefore, ask all the data to see "Does the second (first) of the data match the specified character?". By the way, the way to write a for statement in this list is called "list comprehension", but I thought that it could be written even if it was separated normally, but I got an error and proceeded. I didn't have it, so I had to make it with list comprehension. I remember that the error that occurred at that time was an error such as "Int type data cannot be decorated!". I tried various things, but for some reason I got this error and couldn't solve it.
The resulting "data that meets the conditions" is displayed. After that, I asked "Do you want to continue searching after this?" It's as simple as typing Y to continue, typing N to interrupt, and even if another character is entered, but it was very convenient.
result
It's spicy when you make it, but when you start using it, you can save time and feel E! I want a site that can do profile search even officially ... but there may be () Well, I'm already sleepy, so around here. Baicha.
Recommended Posts