Simple ETL Process using pythan

Question

Hi All , we want to impletement the ETL Process in pythan , can anyone share simple code with working example. Thanks karthik. K

Amit Mohanty · Answer

Let's assume you have a CSV file named "input_data.csv" with the following content: id,name,age 1,Rakesh,25 2,Asit,30 3,Subhashree,22 We want to transform it by adding a "birth_year" column based on the current year (2023). import pandas as pd from datetime import datetime # Extract: Load the data from the input CSV file input_file = "input_data.csv" data = pd.read_csv(input_file) # Transform: Calculate birth year based on current year current_year = datetime.now().year data['birth_year'] = current_year - data['age'] # Load: Save the transformed data to a new CSV file output_file = "transformed_data.csv" data.to_csv(output_file, index=False) print("ETL process completed!") After running the above code, we'll get a new CSV file named "transformed_data.csv" with the following content: id,name,age,birth_year 1,Rakesh,25,1998 2,Asit,30,1993 3,Subhashree,22,2001 Before running the above code, make sure you have the pandas library installed.

Mohammad Hussain · Answer

Here is the just a basic example of how to extract data from a CSV file and load into SQL using Python . Please note that you'll need to install the necessary libraries if you haven't already: pip install pandas sqlalchemy Now, let's go through the ETL process: import pandas as pd import pyodbc # Define the connection string connection_string = "DRIVER={SQL Server};SERVER=localhost;DATABASE=my_database;UID=my_username;PWD=my_password" # Create a connection to the SQL database conn = pyodbc.connect(connection_string) # Load the CSV data into a Pandas DataFrame df = pd.read_csv("data.csv") # Transform the data as needed df.drop_duplicates() df.fillna(0) # Load the data into the SQL database df.to_sql("my_table", conn, if_exists="replace") # Close the connection to the SQL database conn.close()

Deepak Rawat · Answer

Before running the code, make sure you have the pandas library installed. You can install it using pip if you haven't already: pip install pandas Here's a simple example code: import pandas as pd import sqlite3 # Extract csv_file_path = 'data.csv' # Replace with your CSV file path data = pd.read_csv(csv_file_path) # Transform transformed_data = data.rename(columns={'old_column_name': 'new_column_name'}) # Example transformation # Load database_path = 'my_database.db' # Replace with your desired database file path conn = sqlite3.connect(database_path) transformed_data.to_sql('table_name', conn, if_exists='replace', index=False) # Close the connection conn.close() print("ETL process completed.") Replace 'old_column_name' and 'new_column_name' with the appropriate column names you want to change in the transformation step. Also, replace 'data.csv' with the path to your CSV file and 'my_database.db' with the desired path for your SQLite database file.

Cr Bhargavi · Answer

import csv # ETL Extract def extract_data(input_file): data = [] with open(input_file, 'r') as file: reader = csv.reader(file) for row in reader: data.append(row) return data # ETL Transform def transform_data(data): transformed_data = [] for row in data: transformed_row = [cell.upper() for cell in row] transformed_data.append(transformed_row) return transformed_data # ETL Load def load_data(output_file, data): with open(output_file, 'w', newline='') as file: writer = csv.writer(file) writer.writerows(data) # Main ETL Process def main(): input_file = 'input_data.csv' output_file = 'output_data.csv' # Extract extracted_data = extract_data(input_file) # Transform transformed_data = transform_data(extracted_data) # Load load_data(output_file, transformed_data) print("ETL process completed.") if __name__ == "__main__": main()

Karthik K · Answer

Thanks to all for vital respones . I have share my code. In that i had two issue kindly anyone look into this provide some solution for this. #1. where do i need to define the Input file path / Output file Path #2. i got a warning like , " Unresolved import 'src.transform ' " Thanks in advance , Karthik .K

Simple ETL Process using pythan

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (5)