firstly for taking out the names

import pandas as pd
import re
from collections import Counter

# Read the data from the file
data_tash = pd.read_csv("path/to/data_tash.csv")

# Convert text to lowercase
data_tash['text_all'] = data_tash['text_all'].str.lower()

# Split text into words considering space, comma, and line breaks as delimiters
words_text_all_withlinebreaks = re.split("[ ,\n]+", " ".join(data_tash['text_all']))

# Remove special characters from words
pattern = r"^[:;!@#•·»«~}+*{?§%&>()-|=_\"°≈÷×—'“”️]+"
words_text_all_withlinebreaks_clean = [re.sub(pattern, "", word) for word in words_text_all_withlinebreaks]

# Count the occurrences of each word
words_text_count_all_withlinebreaks = Counter(words_text_all_withlinebreaks_clean)

# Convert the Counter object to a DataFrame
words_text_count_all_withlinebreaks = pd.DataFrame.from_dict(words_text_count_all_withlinebreaks, orient='index').reset_index()
words_text_count_all_withlinebreaks.columns = ['word', 'count']

# Sort the DataFrame by word count in descending order
words_text_count_all_withlinebreaks = words_text_count_all_withlinebreaks.sort_values(by='count', ascending=False)

# Calculate the midpoint of the dataset
midpoint_withlinebreaks = len(words_text_count_all_withlinebreaks) // 2

# Split the data into two parts
data_part1_midpoint_withlinebreaks = words_text_count_all_withlinebreaks.iloc[:midpoint_withlinebreaks]
data_part2_midpoint_withlinebreaks = words_text_count_all_withlinebreaks.iloc[midpoint_withlinebreaks:]

# Save each part as a CSV file
data_part1_midpoint_withlinebreaks.to_csv("data_part1_withlinebreaks.csv", index=False)
data_part2_midpoint_withlinebreaks.to_csv("data_part2_withlinebreaks.csv", index=False)





splitting one by one tokens

We import the necessary libraries: pandas for data manipulation, re for regular expressions, and Counter for counting occurrences of elements.
We read the data from the CSV file into a DataFrame.
We convert all text in the 'text_all' column to lowercase.
We split the text into words, considering space, comma, and line breaks as delimiters.
We remove special characters from the words using regular expressions.
We count the occurrences of each word using the Counter object.
We convert the Counter object to a DataFrame and sort it by word count in descending order.
We calculate the midpoint of the dataset to split it into two parts.
We split the data into two parts based on the midpoint.
We save each part as a CSV file.

Substring Concat

row number rank dense rank

lag lead first_value last_value

ntile() to divide how quantile you want to divide

you can use window name as ( window function)

then use lag lead rank row number over(window name)

unbounded preceding
n, precending, n
current row
n, following, n
Unbounded following

firstly find out what kinda columns we need

then connect

.join()

Certainly! Let's break down the Python code step by step:

1. Read the Data: First, you read the data from an Excel file into a pandas DataFrame named dk and another DataFrame named filter_1.

import pandas as pd

# Read data from Excel files
dk = pd.read_excel("./data/ne3.xlsx")
filter_1 = pd.read_excel("./data/ne2.xlsx", sheet_name="data")

2. Data Preprocessing:
- Convert all values to strings in filter_1.
- Create a new column home_sets containing sets of values from columns 2 onwards in filter_1.
- Remove 'nan' from each set.

# Convert all values to strings
filter_1 = filter_1.applymap(str)

# Create a new column containing sets of values from other columns
filter_1['home_sets'] = filter_1.iloc[:, 2:].apply(lambda x: set(x), axis=1)

# Remove 'nan' from each set
for i in filter_1.index:
    filter_1['home_sets'][i].discard('nan')

3. Matching Descriptions with Models:
- Create a list of tuples (word_to_model) containing unique combinations of model names and their corresponding normalized forms.
- Process each description in the dk_need DataFrame and find matches with the models from word_to_model using string matching.

import re

# Function to normalize text
def re_text(x):
    text = re.sub(r'[^A-Za-z0-9А-Яа-я ]+', '', x).replace("  ", "").lower()
    return text

# Apply normalization function to 'combine2' column in dk_need DataFrame
dk_need['combine2'] = dk_need['combine2'].apply(re_text)

# Create a list of tuples containing unique combinations of model names and their normalized forms
word_to_model = []
for _, row in filter_1.iterrows():
    for home in row['home_sets']:
        arent_ = re_text(home).replace(' ', '')
        word_to_model.append((row['name'], arent_))

# Function to process descriptions and find matches with models
def process_descriptions(descriptions, word_to_model):
    matched_models = []
    for _, row_ in descriptions.iterrows():
        desc = row_['combine2']
        desc_ = desc.replace(' ', '')
        match = next(({'combine2': desc, 'arentir': name}
                      for name, arent_ in word_to_model
                      if (arent_ in desc) or (name in desc) or (arent_ in desc_) or (name in desc_)),
                     {'combine2': desc,  'arentir': 'Not matched'})
        matched_models.append(match)
    return matched_models

# Process each description in parallel using ThreadPoolExecutor
import concurrent.futures

with concurrent.futures.ThreadPoolExecutor() as executor:
    # Split descriptions into chunks
    chunks = [dk_need.iloc[i:i + 100] for i in range(0, len(dk_need), 100)]
    # Process each chunk in parallel
    results = executor.map(lambda chunk: process_descriptions(chunk, word_to_model), chunks)

# Combine results
all_matched_models = [item for sublist in results for item in sublist]

# Convert the results into a DataFrame
df_models = pd.DataFrame(all_matched_models)

4. Explanation:
- The code first normalizes the text in the combine2 column of the dk_need DataFrame to remove special characters, convert to lowercase, and remove extra spaces.
- It then creates a list of tuples (word_to_model) containing unique combinations of model names and their corresponding normalized forms.
- Using multithreading with ThreadPoolExecutor, it processes each chunk of descriptions in parallel and finds matches with models based on the normalized forms.
- Finally, it combines all the results into a DataFrame (df_models) containing the matched descriptions and model names.

This Python code performs similar functionality to the SQL code provided earlier, but it's implemented using pandas and Python's concurrent programming features for parallel processing.

Iterrows
Re.sub
Append