[PYTHON] How to extract non-missing value nan data with pandas

--Environment --macOS Catalina version 10.15.7 - Python 3.8.5 - pandas 1.1.3

The data of the extraction source looks like this

df = pandas.read_csv('CSV.csv')
print(df)

output


Name Count Start End
0 Ponsuke 1 9:00  18:00
1 Ponsuke 2 18:00    NaN
2 Ponsuke 3 9:00  13:00
3 Ponsuke 4 NaN NaN
4 Ponsuke 5 9:00    NaN
5 Ponsuke 6 18:00    NaN
6 Ponsuke 7 12:00    NaN
7 Ponsuke 8 12:00    NaN
8 Ponsuke 9 NaN 18:00
9 Ponsuke 10 NaN NaN

Extract data where one column is not NaN

I want to extract
data
start End
x NaN NaN
o Not NaN NaN
x NaN Not NaN
o Not NaN Not NaN

Method


# [start]How to delete data whose column is NaN
print(df.dropna(subset=['start']))
# [start]How to extract data whose columns are not NaN
print(df[df['start'].notna()])
# [start]How to extract data whose columns are not NaN
print(df.query('start.notna()', engine='python'))

output


Name Count Start End
0 Ponsuke 1 9:00  18:00
1 Ponsuke 2 18:00    NaN
2 Ponsuke 3 9:00  13:00
4 Ponsuke 5 9:00    NaN
5 Ponsuke 6 18:00    NaN
6 Ponsuke 7 12:00    NaN
7 Ponsuke 8 12:00    NaN

Extract data that is not NaN in both columns

I want to extract
data
start End
x NaN NaN
x Not NaN NaN
x NaN Not NaN
o Not NaN Not NaN

Method


# [start]Or[End]How to delete data in one of the columns NaN
print(df.dropna(subset=['start', 'End']))
# [start][End]How to extract data where both columns are not NaN
print(df.query('start.notna() &End.notna()', engine='python'))

output


Name Count Start End
0 Ponsuke 1 9:00  18:00
2 Ponsuke 3 9:00  13:00

Extract data if either of the two columns is not NaN

I want to extract
data
start End
x NaN NaN
o Not NaN NaN
o NaN Not NaN
o Not NaN Not NaN

Method


#Make NaN an empty string[start][End]How to extract non-empty data by sticking columns together
print(df[df['start'].str.cat(df['End'], na_rep='') != ''])
# [start][End]How to extract data where either column is not NaN
print(df.query('start.notna() |End.notna()', engine='python'))

output


Name Count Start End
0 Ponsuke 1 9:00  18:00
1 Ponsuke 2 18:00    NaN
2 Ponsuke 3 9:00  13:00
4 Ponsuke 5 9:00    NaN
5 Ponsuke 6 18:00    NaN
6 Ponsuke 7 12:00    NaN
7 Ponsuke 8 12:00    NaN
8 Ponsuke 9 NaN 18:00

Extract data of "1 column is not NaN" & "1 column is NaN"

I want to extract
data
start End
x NaN NaN
x Not NaN NaN
o NaN Not NaN
x Not NaN Not NaN

Method


# [start]After the column extracts NaN[End]How to delete data whose column is NaN
print(df[df['start'].isna()].dropna(subset=['End']))
# [start]The column is not NaN and[End]How to extract data where the column is NaN
print(df.query('start.isna() &End.notna()', engine='python'))

output


Name Count Start End
8 Ponsuke 9 NaN 18:00

Recommended Posts

How to extract non-missing value nan data with pandas
How to extract non-missing value nan data with pandas
How to convert horizontally held data to vertically held data with pandas
How to extract null values and non-null values with pandas
How to deal with imbalanced data
How to Data Augmentation with PyTorch
How to extract features of time series data with PySpark Basics
Try converting to tidy data with pandas
How to read problem data with paiza
How to create sample CSV data with hypothesis
Try to aggregate doujin music data with pandas
How to scrape horse racing data with BeautifulSoup
Convert 202003 to 2020-03 with pandas
Data visualization with pandas
Data manipulation with Pandas!
Shuffle data with pandas
How to replace with Pandas DataFrame, which is useful for data analysis (easy)
How to use xgboost: Multi-class classification with iris data
How to scrape image data from flickr with python
How to scrape horse racing data using pandas read_html
How to access with cache when reading_json in pandas
How to get more than 1000 data with SQLAlchemy + MySQLdb
How to output CSV of multi-line header with pandas
How to convert JSON file to CSV file with Python Pandas
[Python] How to deal with pandas read_html read error
Extract the maximum value with pandas and change that value
Extract Twitter data with CSV
How to update with SQLAlchemy?
How to use Pandas Rolling
How to cast with Theano
How to Alter with SQLAlchemy?
How to separate strings with','
How to RDP with Fedora31
How to handle data frames
Data processing tips with Pandas
How to Delete with SQLAlchemy?
How to extract any appointment in Google Calendar with Python
Ingenuity to handle data with Pandas in a memory-saving manner
Try to extract Azure SQL Server data table with pyodbc
How to extract other than a specific index with Numpy
How to get an overview of your data in Pandas
How to read an Excel file (.xlsx) with Pandas [Python]
How to create dataframes and mess with elements in pandas
Data science companion in python, how to specify elements in pandas
[Introduction to Python] How to get data with the listdir function
Try to extract the features of the sensor data with CNN
How to cancel RT with tweepy
How to read e-Stat subregion data
How to write soberly in pandas
[Python] How to use Pandas Series
[Stock price analysis] Learn pandas with Nikkei 225 (004: Change read data to Nikkei 225)
How to use virtualenv with PowerShell
How to install python-pip with ubuntu20.04LTS
Versatile data plotting with pandas + matplotlib
How to install pandas on EC2 (How to deal with MemoryError and PermissionError)
How to get started with Scrapy
How to get started with Python
[Pandas] I tried to analyze sales data with Python [For beginners]
How to find the memory address of a Pandas dataframe value
<Pandas> How to handle time series data in a pivot table
How to deal with DistributionNotFound errors