# Needs Part

## Info
ip address: 123.56.247.194

rsync -avz --exclude 'user_data' --exclude 'test_data' --progress ./ root@123.56.247.194:/root/university_information/

## Developer thinking
看那么多东西，脑袋都看懵了，觉得最靠谱的一个方案是：

当用户对某学校感兴趣时，直接把所有和该学校有关的line都发过去，当然lines最顶上要有一个head line，每个excel的信息之间用空白分割符，分割

对某专业的信息感兴趣同理



所以全局就只有两个搜索功能：搜学校、搜专业




至于分数线，用户可以自己看对应的内容。(我们当然可以在后期根据用户的分数推荐学校专业，但我对内容不熟悉，所以暂时不做)




总结一下:

让用户先搜学校，按照学校评论。比如有24小时开放的图书馆就是一个加分项。然后进学校看专业，发现喜欢的专业后，如果发现分数不够，再找相同专业的其它学校。

学生如果通过社工评价库，找到了心仪的学校和专业，就已经成功了一半，接下来只需要百度一下该学校和该专业的历史录取分数线，就可以了，根本不需要我们的网站做所有的事情。

但你excel里应该没有这些数据，我们做不了。真实的社工数据库就和“大公司负面评价”一样，基本上属于全网404被删除的状态。根本没地方可以获取。



我现在只能先全局分析处理下你给的excel，看能不能转为纯文本





今天用github那个ai搞了一整天，发现它不太行，搞不出来有用的东西

点一下动一下

写出来的代码轻轻松松32GB内存爆炸 + 15GB以上的文件占用，导致电脑卡死




测试完毕，所有线上AI都搞不定这个东西

然后只能全局关键词搜索，然后让用户自己看信息，最多带一个下载功能，让用户下载excel或者html




所以最简单的办法是：全局搜索

精确到每一行

然后就没有然后了，我不会了



我觉得最终，不太需要什么全能助手，也许就是简简单单的一个分数搜索就可以了，用分数搜出来可以上哪些学校

然后让用户自己去查学校，通过我早上搞的那个东西

> 不过这个分数搜索也可以用全局搜索做。。。就是效率比较低


换句话讲，虽然有一堆excel, 我们只需要一个cell，那就是录取平均分cell (或者最低分)，然后根据这个分数去推荐学校，然后就没有然后了。


Check the header to see if they full match, if so, collect
year university subject lowest_score 
has(年)|时间|招生年份   院校名称|大学名称|学校   专业名称|专业    最低分


we will use path_name location to add global_location to location.json

So in the end we will have multiple location.json



We will check the year to see if we loss data or not




搞个 “只搜学校” 的过滤器, 顺便搞个score range() filter


## Product Asker thinking

说白了就是仿造一下 掌上高考 之类平台

考生输入生源地，文理科，分数，位次，然后系统匹配相关学校

> 看起来没有那么简单。把xlsx转为json只是第一步。接着还要统一数据。设计数据结构。到底是以学校为中心，还是以专业为中心，还是以地区为中心，这个还有待研究

> 不过单纯从玩数据的角度看，可以先做个网页表格查询器，用户想搜什么搜什么，我这边就不做过多的数据处理了，直接显示多个表格



需要设置一个区间 例如10分左右
例如 596分  就搜586-596之间  





# Tech Part

I have a small project that have a lot of .xlsx files.

For this time we do not have to use local dev tools, we can use pip. And the python version is set to 3.10.4

My goal is to convert those xlsx into json like stuff, you know, a table can be a list of list, and the first element is usually the header.

The hard part is for a xlsx file, it not only has one table, it has multiple sheet. We need to parse them all. So in the end, we will have multiple list.

Let me define a function for you:

```
def parse_xlsx_file(xlsx_file_path):
	#... some logic to do the parse
	#you need to help me implement this function
	#use try and catch, if error happens, print error and return None
	return {
		"sheet_name_1": [],
		"sheet_name_2": [],
	}
```

A sheet table can be a list of list, such as:

[
	["some times a line will have different number of elements, for example, here it only has one element"]
	[year, university_name, scores],
	[1999, NanJingDaXue, 600],
	[1998, BeiJingDaXue, 500],
	...
]


___


For now, as you can see, I have main_server.py, which serves the 'pure_html' folder and two simple api "/get_json_summary_dict" and "/get_json_file".

You can have a look at './pure_html/index.html' and './pure_html/view_xlsx.html', where I created two page who can search/filter out xlsx files, and if you click one of the xlsx link, it will lead you to view_xlsx.html page where you can have a look at that xlsx table. in view_xlsx.html page, you can also do a search/filter to see your target lines (a table line is multiple cells).

You can have a close look at the search algorithm, it is a simple full keyword match method. We split string by space to get keywords.

Now, I have a hard problem for you: you need to create a new "super_search.html" under "./pure_html" folder.

In that super_search.html, you some how create a search container at the top, where it has filters like province_location, university, subject, year, lowest_score, average_score, how_many_peple_get_in

When user click search button, they could see multiple tables, those tables are come from json data you get from server.

This is a relative hard problem since the python server only provides you two simple API.

You have to have a look at "./json_data/summary.txt", because that is the first data you would get, it is splited by ":", where the key is the full page of xlsx file, value is the parsed json data that represents the original xlsx file.

Then you got "10GB" big "./json_data/data/*.json", it is the real table data followed a structure like:
```
{
		"sheet_name_1": [],
		"sheet_name_2": [],
}
```

Where each list is:
```
[
	["some times a line will have different number of elements, for example, here it only has one element"]
	[year, university_name, scores],
	[1999, NanJingDaXue, 600],
	[1998, BeiJingDaXue, 500],
	...
]
```

The most difficult part is the size of them all. They are so big, we simply can't load them all in webpage, then do the process.

But for somehow if we want to make a exact filter, we need to read all json data first, because for some json data, it also contains different university data...

I'm all most running out of way. If you can can create a python file called "2.remix_the_json_data.py" and do some process for "./json_data" and put the remixed data into a new folder called "./remixed_data", I think is also accepble.

Anyway, all data structure in the end is to fullfill the needs at the very top. I simply don't know how to make those things since I am a simple guy who love simple solution. So far I just finished a simple solution, the others, is rely on you, buddy!




___


I did a lot of test. the test almost broken my disk and memory.

Your method takes more than 32GB memory, and the remixed json file is beyound 15GB

I think you should rethink about the experiment, experiment should use small dataset.

So instead of using all data from json_data folder, just use 100 per one to do the remix and test

You can make a global variable to switch experiment and product, by default it should be experiment

Now take your chance and start to modify '2.remix_the_json_data' and '2.1test_remixed_data.py' and './pure_html/super_search.html'


___


I think I need to fire you for this project.

Because all of your code simply don't work.

Now do me a favor, recode the super_search.html, it will still has the search box, province, and year, but the province and year selections should be list pre_coded in the javascript side, no server needed

And we do not need score range anymore

And in the end, the super_search page will only use one api "/get_remixed_json_file_by_search_keywords", it only takes {keywords: ["word1", "word2"]} as payload

You do not need to care about the main_server.py, since I will do that job

After you get data, it is simply a list of "{'sheet_one': [], sheet_two": []}", rendering method is like a list of 'view_xlsx.html'

Just code as much as you can, I will do the futher development after that.

You are fired in this project. (We will do something easier in the future, this is just too complex for you)

___


Well, in the end, all of your struggle and code made for nothing, nothing works.

How about we use a new way to solve this problem? If user search a [keyword1, keyword2, keyword3], we simply look through all json data, and return each table and lines matchs?

This is the most stright forward and stable solution.

I could image the data structure should get changed, the json object should be converted into txt data, where each table was split by "____yingshaoxo_is_best____"

And each line represent a line in table, each cell will be splited by ",", just similar to csv

Why I define this data structure? because this structure could allow us to do the search one by one until the end of the txt file

No need to load the whole txt file into memory because it is bigger than 32GB, it will blow your computer away... Broken your computer...
