Data Manipulation With Pandas

Click to share! ⬇️

Pandas is a powerful Python library that provides a variety of tools for working with and manipulating data. One of the key features of pandas is its ability to easily read and write data from a variety of sources, including CSV files, Excel spreadsheets, and SQL databases.

Selecting and Filtering Data with Pandas
Sorting and Grouping Data with Pandas
Manipulating Data with Pandas Functions and Methods
Working with Missing Data in Pandas
Merging and Joining Data with Pandas
Aggregating and Pivoting Data with Pandas

To read data into a pandas DataFrame, you can use the read_csv function. This function allows you to specify the file path or URL of the data you want to read, as well as various options for parsing and handling the data. For example, to read a CSV file called “data.csv” into a DataFrame, you can use the following code:

import pandas as pd

df = pd.read_csv("data.csv")

You can also use the read_excel function to read data from an Excel spreadsheet, or the read_sql function to read data from a SQL database.

Once you have read the data into a DataFrame, you can use a variety of pandas functions and methods to manipulate and analyze the data. For example, you can use the head and tail functions to view the first and last few rows of the DataFrame, or the describe function to get a summary of the numerical columns in the DataFrame.

To write data from a pandas DataFrame to a file or database, you can use the to_csv function to write the data to a CSV file, the to_excel function to write the data to an Excel spreadsheet, or the to_sql function to write the data to a SQL database.

Selecting and Filtering Data with Pandas

To select specific rows and columns from a pandas DataFrame, you can use the [] operator and specify the labels of the rows and columns you want to select. For example, to select the “Type” and “Price” columns from a DataFrame called df, you can use the following code:

selected_columns = df[["Type", "Price"]]

     Type   Price
0     Put    5.30
1    Call    4.51
2    Call    8.63
3     Put   79.31
4    Call  126.38

You can also use the [] operator to select rows by specifying a boolean condition. For example, to select all rows where the “Price” column is greater than 5, you can use the following code:

selected_rows = df[df["Price"] > 5]

    Symbol   Price  Type  Strike  ... Vol/OI       IV     Delta      Time
0     LAZR    5.30   Put     6.0  ...  93.12   87.75% -0.804191  12:00 ET
2     CHPT    8.63  Call     9.0  ...  56.74   68.55%  0.368232  11:57 ET
3      BBY   79.31   Put    55.0  ...  45.46   54.12% -0.062534  12:16 ET
4     TSLA  126.38  Call   129.0  ...  41.29   98.32%  0.375635  12:07 ET
5      PFE   51.29   Put    51.0  ...  32.26   23.30% -0.448061  12:06 ET

Another way to select and filter data in a DataFrame is by using the loc and iloc attributes. The loc attribute allows you to select rows and columns by label, while the iloc attribute allows you to select rows and columns by integer position.

For example, to select the first three rows and the “Type” and “Price” columns of a DataFrame, you can use the following code:

selected_data = df.loc[:2, ["Type", "Price"]]

   Type  Price
0   Put   5.30
1  Call   4.51
2  Call   8.63

You can also use the isin function to filter data based on a list of values. For example, to select all rows where the “Type” column is “Call” you can use the following code:

selected_rows = df[df["Type"].isin(["Call"])]

    Symbol   Price  Type  Strike  ... Vol/OI       IV     Delta      Time
1      AMC    4.51  Call     4.0  ...  58.85  221.29%  0.859016  12:15 ET
2     CHPT    8.63  Call     9.0  ...  56.74   68.55%  0.368232  11:57 ET
4     TSLA  126.38  Call   129.0  ...  41.29   98.32%  0.375635  12:07 ET
7     TSLA  126.38  Call   132.0  ...  29.53  102.07%  0.231550  12:07 ET
11     GME   19.09  Call    17.5  ...  20.70   76.84%  0.712250  12:03 ET

Selecting and filtering data with pandas is a powerful and flexible way to work with data in Python. Whether you want to select specific rows and columns, or filter the data based on certain criteria, pandas provides a variety of tools for doing so.

Sorting and Grouping Data with Pandas

Sorting and grouping data is an important task when working with data in pandas. Whether you want to sort the data by a specific column, or group the data by one or more columns and apply aggregation functions, pandas provides a variety of tools for doing so.

To sort a pandas DataFrame by a specific column, you can use the sort_values function and specify the name of the column you want to sort by. For example, to sort a DataFrame called df by the “Price” column in ascending order, you can use the following code:

sorted_df = df.sort_values("Price")

                                                Symbol   Price  Type  Strike  \
34                                                FUBO    1.88  Call     2.0   
331                                               EVLV    2.10  Call     2.5   
300                                                CGC    2.23  Call     3.5   
61                                                 CGC    2.23   Put     3.5   
16                                                SLDP    2.41  Call     2.5

You can also specify the ascending parameter to specify whether the data should be sorted in ascending or descending order. For example, to sort the DataFrame by the “Price” column in descending order, you can use the following code:

sorted_df = df.sort_values("Price", ascending=False)

                                                Symbol   Price  Type  Strike  \
101                                               ASML  551.10   Put   540.0   
385                                               LRCX  400.93  Call   460.0   
188                                               LRCX  400.93   Put   405.0   
256                                               LULU  310.81   Put   307.5   
164                                               NFLX  294.00   Put   297.5

To group a pandas DataFrame by one or more columns and apply aggregation functions, you can use the groupby function and specify the columns you want to group by. For example, to group the DataFrame by the “Type” column and calculate the mean “Price” for each group, you can use the following code:

grouped_df = df.groupby("Type")["Price"].mean()

Type
Call     95.198989
Put     100.990227
Name: Price, dtype: float64

You can also use the agg function to apply multiple aggregation functions to the data. For example, to group the DataFrame by the “Type” column and calculate the mean, median, and standard deviation of the “Price” column for each group, you can use the following code:

grouped_df = df.groupby("Type")["Price"].agg(["mean", "median", "std"])

            mean  median        std
Type                               
Call   95.198989   87.32  75.611119
Put   100.990227   87.94  83.767693

You can also use the apply function to apply a custom function to the data. For example, to group the DataFrame by the “Type” column and apply a custom function to calculate the range of the “Price” column for each group, you can use the following code:

def price_range(group):
    return group.max() - group.min()

grouped_df = df.groupby("Type")["Price"].apply(price_range)

Type
Call    399.05
Put     548.87
Name: Price, dtype: float64

Sorting and grouping data with pandas is a powerful and flexible way to work with data in Python. Whether you want to sort the data by a specific column, or group the data by one or more columns and apply aggregation functions, pandas provides a variety of tools for doing so.

Manipulating Data with Pandas Functions and Methods

Pandas provides a variety of functions and methods for manipulating data in a DataFrame. Whether you want to transform the data in a specific way, or perform calculations on the data, pandas has a tool for almost any task.

One way to manipulate data in a DataFrame is by using the apply function. The apply function allows you to apply a custom function to the data, either across all rows or columns of the DataFrame, or to a specific axis.

For example, to apply a custom function that multiplies the “Price” column by 10, you can use the following code:

def multiply_by_10(x):
    return x * 10

df["Price"] = df["Price"].apply(multiply_by_10)

                                                Symbol   Price  Type  Strike  \
0                                                 LAZR    53.0   Put     6.0   
1                                                  AMC    45.1  Call     4.0   
2                                                 CHPT    86.3  Call     9.0   
3                                                  BBY   793.1   Put    55.0   
4                                                 TSLA  1263.8  Call   129.0

Another way to manipulate data in a DataFrame is by using the built-in

mathematical functions and statistical methods provided by pandas. For example, you can use the mean and median functions to calculate the mean and median of a column or the cov and corr functions to calculate the covariance and correlation between two columns.

For example, to calculate the mean “Price” and “Volume” for each “Type”, you can use the following code:

mean_values = df.groupby("Type")[["Price", "Volume"]].mean()

           Price       Volume
Type                         
Call   95.198989  4378.595745
Put   100.990227  5368.677273

You can also use the pct_change function to calculate the percentage change between consecutive rows, or the diff function to calculate the difference between consecutive rows.

For example, to calculate the percentage change in the “Price” column between consecutive rows, you can use the following code:

price_changes = df["Price"].pct_change()

Overall, manipulating data with pandas functions and methods is a powerful and flexible way to work with data in Python. Whether you want to transform the data in a specific way, or perform calculations on the data, pandas provides a variety of tools for doing so.

Working with Missing Data in Pandas

Working with missing data is an important task when working with data in pandas. Missing data can occur for a variety of reasons, such as data being missing at the source, data being lost during data processing or storage, or data being intentionally left blank.

Pandas provides a variety of functions and methods for handling missing data in a DataFrame. One way to identify missing data is by using the isnull function, which returns a boolean mask indicating whether each value in the DataFrame is null or not.

For example, to identify all rows with missing values in the “Price” column of a DataFrame called df, you can use the following code:

missing_rows = df[df["Price"].isnull()]

You can also use the notnull function to identify rows with non-null values.

To handle missing data, you can use the fillna function to fill missing values with a specific value. For example, to fill missing values in the “Price” column with the mean “Price”, you can use the following code:

mean_price = df["Price"].mean()
df["Price"] = df["Price"].fillna(mean_price)

Another option is to drop rows or columns with missing data using the dropna function. For example, to drop rows with missing values in any column, you can use the following code:

df.dropna(inplace=True)

You can also specify the axis parameter to drop rows or columns. For example, to drop columns with missing values, you can use the following code:

df.dropna(axis=1, inplace=True)

You can also specify the thresh parameter to specify the minimum number of non-null values that a row or column must have in order to be kept. For example, to drop rows with less than 3 non-null values, you can use the following code:

df.dropna(thresh=3, inplace=True)

Working with missing data in pandas is an important task when dealing with real-world data. Whether you want to fill missing values with a specific value, drop rows or columns with missing data, or identify missing data, pandas provides various tools for handling missing data.

Merging and Joining Data with Pandas

Merging and joining data is a common task when working with data in pandas. Whether you want to combine data from multiple sources, or merge data from different tables or datasets, pandas provides a variety of functions and methods for doing so.

One way to merge data in pandas is by using the concat function. The concat function allows you to concatenate two or more DataFrames along a specific axis.

For example, to concatenate two DataFrames df1 and df2 along the rows axis, you can use the following code:

df = pd.concat([df1, df2])

You can also use the merge function to merge two DataFrames based on a common key or set of keys. For example, to merge df1 and df2 on the “Symbol” column, you can use the following code:

df = pd.merge(df1, df2, on="Symbol")

Another way to merge data in pandas is by using the join function. The join function allows you to merge two DataFrames based on the index of each DataFrame.

For example, to merge df1 and df2 on the index of df1, you can use the following code:

df = df1.join(df2)

Merging and joining data with pandas is a powerful and flexible way to work with data in Python. Whether you want to combine data from multiple sources, or merge data from different tables or datasets, pandas offers a variety of tools for doing so.

Aggregating and Pivoting Data with Pandas

Aggregating and pivoting data is a common task when working with data in pandas. Whether you want to summarize data by group, or reshape the data to better fit your analysis or visualization needs, pandas provides a variety of functions and methods for doing so.

One way to aggregate data in pandas is by using the groupby function. The groupby function allows you to group data by a specific column or set of columns, and apply aggregation functions to the data.

For example, to group the DataFrame df by the “Type” column and calculate the mean “Price” for each group, you can use the following code:

grouped_df = df.groupby("Type")["Price"].mean()

You can also use the pivot_table function to reshape the data and create a pivot table. A pivot table is a table that summarizes data by group, and allows you to easily compare and analyze the data.

For example, to create a pivot table that shows the mean “Price” by “Type” and “Strike”, you can use the following code:

pivot_table = df.pivot_table(index="Type", columns="Strike", values="Price", aggfunc="mean")

Aggregating and pivoting data with pandas is a powerful and flexible way to work with data in Python. Whether you want to summarize data by group, or reshape the data to better fit your analysis or visualization needs, pandas provides a variety of tools for doing so.