import pandas as pd
from google.colab import files

Function to read a specified column from the uploaded Excel file

def read_excel_column(file_path, column_name):
try:
df = pd.read_excel(file_path)
if column_name in df.columns:
return df[column_name]
else:
raise ValueError(f”Column ‘{column_name}’ not found in the Excel file.”)
except Exception as e:
print(f”Error reading the Excel file: {e}”)
return None

Function to process the column data

def process_data(column_data):
data = {
‘Text’: column_data
}

# Create DataFrame
df = pd.DataFrame(data)

# Split 'Text' column into 'Depart' and 'Destination'
df[['Depart', 'Destination']] = df['Text'].str.split(' to ', expand=True)

# Remove rows with None values in 'Destination'
df = df.dropna(subset=['Destination'])

# Group by 'Depart' and concatenate 'Destination'
grouped = df.groupby('Depart')['Destination'].apply(lambda x: ', '.join(set(x.dropna()))).reset_index()

return grouped

Function to save the processed data to a text file

def save_output_to_file(grouped_data, output_file):
try:
with open(output_file, ‘w’) as f:
for index, row in grouped_data.iterrows():
f.write(f”Depart: {row[‘Depart’]}nDestination: {row[‘Destination’]}nn”)
print(f”Data saved to {output_file}”)
except Exception as e:
print(f”Error saving the file: {e}”)

Upload the Excel file

uploaded = files.upload()

Assuming only one file is uploaded, get the file name

excel_file_path = list(uploaded.keys())[0]

Specify the column name you want to read

column_name = ‘Text’

Read the specified column

column_data = read_excel_column(excel_file_path, column_name)
if column_data is not None:
# Process the data
grouped_data = process_data(column_data)

# Define the output file path
output_file_path = 'output_depart_dest.txt'

# Save the processed data to the output file
save_output_to_file(grouped_data, output_file_path)

# Download the output file
files.download(output_file_path)

else:
print(“Failed to read the column data.”)

i developped a code that transform excel file to text and i wanna count the persons going from the same place i tried but the count still incorrect i wanna count from the transformed text

script python
that transform excel file to text and count the persons where they depart from the same place