reddysainath581/placement.activity
0
1import streamlit as st2import pandas as pd3import gspread4from google.oauth2.service_account import Credentials5import re6import requests7import fitz # PyMuPDF8import pdfplumber9import logging10from selenium import webdriver11from selenium.webdriver.chrome.options import Options12from datetime import datetime13from dotenv import load_dotenv14from linkedin_api import Linkedin15import os16 17# Configure logging18logging.basicConfig(level=logging.WARNING, format="%(asctime)s - %(levelname)s - %(message)s")19 20# Load environment variables21load_dotenv()22options = Options()23options.add_argument("--headless")24driver = webdriver.Chrome(options=options)25st.title("Placement Report")26# Streamlit input for email and password27EMAIL = st.text_input("## Enter your LinkedIn username or email:")28PASSWORD = st.text_input("## Enter your password:", type="password")29 30# Check if both email and password are provided31if EMAIL and PASSWORD:32 # Proceed with authentication if both fields are filled33 api = Linkedin(EMAIL, PASSWORD, debug=True)34 35 36# Streamlit App Title37 38 39# Google Sheets Connection Setup40SERVICE_ACCOUNT_FILE = r"C:\Users\lopam\Documents\Linkedin interface\newcred.json" 41SCOPES = ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive']42 43# Authenticate with Google Sheets44try:45 credentials = Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES)46 client = gspread.authorize(credentials)47except Exception as e:48 st.error(f"Failed to authenticate Google Sheets API. Error: {e}")49 st.stop()50 51# Function to extract LinkedIn URLs from a given cell (text or HTML)52def extract_hyperlinks(cell):53 linkedin_urls = []54 if isinstance(cell, str): # Ensure the cell contains a string.55 linkedin_urls = re.findall(r'https?://(?:www\.)?linkedin\.com/in/[^\s]+', cell)56 return linkedin_urls57 58# Function to extract LinkedIn URLs from PDF59def extract_linkedin_from_pdf(pdf_file):60 linkedin_urls = []61 with fitz.open(pdf_file) as my_pdf_file:62 for page_number in range(1, len(my_pdf_file) + 1):63 page = my_pdf_file[page_number - 1]64 for pdf_link in page.links():65 if "uri" in pdf_link:66 url = pdf_link["uri"]67 if re.match(r'https?://(?:www\.)?linkedin\.com/in/[^\s]+', url):68 linkedin_urls.append(url)69 return linkedin_urls70 71def split_experience(row):72 # If no experience data is found, return "No Experience Data" for both companies73 if row['Experience'] == "No experience data found.":74 return {"Company 1": "No Experience Data", "Company 2": "No Experience Data found" }75 76 # Split the experience data by newlines to separate different companies77 companies = row['Experience'].split("\n")78 79 # Create a dictionary to store the company data80 company_dict = {}81 82 # Assign each company data to a Company column83 for i, company in enumerate(companies):84 company_dict[f"Company {i + 1}"] = company.strip() # Strip any extra spaces85 86 return company_dict87 88# Function to process the DataFrame and split experience columns89def process_data(df, api):90 # Split the experience data and add new columns91 experience_split = df.apply(split_experience, axis=1)92 experience_part = pd.DataFrame(experience_split.tolist())93 94 # Merge the new experience columns into the original DataFrame95 df = pd.concat([df, experience_part], axis=1)96 97 # Fill any NaN values with "No Experience Data found"98 df.fillna("No Experience Data found", inplace=True)99 100 return df101 102# Updated classify_experience function103def classify_experience(row, company_columns):104 # Check if Batch Start Date and Batch End Date are valid105 if pd.isna(row['Batch Start Date']) or pd.isna(row['Batch End Date']):106 return "Invalid Batch Dates"107 108 batch_start_date = datetime.strptime(row['Batch Start Date'], "%m %Y")109 batch_end_date = datetime.strptime(row['Batch End Date'], "%m %Y")110 111 # Loop through the experience columns (e.g., Company 1, Company 2, etc.)112 for company_col in company_columns:113 # If the company column is empty or has 'Not placed' data, classify as "No experience"114 if pd.isna(row.get(company_col, None)) or 'Not placed' in str(row.get(company_col, '')):115 return "No experience"116 117 # Extract the experience data from the company column118 experience_data = row[company_col]119 120 # Try to extract the start date from the experience data121 try:122 start_date_str = experience_data.split("Start Date:")[1].split(",")[0].strip()123 start_date = datetime.strptime(start_date_str, "%m %Y")124 except (IndexError, ValueError):125 return "Not placed" # If unable to extract the start date, return "Not placed"126 127 # Classify based on the batch dates128 if start_date < batch_start_date:129 return "Pre Imarticus"130 elif start_date > batch_end_date:131 return "Post Imarticus"132 elif batch_start_date <= start_date <= batch_end_date:133 return "Self Placed"134 else:135 return "Unknown" # In case no condition is met, you can use "Unknown"136 137 # If no valid experience is found after checking all columns138 return "No experience data found"139 140# For Google Sheets input141file_type = st.radio("Choose the source of data:", ("Google Sheets", "Excel File"))142 143# Convert Batch Start Date and Batch End Date to datetime format with only month and year (month as number)144def convert_to_month_year(df, date_column_name):145 try:146 df[date_column_name] = pd.to_datetime(df[date_column_name], errors='coerce').dt.to_period('M').dt.strftime('%m %Y')147 except Exception as e:148 logging.warning(f"Error converting {date_column_name}: {e}")149 return df150 151if file_type == "Google Sheets":152 SHEET_ID = st.text_input("## Enter the Google Sheet URL (found in the sheet URL):")153 154 if SHEET_ID:155 try:156 sheet = client.open_by_key(SHEET_ID).sheet1157 cell_values = sheet.get_all_values()158 data = pd.DataFrame(cell_values[1:], columns=cell_values[0])159 st.dataframe(data)160 161 # LinkedIn URL Extraction Logic162 df = pd.DataFrame(columns=["Unique ID", "Student Name", "Batch Start Date", "Batch End Date", "Link"])163 164 # Process each row for LinkedIn URLs165 for _, row in data.iterrows():166 linkedin_urls_collected = []167 for cell in row:168 linkedin_urls = extract_hyperlinks(cell)169 linkedin_urls_collected.extend(linkedin_urls)170 171 # Look for Google Drive links to extract PDFs172 for cell in row:173 if isinstance(cell, str) and 'drive.google.com' in cell:174 try:175 if "id=" in cell:176 file_id = cell.split("id=")[1].split("&")[0]177 elif "/d/" in cell:178 file_id = cell.split("/d/")[1].split("/")[0]179 else:180 continue181 182 download_url = f"https://drive.google.com/uc?export=download&id={file_id}"183 response = requests.get(download_url)184 if response.status_code == 200:185 with open("temp.pdf", "wb") as f:186 f.write(response.content)187 188 linkedin_urls_from_pdf = extract_linkedin_from_pdf("temp.pdf")189 linkedin_urls_collected.extend(linkedin_urls_from_pdf)190 except Exception as e:191 logging.error(f"Error processing Google Drive link: {e}")192 193 linkedin_urls_collected = list(set(linkedin_urls_collected)) # Remove duplicates194 195 # Add LinkedIn URLs to DataFrame196 if linkedin_urls_collected:197 for linkedin_url in linkedin_urls_collected:198 temp_df = pd.DataFrame({199 "Unique ID": [row['Unique ID']],200 "Student Name": [row['Student Name']],201 "Batch Start Date": [row['Batch Start Date']],202 "Batch End Date": [row['Batch End Date']],203 "Link": [linkedin_url]204 })205 df = pd.concat([df, temp_df], ignore_index=True)206 else:207 temp_df = pd.DataFrame({208 "Unique ID": [row['Unique ID']],209 "Student Name": [row['Student Name']],210 "Batch Start Date": [row['Batch Start Date']],211 "Batch End Date": [row['Batch End Date']],212 "Link": ["No LinkedIn URL found"]213 })214 df = pd.concat([df, temp_df], ignore_index=True)215 216 # LinkedIn username extraction for the Google Sheets data217 df["Username"] = df["Link"].apply(lambda link: re.search(r'linkedin\.com/in/([^/]+)/?', link).group(1) if pd.notnull(link) and "linkedin.com/in/" in link else "Invalid URL")218 219 # Apply date conversion to 'Batch Start Date' and 'Batch End Date' after LinkedIn extraction220 df = convert_to_month_year(df, "Batch Start Date")221 df = convert_to_month_year(df, "Batch End Date")222 223 # Scrape each LinkedIn profile link for the Google Sheets data224 data1 = []225 for _, row in df.iterrows():226 username = row["Username"]227 url = row["Link"]228 name_text = row["Student Name"]229 batch_start_date = row["Batch Start Date"]230 batch_end_date = row["Batch End Date"]231 unique_id = row["Unique ID"]232 233 if username != "Invalid URL":234 try:235 profile = api.get_profile(username)236 experience_data = profile.get("experience", [])237 experience_text = "\n".join(238 [239 f"Company: {exp.get('companyName', 'N/A')}, Title: {exp.get('title', 'N/A')}, "240 f"Start Date: {exp.get('timePeriod', {}).get('startDate', {}).get('month', 'N/A')} "241 f"{exp.get('timePeriod', {}).get('startDate', {}).get('year', 'N/A')}, "242 f"End Date: {exp.get('timePeriod', {}).get('endDate', {}).get('month', 'Present')} "243 f"{exp.get('timePeriod', {}).get('endDate', {}).get('year', 'N/A')}"244 for exp in experience_data245 ]246 ) if experience_data else "No experience data found."247 except Exception as e:248 logging.warning(f"API failed for {username}: {e}")249 experience_text = "API Error"250 else:251 experience_text = "Invalid URL"252 253 data1.append({254 "Unique ID": unique_id,255 "Student Name": name_text,256 "Batch Start Date": batch_start_date,257 "Batch End Date": batch_end_date,258 "LinkedIn URL": url,259 "Experience": experience_text260 })261 262 # Display the scraped data263 scraped_data_df = pd.DataFrame(data1)264 scraped_data_df = process_data(scraped_data_df, api)265 266 # Apply experience classification267 company_columns = ["Company 1", "Company 2"] # Modify based on your actual column names268 scraped_data_df["Experience Classification"] = scraped_data_df.apply(269 lambda row: classify_experience(row, company_columns), axis=1270 )271 272 st.dataframe(scraped_data_df)273 274 except Exception as e:275 st.error(f"Error accessing Google Sheets: {e}")276 277# For Excel File input278elif file_type == "Excel File":279 excel_file = st.file_uploader("Upload Excel file", type=["xls", "xlsx"])280 281 if excel_file:282 try:283 data = pd.read_excel(excel_file)284 st.dataframe(data)285 286 # LinkedIn URL Extraction Logic287 df = pd.DataFrame(columns=["Unique ID", "Student Name", "Batch Start Date", "Batch End Date", "Link"])288 289 # Process each row for LinkedIn URLs290 for _, row in data.iterrows():291 linkedin_urls_collected = []292 for cell in row:293 linkedin_urls = extract_hyperlinks(cell)294 linkedin_urls_collected.extend(linkedin_urls)295 296 # Look for Google Drive links to extract PDFs297 for cell in row:298 if isinstance(cell, str) and 'drive.google.com' in cell:299 try:300 if "id=" in cell:301 file_id = cell.split("id=")[1].split("&")[0]302 elif "/d/" in cell:303 file_id = cell.split("/d/")[1].split("/")[0]304 else:305 continue306 307 download_url = f"https://drive.google.com/uc?export=download&id={file_id}"308 response = requests.get(download_url)309 if response.status_code == 200:310 with open("temp.pdf", "wb") as f:311 f.write(response.content)312 313 linkedin_urls_from_pdf = extract_linkedin_from_pdf("temp.pdf")314 linkedin_urls_collected.extend(linkedin_urls_from_pdf)315 except Exception as e:316 logging.error(f"Error processing Google Drive link: {e}")317 318 linkedin_urls_collected = list(set(linkedin_urls_collected)) # Remove duplicates319 320 # Add LinkedIn URLs to DataFrame321 if linkedin_urls_collected:322 for linkedin_url in linkedin_urls_collected:323 temp_df = pd.DataFrame({324 "Unique ID": [row['Unique ID']],325 "Student Name": [row['Student Name']],326 "Batch Start Date": [row['Batch Start Date']],327 "Batch End Date": [row['Batch End Date']],328 "Link": [linkedin_url]329 })330 df = pd.concat([df, temp_df], ignore_index=True)331 else:332 temp_df = pd.DataFrame({333 "Unique ID": [row['Unique ID']],334 "Student Name": [row['Student Name']],335 "Batch Start Date": [row['Batch Start Date']],336 "Batch End Date": [row['Batch End Date']],337 "Link": ["No LinkedIn URL found"]338 })339 df = pd.concat([df, temp_df], ignore_index=True)340 341 # LinkedIn username extraction for the Google Sheets data342 df["Username"] = df["Link"].apply(lambda link: re.search(r'linkedin\.com/in/([^/]+)/?', link).group(1) if pd.notnull(link) and "linkedin.com/in/" in link else "Invalid URL")343 344 # Apply date conversion to 'Batch Start Date' and 'Batch End Date' after LinkedIn extraction345 df = convert_to_month_year(df, "Batch Start Date")346 df = convert_to_month_year(df, "Batch End Date")347 348 # Scrape each LinkedIn profile link for the Google Sheets data349 data1 = []350 for _, row in df.iterrows():351 username = row["Username"]352 url = row["Link"]353 name_text = row["Student Name"]354 batch_start_date = row["Batch Start Date"]355 batch_end_date = row["Batch End Date"]356 unique_id = row["Unique ID"]357 358 if username != "Invalid URL":359 try:360 profile = api.get_profile(username)361 experience_data = profile.get("experience", [])362 experience_text = "\n".join(363 [364 f"Company: {exp.get('companyName', 'N/A')}, Title: {exp.get('title', 'N/A')}, "365 f"Start Date: {exp.get('timePeriod', {}).get('startDate', {}).get('month', 'N/A')} "366 f"{exp.get('timePeriod', {}).get('startDate', {}).get('year', 'N/A')}, "367 f"End Date: {exp.get('timePeriod', {}).get('endDate', {}).get('month', 'Present')} "368 f"{exp.get('timePeriod', {}).get('endDate', {}).get('year', 'N/A')}"369 for exp in experience_data370 ]371 ) if experience_data else "No experience data found."372 except Exception as e:373 logging.warning(f"API failed for {username}: {e}")374 experience_text = "API Error"375 else:376 experience_text = "Invalid URL"377 378 data1.append({379 "Unique ID": unique_id,380 "Student Name": name_text,381 "Batch Start Date": batch_start_date,382 "Batch End Date": batch_end_date,383 "LinkedIn URL": url,384 "Experience": experience_text385 })386 387 388 389 # Function to convert DataFrame to CSV390 def convert_df_to_csv(df):391 return df.to_csv(index=False).encode("utf-8")392 393 394 # Display the scraped data395 scraped_data_df = pd.DataFrame(data1)396 scraped_data_df = process_data(scraped_data_df, api)397 398 # Apply experience classification399 company_columns = ["Company 1", "Company 2"] # Modify based on your actual column names400 scraped_data_df["Experience Classification"] = scraped_data_df.apply(401 lambda row: classify_experience(row, company_columns), axis=1402 )403 404 st.dataframe(scraped_data_df)405 406 # Convert DataFrame to CSV407 csv_data = convert_df_to_csv(scraped_data_df)408 409 # Add download button410 st.download_button(411 label="Download Scraped Data as CSV",412 data=csv_data,413 file_name="scraped_data.csv",414 mime="text/csv",415 )416 417 418 except Exception as e:419 st.error(f"Error reading Excel file: {e}")420driver.quit()421 422 423# Generate summary report for experience classification424import pandas as pd425import streamlit as st426 427# Generate summary report for experience classification428import pandas as pd429import streamlit as st430 431def generate_summary_report(scraped_data_df):432 """433 Generates and displays a summary report for experience classification with names included.434 """435 if "Experience Classification" in scraped_data_df.columns and "Student Name" in scraped_data_df.columns:436 summary_report = scraped_data_df.groupby(["Experience Classification"])\437 .agg({"Student Name": list, "Experience Classification": "count"})\438 .rename(columns={"Experience Classification": "Count"})\439 .reset_index()440 441 st.subheader("Summary Report: Experience Classification")442 st.dataframe(summary_report)443 444 # Optionally, display a bar chart445 st.bar_chart(summary_report.set_index("Experience Classification")['Count'])446 447 # Prepare CSV data for download448 csv = summary_report.to_csv(index=False).encode('utf-8')449 st.download_button(label="Download Summary Report", 450 data=csv, 451 file_name="experience_summary_report.csv", 452 mime="text/csv")453 else:454 st.warning("Required columns not found in the DataFrame.")455 456# Call the function to generate the summary report457generate_summary_report(scraped_data_df)458 459