1

pattern = r"[（(]\d{4}[)）][^号]*?(?:初|终|再)[^号]*?号"

1

exclusions = ['X', 'x', 'Ｘ', '执', '不动产', '-', '第']

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149

import pandas as pd
from collections import OrderedDict
import json
import re
import os

# Read the data
data = pd.read_csv("F:/桌面/裁判文书数据库/test/test.txt", delimiter='\t')

# Check the first few rows of data
print(data.head(3))

# Assume the CourtInfo column contains JSON strings
json_data = data['CourtInfo']

# Parse the JSON data
def parse_json(json_str):
    try:
        return json.loads(json_str)
    except json.JSONDecodeError:
        return None

# Apply the parsing function
parsed_prac_c = json_data.apply(parse_json)

# Convert the JSON dictionary to a DataFrame and handle missing values
json_df = pd.json_normalize(parsed_prac_c.dropna(how='all'))

# Check the converted DataFrame
print(json_df.head(20))

# Regular expression pattern
pattern = r"[（(]\d{4}[）)]\s*.*?号"

# Use findall to extract all matches
matches = json_df['s23'].apply(lambda x: re.findall(pattern, x) if isinstance(x, str) else [])

# Check the converted DataFrame
print(matches.head(3))
print(matches)

# Create a new DataFrame to save the updated 's23' column
matches_df = pd.DataFrame({
    's23': matches
})

# Check the converted DataFrame
print(matches_df.head(10))
# print(matches_df)

# Select the columns to concatenate
selected_data_columns = []
selected_json_df_columns = []

# Concatenate the selected columns
combined_df = pd.concat([data[selected_data_columns], json_df[selected_json_df_columns],matches_df], axis=1)

def clean_text(text):
    if isinstance(text, list):
        text = ', '.join(text)  # Convert the list to a string, elements separated by comma and space
    if isinstance(text, str):
        if text == "[]":
            return ''
        cleaned_text = text.replace('[', '').replace(']', '').strip()
        return cleaned_text
    return ''

# Split and generate column names
def split_and_rename(content):
    content = clean_text(content)
    split_content = content.split(', ')
    result = {f'm{i+1}': split_content[i].strip() for i in range(len(split_content)) if split_content[i].strip()}
    return result

# Apply the function
expanded_df = combined_df['s23'].apply(split_and_rename).apply(pd.Series)

# Check the result
# print("Expanded DataFrame:")

# Function to extract the year
def extract_year(text):
    match = re.search(r'[（(](\d{4})[）)]', text)
    return int(match.group(1)) if match else float('inf')

# Cleaning and sorting function
def clean_and_sort(row):
    # Filter out non-string values
    filtered = [item for item in row if isinstance(item, str) and not any(keyword in item for keyword in exclusions)]
    # Remove duplicates while preserving order
    unique_items = list(OrderedDict.fromkeys(filtered))
    # Sort by year
    sorted_items = sorted(unique_items, key=lambda x: extract_year(x))
    return sorted_items

# List of keywords to exclude
exclusions = ['X', 'x', 'Ｘ', '执', '不动产', '-', '第']

# Select all m* columns
m_columns = [col for col in expanded_df.columns if col.startswith('m')]

# Apply the cleaning and sorting function to each row
def process_row(row):
    # Get the content of the m* columns
    row_values = [row[col] for col in m_columns]
    # Flatten the list
    flattened = [item for sublist in row_values for item in (sublist if isinstance(sublist, list) else [sublist])]
    # Process and sort
    sorted_items = clean_and_sort(flattened)
    return sorted_items

# Apply to each row of the DataFrame
sorted_results = expanded_df.apply(process_row, axis=1)

# Create a new DataFrame
max_length = max(sorted_results.apply(len))
sorted_df = pd.DataFrame(sorted_results.tolist(), columns=[f'm_sorted_{i+1}' for i in range(max_length)])

# Print the result
print(sorted_df.head(10))

# Concatenate to the original data
combined_df2 = pd.concat([combined_df, sorted_df], axis=1)

# Check the result
print(combined_df.head(10))
# Check whether the 'm_sorted_5' column exists, and drop non-empty rows
if 'm_sorted_5' in combined_df2.columns:
    combined_df2_cleaned = combined_df2[combined_df2['m_sorted_5'].isna() | (combined_df2['m_sorted_5'] == '')]
else:
    # If the column doesn't exist, keep the original DataFrame
    combined_df2_cleaned = combined_df2.copy()

# Print the result
print(combined_df2_cleaned.head(10))

# Get the names of all m_sorted_* columns
m_sorted_columns = [col for col in combined_df2_cleaned.columns if col.startswith('m_sorted_')]

# Filter out columns with index >= 5
columns_to_drop = [col for col in m_sorted_columns if int(col.split('_')[2]) >= 5]

# Drop the specified columns
combined_df2_cleaned = combined_df2_cleaned.drop(columns=columns_to_drop, errors='ignore')

# Print the result to check the cleaned DataFrame
print(combined_df2_cleaned.head())

combined_df2.to_csv('F:/桌面/combined_df.txt', sep='\t', index=False, quoting=1, quotechar='"')

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148

import pandas as pd
from collections import OrderedDict
import json
import re
import os

# Define the input and output folder paths
# Define the input and output folder paths
input_folder_path = "C:/Users/PC/Desktop/hzp_project/二审和一审/数据/"
output_folder_path = "C:/Users/PC/Desktop/hzp_project/二审和一审/清洗结果/"

# Create the output folder if it doesn't exist
if not os.path.exists(output_folder_path):
    os.makedirs(output_folder_path)

# Iterate over all .txt files in the input folder
for filename in os.listdir(input_folder_path):
    if filename.endswith('.txt'):
        input_file_path = os.path.join(input_folder_path, filename)
        output_file_path = os.path.join(output_folder_path, filename)

        # Read the data
        data = pd.read_csv(input_file_path, delimiter='\t')

        # Print the first 3 rows to check
        print(f"Processing file: {filename}")
        print(data.head(3))

        # Parse the JSON field
        json_data = data['CourtInfo']

        def parse_json(json_str):
            try:
                return json.loads(json_str)
            except json.JSONDecodeError:
                return None

        parsed_prac_c = json_data.apply(parse_json)

        # Convert the parsed JSON to a DataFrame
        json_df = pd.json_normalize(parsed_prac_c.dropna(how='all'))

        # Print the first 20 rows to check
        print(json_df.head(20))

        # Define the matching pattern
        pattern = r"[（(]\d{4}[）)]\s*.*?号"

        # Find the matches
        matches = json_df['s23'].apply(lambda x: re.findall(pattern, x) if isinstance(x, str) else [])

        # Print the first 3 matches to check
        print(matches.head(3))
        print(matches)

        # Convert the matches to a DataFrame
        matches_df = pd.DataFrame({'s23': matches})

        # Print the first 10 rows of the matches DataFrame to check
        print(matches_df.head(10))

        # Select the data columns to keep
        selected_data_columns = []
        selected_json_df_columns = []

        # Concatenate the original data, parsed JSON data, and match results
        combined_df = pd.concat([data[selected_data_columns], json_df[selected_json_df_columns], matches_df], axis=1)

        # Text cleaning function
        def clean_text(text):
            if isinstance(text, list):
                text = ', '.join(text)
            if isinstance(text, str):
                if text == "[]":
                    return ''
                cleaned_text = text.replace('[', '').replace(']', '').strip()
                return cleaned_text
            return ''

        # Split and rename function
        def split_and_rename(content):
            content = clean_text(content)
            split_content = content.split(', ')
            result = {f'm{i+1}': split_content[i].strip() for i in range(len(split_content)) if split_content[i].strip()}
            return result

        # Expand the match results
        expanded_df = combined_df['s23'].apply(split_and_rename).apply(pd.Series)

        # Function to extract the year
        def extract_year(text):
            match = re.search(r'[（(](\d{4})[）)]', text)
            return int(match.group(1)) if match else float('inf')

        # Cleaning and sorting function
        def clean_and_sort(row):
            filtered = [item for item in row if isinstance(item, str) and not any(keyword in item for keyword in exclusions)]
            unique_items = list(OrderedDict.fromkeys(filtered))
            sorted_items = sorted(unique_items, key=lambda x: extract_year(x))
            return sorted_items

        # List of exclusion keywords
        exclusions = ['X', 'x', 'Ｘ', '执', '不动产', '-', '第']

        # Get the names of all columns starting with m
        m_columns = [col for col in expanded_df.columns if col.startswith('m')]

        # Process each row
        def process_row(row):
            row_values = [row[col] for col in m_columns]
            flattened = [item for sublist in row_values for item in (sublist if isinstance(sublist, list) else [sublist])]
            sorted_items = clean_and_sort(flattened)
            return sorted_items

        sorted_results = expanded_df.apply(process_row, axis=1)

        # Compute the maximum length and create a new sorted DataFrame
        max_length = max(sorted_results.apply(len))
        sorted_df = pd.DataFrame(sorted_results.tolist(), columns=[f'm_sorted_{i+1}' for i in range(max_length)])

        # Concatenate the data again
        combined_df2 = pd.concat([combined_df, sorted_df], axis=1)

        # Filter condition
        if 'm_sorted_5' in combined_df2.columns:
            combined_df2_cleaned = combined_df2[combined_df2['m_sorted_5'].isna() | (combined_df2['m_sorted_5'] == '')]
        else:
            combined_df2_cleaned = combined_df2.copy()

        # Print the first 10 rows of cleaned data to check
        print(combined_df2_cleaned.head(10))

        # Get the names of all columns starting with m_sorted
        m_sorted_columns = [col for col in combined_df2_cleaned.columns if col.startswith('m_sorted_')]

        # Column names to drop
        columns_to_drop = [col for col in m_sorted_columns if int(col.split('_')[2]) >= 5]

        # Drop the unneeded columns
        combined_df2_cleaned = combined_df2_cleaned.drop(columns=columns_to_drop, errors='ignore')

        # Print the final first few rows to check
        print(combined_df2_cleaned.head())

        # Save to file
        combined_df2_cleaned.to_csv(output_file_path, sep='\t', index=False, quoting=1, quotechar='"')

print("Processing complete.")

1
2
3
4

for clause in data:
    fgmc = clause.get('fgmc', '')
    tkx = clause.get('tkx', '')
    match = re.search(r'(第.*?条)', tkx)

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143

import pandas as pd
import json
import re
import os
import glob
import logging

# Configure logging
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

# Explicitly specify the input and output folder paths
input_folder_path = "C:/Users/PC/Desktop/hzp_project/二审和一审/数据/"
output_folder_path = "C:/Users/PC/Desktop/hzp_project/二审和一审/清洗结果/"
log_folder_path = "C:/Users/PC/Desktop/hzp_project/二审和一审/日志/"

# Make sure the output and log folders exist
os.makedirs(output_folder_path, exist_ok=True)
os.makedirs(log_folder_path, exist_ok=True)

# Parse JSON data
def parse_json(json_str):
    try:
        if isinstance(json_str, str):
            return json.loads(json_str)
        elif isinstance(json_str, list):
            return json_str
        else:
            logging.warning(f"未知数据类型: {type(json_str)}")
            return None
    except json.JSONDecodeError:
        logging.warning(f"JSON 解析失败: {json_str}")
        return None

# Count Chinese characters
def count_chinese_chars(text):
    if pd.isnull(text):
        return 0
    return len(re.findall(r'[\u4e00-\u9fff\u0030-\u0039\u3000-\u303f\uff00-\uffef]', str(text)))

# Extract and format clauses
def extract_clauses(s47_data):
    formatted_clauses = []
    for item in s47_data.dropna():
        data = parse_json(item)
        if data is not None:
            clauses = set()  # Use a set to deduplicate
            for clause in data:
                fgmc = clause.get('fgmc', '')
                tkx = clause.get('tkx', '')
                match = re.search(r'(第.*?条)', tkx)
                if match:
                    clauses.add(f"{fgmc}{match.group(1)}")
            formatted_clauses.append(list(clauses))  # Save the clauses extracted per row
        else:
            formatted_clauses.append([])  # If parsing fails, add an empty list
    return formatted_clauses

# Process a single file
def process_file(input_file, output_folder, log_folder, total_rows):
    # Initialize an empty list to store error info
    error_logs = []

    def bad_line_handler(bad_line):
        error_logs.append((input_file, bad_line.line_num, bad_line.line))

    # Read the data
    try:
        data = pd.read_csv(input_file, delimiter='\t', on_bad_lines=bad_line_handler, engine='python')
        logging.info(f"Read {len(data)} rows from file: {input_file}")
    except Exception as e:
        logging.error(f"Error reading file: {input_file}")
        logging.error(f"Error message: {e}")
        return

    # Filter out rows where the CourtInfo column starts with "DocInfoVo" or cannot be parsed as JSON
    data = data[data['CourtInfo'].apply(lambda x: x.startswith('{"s1":') if pd.notnull(x) else False)]
    data = data[data['CourtInfo'].apply(lambda x: isinstance(parse_json(x), dict))]

    logging.info(f"Filtered out invalid rows from file: {input_file}")

    # Extract the JSON data
    json_data = data['CourtInfo']

    # Apply the parsing function
    parsed_prac_c = json_data.apply(parse_json)
    logging.info(f"Parsed JSON data from file: {input_file}")

    # Convert the JSON dictionary to a DataFrame and handle missing values
    json_df = pd.json_normalize(parsed_prac_c.dropna(how='all'))
    logging.info(f"Normalized JSON data from file: {input_file}")

    # Select specific columns
    selected_columns = json_df[['s7', 's23', 's25', 's26', 's27', 's47']].copy()  # Use .copy() to create a copy

    # Count occurrences of the character "条" in the s26 column
    selected_columns['count_tiao'] = selected_columns['s26'].apply(lambda x: x.count('条') if pd.notnull(x) else 0)
    logging.info(f"Calculated 'count_tiao' from file: {input_file}")

    # Compute the total character count of the s23, s25, s26, s27 columns
    selected_columns['total_chars'] = selected_columns[['s23', 's25', 's26', 's27']].apply(
        lambda row: sum(count_chinese_chars(x) for x in row), axis=1
    )
    logging.info(f"Calculated 'total_chars' from file: {input_file}")

    # Extract and format clauses
    unique_clauses = extract_clauses(selected_columns['s47'])
    selected_columns['unique_clauses'] = unique_clauses  # Use .loc for safe assignment
    selected_columns['clause_count'] = [len(clauses) for clauses in unique_clauses]  # Number of clauses per row
    logging.info(f"Extracted and formatted clauses from file: {input_file}")

    # Generate the output file name
    base_name = os.path.basename(input_file)
    output_file = os.path.join(output_folder, f"cleaned_{base_name}")

    # Save the result as a txt file
    selected_columns[['s7', 'count_tiao', 'total_chars', 'unique_clauses', 'clause_count']].to_csv(output_file, sep='\t', index=False, quoting=1, quotechar='"')
    logging.info(f"Processed and saved to: {output_file}")

    # Report progress
    processed_rows = len(data)
    logging.info(f"已完成处理文件: {base_name}, 条目数: {processed_rows}/{total_rows}")

    # Save the error log
    if error_logs:
        log_file = os.path.join(log_folder, f"error_log_{base_name}.txt")
        with open(log_file, 'w', encoding='utf-8') as f:
            for log in error_logs:
                f.write(f"File: {log[0]}, Line: {log[1]}, Content: {log[2]}\n")
        logging.info(f"Error logs saved to: {log_file}")

# Iterate over all files in the input folder
all_files = glob.glob(os.path.join(input_folder_path, "*.txt"))
total_files = len(all_files)
processed_files = 0

if not all_files:
    logging.warning("No files found in the input folder.")
else:
    for input_file in all_files:
        processed_files += 1
        # Report the progress of the current file
        logging.info(f"正在处理文件: {input_file} ({processed_files}/{total_files})")
        process_file(input_file, output_folder_path, log_folder_path, total_files)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

import os
import shutil

# Define the folder paths
source_folder = r'F:/桌面/测试/清洗结果'
data_folder = r'F:/桌面/测试/数据'
cleaned_folder = r'F:/桌面/测试/完成提取'

# Get all .txt file names in the source folder (without paths), remove the "cleaned_" prefix, strip all extra spaces, and convert to lowercase
def standardize_filename(filename):
    return ''.join(filename.split()).lower()

# Get the file names in the source folder
source_files = [f for f in os.listdir(source_folder) if f.startswith('cleaned_') and f.endswith('.txt')]
standardized_source_files = {standardize_filename(f[8:]): f for f in source_files}

# Get the file names in the data folder
data_files = [f for f in os.listdir(data_folder) if f.endswith('.txt')]
standardized_data_files = set(standardize_filename(f) for f in data_files)

# Find files in the data folder that also exist in the source folder
common_files = standardized_source_files.keys() & standardized_data_files

# Move the matching files to the cleaned folder
for filename in common_files:
    original_filename = standardized_source_files[filename]
    source_path = os.path.join(data_folder, original_filename.replace('cleaned_', ''))
    destination_path = os.path.join(cleaned_folder, original_filename.replace('cleaned_', ''))

    # Make sure the destination folder exists
    os.makedirs(os.path.dirname(destination_path), exist_ok=True)

    shutil.move(source_path, destination_path)
    print(f"Moved: {source_path} -> {destination_path}")

print("Process completed.")

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141

import pandas as pd
from datetime import datetime


def parse_date(year_str, month_str):
    """将年份、月份字符串转换为日期对象，若转换失败返回 None"""    try:
        year = int(year_str)
        month = int(month_str) if month_str and month_str.strip() != "" else 1
        return datetime(year, month, 1)
    except Exception:
        return None


def reorder_row(row):
    # List of extra fields to move together (associated variables other than m_sorted_i)
    extra_fields = ['court', 'court_name', 'jud_year', 'jud_month', 'win', 'judge_am', 'judge_fin']
    entries = []

    # Iterate over the original 5 groups of data, constructing each group's entry (only when both m*_judge_am and m*_jud_year have values is the group considered valid)
    for i in range(1, 6):
        judge_am = row.get(f"m{i}_judge_am")
        jud_year = row.get(f"m{i}_jud_year")
        if pd.isna(judge_am) or pd.isna(jud_year) or judge_am == "" or jud_year == "":
            continue
        case_str = row.get(f"m_sorted_{i}")
        if not case_str or pd.isna(case_str):
            continue
        jud_month = row.get(f"m{i}_jud_month")
        dt = parse_date(jud_year, jud_month)

        # Judge the case-number type
        if "初" in case_str:
            typ = "chu"
        elif "终" in case_str:
            typ = "zhong"
        elif "再" in case_str:
            typ = "zai"
        else:
            typ = "other"

        # Collect other related fields
        fields_data = {}
        for field in extra_fields:
            col_name = f"m{i}_{field}"
            fields_data[field] = row.get(col_name, "")

        entry = {
            "orig_index": i,  # Original group number
            "case": case_str,  # Case-number string
            "date": dt,  # Converted date (may be None)
            "type": typ,  # Case-number type: chu / zhong / zai / other
            "fields": fields_data
        }
        entries.append(entry)

    # If there are no valid entries, return all new groups as empty
    if not entries:
        return pd.Series({f"m_sorted_{i}": "" for i in range(1, 6)})

    # Locate "初": take the earliest date among all entries containing "初"
    chu_entries = [e for e in entries if e["type"] == "chu" and e["date"] is not None]
    initial_entry = min(chu_entries, key=lambda e: e["date"]) if chu_entries else None

    # Locate "终": take the largest date among all entries containing "终"
    zhong_entries = [e for e in entries if e["type"] == "zhong" and e["date"] is not None]
    terminal_entry = max(zhong_entries, key=lambda e: e["date"]) if zhong_entries else None

    # Get all "再" entries
    zai_entries = [e for e in entries if e["type"] == "zai" and e["date"] is not None]

    # Initialize the dictionary of new sorting positions, representing the entries finally placed in m_sorted_1 to m_sorted_5
    new_positions = {1: None, 2: None, 3: None, 4: None, 5: None}

    # m_sorted_1 always holds the "初" case
    if initial_entry:
        new_positions[1] = initial_entry

    if terminal_entry:
        # If a final case exists, m_sorted_3 is fixed to hold the final case
        new_positions[3] = terminal_entry

        # Group: "再" before the final case
        re_before = [e for e in zai_entries if e["date"] < terminal_entry["date"]]
        # Group: "再" after the final case
        re_after = [e for e in zai_entries if e["date"] > terminal_entry["date"]]

        # Take the earliest "再" with a date before the final case into m_sorted_2        if re_before:
            new_positions[2] = min(re_before, key=lambda e: e["date"])

        # Take the latest "再" with a date after the final case into m_sorted_4        if re_after:
            new_positions[4] = max(re_after, key=lambda e: e["date"])
    else:
        # If no final case exists, i.e. only "初" and "再"
        zai_sorted = sorted(zai_entries, key=lambda e: e["date"])
        if len(zai_sorted) == 1:
            new_positions[2] = zai_sorted[0]
        elif len(zai_sorted) >= 2:
            # Earliest into m_sorted_2, latest into m_sorted_4            new_positions[2] = zai_sorted[0]
            new_positions[4] = zai_sorted[-1]

    # Fill the remaining unassigned entries (including type other or unselected) into remaining gaps (e.g. m_sorted_5) after sorting by date
    assigned_indices = {e["orig_index"] for e in new_positions.values() if e is not None}
    remaining = [e for e in entries if e["orig_index"] not in assigned_indices]
    remaining_sorted = sorted(remaining, key=lambda e: e["date"] if e["date"] is not None else datetime.max)
    for pos in [2, 5]:
        if new_positions[pos] is None and remaining_sorted:
            new_positions[pos] = remaining_sorted.pop(0)

    # Construct the returned Series: write m_sorted_i and the associated fields by new group number 1-5
    result = {}
    for new_group in range(1, 6):
        if new_positions[new_group]:
            entry = new_positions[new_group]
            result[f"m_sorted_{new_group}"] = entry["case"]
            for field in extra_fields:
                result[f"m{new_group}_{field}"] = entry["fields"].get(field, "")
        else:
            result[f"m_sorted_{new_group}"] = ""
            for field in extra_fields:
                result[f"m{new_group}_{field}"] = ""

    return pd.Series(result)


# Read the data (note the raw string in the path to avoid backslash-escaping problems)
df = pd.read_csv(r"C:\Users\PC\Desktop\案号排序.txt", sep=",", dtype=str)

# Call reorder_row on each row to get the re-sorted m* series data
df_new = df.apply(reorder_row, axis=1)

# Update the re-sorted variables back into the original DataFrame (including m_sorted_1 to m_sorted_5 and each group's associated fields)
for col in [f"m_sorted_{i}" for i in range(1, 6)]:
    df[col] = df_new[col]

for new_group in range(1, 6):
    for field in ['court', 'court_name', 'jud_year', 'jud_month', 'win', 'judge_am', 'judge_fin']:
        col_name = f"m{new_group}_{field}"
        df[col_name] = df_new[col_name]

# Save the result to a new file
df.to_csv(r"C:\Users\PC\Desktop\结果_案号排序.txt", index=False)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

2018（）皖02民终1098号
2016湘06民终2392号
（（2017）豫0481民再10号
一（2017）粤0605民初10499号
(2015)芙民初字第7479、7601号
（2015）芙民初字第7478.7603号
（2019）粤0305民初1382号-1429号
（2019）鲁1724民初195号- 018）
苏0509民初6820号
ｌｏｚ2016ｌｏｚ赣1030民初220号
一亿二千三百四十五万（2016）粤0605民初14359号
（2018）土地承包经营权转让浙0523民初2386号
2016闽0525民初3798号2016闽0525民初3798号
（空一行行距15磅）（2014）滨港民初字第307号

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

sort case_wid
drop if case_wid ==""

foreach var of varlist case_wid {
	* Remove space variables, parentheses, and other symbols
	replace `var' = ustrregexra(`var',"[^\p{L}\p{N}]", "")
		}
sort case_wid

gen case_length = strlen(case_wid)

summarize case_length

drop if  case_length > 66

drop case_length

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

* Flag duplicate case_wid and count per group
    bysort case_wid: gen dup_count = _N
    replace dup_count = . if _n > 1 // Optional: keep only the count at the first occurrence
    * Create a local macro containing all variables to check
    local varlist var1 var2
    * Use a loop to create flag variables
    foreach var of local varlist {
        gen flag_`var' = cond(missing(`var'), 0, 1)
    }
    * Count the number of non-missing values per row
    egen non_missing = rowtotal(flag*)
    * Within each case_wid, sort by non-missing count descending and flag the observation to keep
    bysort case_wid (non_missing): gen keep = (_n == _N)
    * Clean up temporary variables
    capture drop flag*
    * Drop duplicates, keeping the row with the most non-missing variables
    drop if !keep
    * Clean up auxiliary variables
    drop dup_count non_missing keep
    * Save the processed file, overwriting the original
    save "`input_file'", replace
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

* For the second variable, clear it if it equals the first
replace m_sorted_2 = "" if m_sorted_2 == m_sorted_1

* For the third variable, clear it if it equals any previous variable
replace m_sorted_3 = "" if m_sorted_3 == m_sorted_2 | m_sorted_3 == m_sorted_1

* For the fourth variable, clear it if it equals any previous variable
replace m_sorted_4 = "" if m_sorted_4 == m_sorted_3 | m_sorted_4 == m_sorted_2 | m_sorted_4 == m_sorted_1
* For the fifth variable, clear it if it equals any previous variable
replace m_sorted_5 = "" if  m_sorted_5 == m_sorted_4 | m_sorted_5 == m_sorted_3 | m_sorted_5 == m_sorted_2 | m_sorted_5 == m_sorted_1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

# Truncate characters
gen judge_fin2 = substr(judge_fin, 1, 45)
gen court_name2 = substr(court_name, 1, 88)
drop judge_fin court_name
rename judge_fin2  judge_fin
rename court_name2 court_name


# Clean case numbers longer than a certain character count, e.g. 66, to empty

foreach var of varlist s7 m_sorted_1 m_sorted_2 m_sorted_3 m_sorted_4 {

	gen case_length = strlen(`var')

	summarize case_length

	replace `var' =  "" if  case_length > 66

	drop case_length

		}

1
2
3
4
5
6
7
8

clear
forvalues j = 1/83 {
    use "D:\数据合集\原始数据\ans`j'.dta", clear
    merge 1:m case_wid_str using "C:\Users\PC\Desktop\hzp_project_re\一审二审数据匹配\排序\面板数据\all.dta", update nogen force
    drop if case_wid == ""
duplicates drop case_wid_str , force
    save "C:\Users\PC\Desktop\hzp_project_re\一审二审数据匹配\排序\面板数据\ans`j'.dta", replace
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

* Define the exclusion list
global exclude_list 22 50 54 58  105

* Initialize the valid_files variable
global valid_files

* Loop over the file number range and check whether each is in the exclusion list
forvalues i = 1/119 {
    local is_excluded 0
    foreach excl in $exclude_list {
        if `i' == `excl' {
            local is_excluded 1
            break
        }
    }
    if !`is_excluded' {
        global valid_files $valid_files `i'
    }
}

* Display the valid file numbers
display "Valid file numbers: $valid_files"


* Loop over and process the valid file numbers
foreach i of global valid_files {
    *Write your own loop matching code here
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

* ==============================
* Configure paths
* ==============================
local path "F:\数据合集\刑事案件"
local outpath "F:\数据合集\刑事案件简洁"

cd "`path'"


fs "*.csv", all

* ==============================
* Manually generate the CSV file list into a local
* ==============================
local files " 080hfjsviunvohos.csv 749baig9gvbblcn.csv     97sytdv97goushd.csv 08cdhas0hvihikd.csv 7839yhcudaboce.csv      98ya08chiahnicn.csv 0s87d0hvcinowbv.csv 788tgd9cayh0c-9.csv 9a7gtdc97g08a0a.csv 1368bcohucg970.csv 797fshonlsnlfu98.csv 9f79sghuvjksbklk.csv 236797rbvlsbvoo.csv 79d7goadbco9hki.csv 9s7fhosivnlsnlnffr.csv 2489ndsujbovbo.csv 79s7dhnofnvlbns.csv a97dyc8udv8+hds9.csv 28638bvbsa7f93.csv 80sdhv08uisnvoh.csv i7sdhvu9hbosdv8.csv 2937hvoushcv8h.csv 89hdoshvoinsnnlj.csv oa7gc7agbkd78ad.csv 4689578yhkjsbd.csv 89s0fjisn7vgiskub.csv oc8ahhc89ha98g.csv 4790r8hvobosh7.csv 8y08h0chaicnil8o.csv os8dvyh089shdvi.csv 4792794bvoshc8.csv 94573hvsoidhv9y.csv s8oyvf08s09vu08.csv 479hu5nnv89007.csv 9473hgnvov9h7s.csv      u8ca0hc80ha00a.csv 4927bvogf92h011.csv 97atcg97ga0hc08.csv ughdso8h98989.csv 739g4foh8hv991.csv 97sgvc98dshv08.csv "


* ==============================
* Loop over each file
* ==============================
* Initialize the counter
local i = 1

* Loop over each file
foreach f of local files {
    di "Processing file `i': `f'"
    * Import CSV
    import delimited "`f'", clear varnames(1) encoding(utf8)
    * Data cleaning
    * Processing section
    * Save as a numerically ordered DTA file
    save "`outpath'/`i'.dta", replace
    * Increment the counter
    local ++i
}

1

replace judge_fin = ustrregexra(法官名字变量, "[^\p{L}\p{N}]+", "")

1

keep if strpos(var, "民初") > 0

1

gen localed = strpos(a, b) > 0

1
2
3
4
5
6
7

# Define the list of symbols to remove (including spaces)
local symbols "ⅩⅩⅩ Ｘ ? ; ；， 《 》 ． ＋ （ ） ( ) & # 1 2 3 4 5 6 7 8 9 0 & A-Z a-z ；？ ＊ ︰ ++  .   XX XXX   ______   ` - +  BRR H imestimes jzwjzwnjzwnjzwjzwnjzwnjzwnjzwjzwjzwjzwnjzwnjzwnjzwjzwnjzwnj jzwnjzwjzwjzwnjzwnjzwnjzwnjzwnjzwjzwjzwjzwnjzwjzwnjzwjzwj spemsp sp emsp ；； *  XX XXX Ｘ ＸＸＸ"

* Iterate over the symbol list and remove each symbol and spaces
foreach s of local symbols {
    replace judge_fin = subinstr(judge_fin, "`s'", "", .)
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

* Import the Excel lookup table
clear
import excel "基本情况代码对照表.xlsx", ///
    sheet("支出1编码对照")

rename B varname
rename A varlabel

* Generate the command (note the compound quotes)
gen cmd = "label variable " + varname + " `" + `"""' + varlabel + `"""' + "'"

outsheet cmd using "变量标签命令.txt", noquote replace

1
2

*List the variable rows with character length greater than 14
list var if ustrlen(judge_fin) >= 14

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

replace judge_fin = ustrregexra(judge_fin, "人民陪审员.*", "") if  strpos(judge_fin, "人民陪审员") > 0
replace judge_fin = ustrregexra(judge_fin, "审判员.*", "") if  strpos(judge_fin, "审判员") > 0
replace judge_fin = ustrregexra(judge_fin, "独任.*", "") if  strpos(judge_fin, "独任") > 0
replace judge_fin = ustrregexra(judge_fin, "陪审员.*", "") if  strpos(judge_fin, "陪审员") > 0
replace judge_fin = ustrregexra(judge_fin, "代理.*", "") if  strpos(judge_fin, "代理") > 0
replace judge_fin = ustrregexra(judge_fin, "书记员.*", "") if  strpos(judge_fin, "书记员") > 0
replace judge_fin = ustrregexra(judge_fin, "助理.*", "") if  strpos(judge_fin, "助理") > 0
replace judge_fin = ustrregexra(judge_fin, "审判长.*", "") if  strpos(judge_fin, "审判长") > 0
replace judge_fin = ustrregexra(judge_fin, "人民.*", "") if  strpos(judge_fin, "人民") > 0
replace judge_fin = ustrregexra(judge_fin, "二.*", "") if  strpos(judge_fin, "年") > 0
replace judge_fin = ustrregexra(judge_fin, "适用.*", "") if  strpos(judge_fin, "适用") > 0
replace judge_fin = ustrregexra(judge_fin, "于.*", "") if  strpos(judge_fin, "开庭") > 0
replace judge_fin = ustrregexra(judge_fin, "对.*", "") if  strpos(judge_fin, "审理") > 0

1
2

*Delete rows with only one character, e.g. originally *陈
drop if ustrregexm(judge_fin, "^[\p{Han}]$")

1
2