If you’re familiar with statistical and data analysis, you’ve probably worked with CSV (Comma-Separated Values) files. They store tabular data in plain text, making them easy to create, read, and modify using Python.
In this article, we’ll learn to process a sample CSV file with Python, from loading data to performing basic statistical analysis, data manipulations and calculations.
▶️ Link to the Google Colab notebook
Setting Up the Environment
We’ll be using the pandas library to read, analyze, and manipulate CSV data. Install it using pip:
pip install pandas
Generating a Sample CSV File
Before going ahead, let’s create a sample CSV file with columns such as date, product_id, quantity_sold, unit_price, and total_sales:
import pandas as pd
from datetime import datetime, timedelta
import random
# Generate random data
dates = [datetime.now().date() - timedelta(days=i) for i in range(10)]
product_ids = [101, 102, 103, 104, 105]
data = []
for date in dates:
for product_id in product_ids:
quantity_sold = random.randint(1, 50)
unit_price = round(random.uniform(10, 100), 2)
total_sales = round(quantity_sold * unit_price, 2)
data.append([date, product_id, quantity_sold, unit_price, total_sales])
# Create DataFrame and save to CSV
df = pd.DataFrame(data, columns=['date', 'product_id', 'quantity_sold', 'unit_price', 'total_sales'])
df.to_csv('sample_sales_data.csv', index=False)
print("Sample CSV file 'sample_sales_data.csv' generated successfully.")
This creates a sample CSV file named with random sales data:
Sample CSV file 'sample_sales_data.csv' generated successfully.
Reading CSV Files
Now that we have a sample CSV file, let’s load it into Python using pandas. We can load CSV files using the read_csv() method:
# Import pandas and load CSV file
import pandas as pd
# Read CSV file
df = pd.read_csv('sample_sales_data.csv')
# Get the first few rows
print(df.head())
This code reads sample_sales_data.csv into a dataframe and shows the first five rows.
date product_id quantity_sold unit_price total_sales 0 2024-11-06 101 12 71.44 857.28 1 2024-11-06 102 41 29.63 1214.83 2 2024-11-06 103 33 70.56 2328.48 3 2024-11-06 104 43 68.49 2945.07 4 2024-11-06 105 38 52.25 1985.50
Exploring and Summarizing Data
Once you’ve loaded the data into a dataframe, it’s important to explore and understand the data.
Checking Data Types and Summary Information
To get an overview of the dataframe, including data types and any missing values, you can use the info() method:
# Get info about the dataframe print(df.info())
This outputs:
RangeIndex: 50 entries, 0 to 49 Data columns (total 5 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 date 50 non-null object 1 product_id 50 non-null int64 2 quantity_sold 50 non-null int64 3 unit_price 50 non-null float64 4 total_sales 50 non-null float64 dtypes: float64(2), int64(2), object(1) memory usage: 2.1+ KB None
Descriptive Statistics
You can get basic descriptive statistics on the numerical columns by calling the describe() method on the dataframe:
# Get descriptive statistics print(df.describe())
This shows statistics like count, mean, standard deviation, min, max, and quartiles for the numeric columns in your dataframe.
product_id quantity_sold unit_price total_sales count 50.000000 50.000000 50.000000 50.000000 mean 103.000000 25.480000 57.916400 1407.498400 std 1.428571 15.026833 22.160509 978.596119 min 101.000000 1.000000 12.560000 42.740000 25% 102.000000 13.000000 42.110000 697.895000 50% 103.000000 25.500000 53.220000 1231.015000 75% 104.000000 40.250000 74.472500 2073.940000 max 105.000000 50.000000 97.600000 3765.510000
Handling Missing Values
In real-world datasets, missing values are common.
Detecting Missing Values
To check for missing values, use the isna() method combined with sum():
# Check for missing values missing_values = df.isna().sum() print(missing_values)
There aren’t any missing values:
date 0 product_id 0 quantity_sold 0 unit_price 0 total_sales 0 discounted_sales 0 dtype: int64
Imputing Missing Values
If you find any missing values, you can fill them using fillna(). There are no missing values in the sample dataset. But if you have, you can fill the missing values in unit_price with the mean price:
# Fill missing values with the mean df['unit_price'] = df['unit_price'].fillna(df['unit_price'].mean())
Dropping Missing Values
You can drop rows with missing values like so:
# Drop rows with any missing values df_cleaned = df.dropna() print(df_cleaned.info())
Basic Data Manipulation
With an understanding of your data, you can start manipulating it.
Filtering Rows
Sometimes, you’ll need to filter rows based on conditions. For the example we have, let’s get all records where quantity_sold is greater than 20:
# Filter rows where quantity_sold is greater than 20 high_sales = df[df['quantity_sold'] > 20] print(high_sales)
Truncated output:
date product_id quantity_sold unit_price total_sales 1 2024-11-06 102 41 29.63 1214.83 2 2024-11-06 103 33 70.56 2328.48 3 2024-11-06 104 43 68.49 2945.07 4 2024-11-06 105 38 52.25 1985.50 . . . 46 2024-10-28 102 25 53.77 1344.25 47 2024-10-28 103 38 49.54 1882.52 48 2024-10-28 104 47 52.67 2475.49 49 2024-10-28 105 41 70.59 2894.19
Selecting Specific Columns
Let’s select specific columns:
# Select specific columns selected_columns = df[['date', 'product_id', 'total_sales']] print(selected_columns)
Truncated output:
date product_id total_sales 0 2024-11-06 101 857.28 1 2024-11-06 102 1214.83 2 2024-11-06 103 2328.48 3 2024-11-06 104 2945.07 4 2024-11-06 105 1985.50 . . . 46 2024-10-28 102 1344.25 47 2024-10-28 103 1882.52 48 2024-10-28 104 2475.49 49 2024-10-28 105 2894.19
Adding New Columns
If needed, you can add columns to the dataframe. For example, say you want to calculate a 10% discount on total_sales:
# Add a new column for discounted sales df['discounted_sales'] = df['total_sales'] * 0.9 print(df.head())
The head of the updated dataframe:
date product_id quantity_sold unit_price total_sales \ 0 2024-11-06 101 12 71.44 857.28 1 2024-11-06 102 41 29.63 1214.83 2 2024-11-06 103 33 70.56 2328.48 3 2024-11-06 104 43 68.49 2945.07 4 2024-11-06 105 38 52.25 1985.50 discounted_sales 0 771.552 1 1093.347 2 2095.632 3 2650.563 4 1786.950
Grouping and Aggregating Data
You can group data to perform calculations on subsets of your dataframe.
Grouping by a Column
To find the total sales for each product, you can use groupby() along with an aggregation function:
# Calculate total sales by product_id
total_sales_by_product = df.groupby('product_id')['total_sales'].sum().reset_index()
print(total_sales_by_product)
Here’s the output:
product_id total_sales 0 101 9756.00 1 102 15535.11 2 103 16004.02 3 104 17617.64 4 105 11462.15
Multiple Aggregations
You can also perform multiple aggregations at once. Let’s get both the total and average sales per product:
# Calculate total and average sales grouped by product_id
sales_summary = df.groupby('product_id').agg(total_sales=('total_sales', 'sum'), average_sales=('total_sales', 'mean')).reset_index()
print(sales_summary)
This outputs:
product_id total_sales average_sales 0 101 9756.00 975.600 1 102 15535.11 1553.511 2 103 16004.02 1600.402 3 104 17617.64 1761.764 4 105 11462.15 1146.215
After you’ve processed the dataframe, you may want to save your results back to a CSV file:
df.to_csv('processed_sales_data.csv', index=False)
print("Processed data saved to 'processed_sales_data.csv'.")
Next Steps
If you’re looking to learn CSV processing with Python, here are a few advanced topics to consider:
- Chunked reading: Useful for handling large files that do not fit into memory by reading the data in smaller chunks using the chunksize parameter in pd.read_csv().
- Custom data types: You can specify data types for columns when loading the CSV to optimize memory usage with the dtype parameter.
In this tutorial, we’ve covered the basics of processing CSV files in Python using pandas. Happy data analysis!
Cover image: Created by Author | Segmind SSD-1B Model
