# Lists to hold each column of the DataFrame
dates = []
times = []
senders = []
contents = []

# Process each line again
for line in lines:
# Check if the line matches the date format
date_match = re.match(date_pattern, line)
if date_match:
current_date = datetime.strptime(date_match.group(1), '%Y/%m/%d').date()
continue

# Check if the line matches the time and sender format
time_sender_match = re.match(time_sender_pattern, line)
if time_sender_match:
# Save the previous message if there is one
if current_content:
dates.append(current_date)
times.append(current_time)
senders.append(current_sender)
contents.append('\n'.join(current_content))

# Start a new message
current_time = time_sender_match.group(1)
current_sender = time_sender_match.group(2)
current_content = [re.sub(time_format_in_content_pattern, '', line[time_sender_match.end():])]
else:
# If the line is part of a message, add it to the current content
current_content.append(re.sub(time_format_in_content_pattern, '', line))

# Add the last message if there is one
if current_content:
dates.append(current_date)
times.append(current_time)
senders.append(current_sender)
contents.append('\n'.join(current_content))

# Create a DataFrame
df = pd.DataFrame({
'年': [d.year for d in dates],
'月': [d.month for d in dates],
'日': [d.day for d in dates],
'時間': times,
'送信者': senders,
'内容': contents
})

# Remove the first row
df = df.iloc[1:]

# Save the DataFrame to an Excel file
df.to_excel('/mnt/data/line_chat_history_corrected.xlsx', index=False)

df.head()