Master Pandas: Essential Data Reading, Cleaning, and Merging Techniques

Background Introduction

Many readers ask how to process data, split columns, read JSON into Python, and similar tasks. The author suggests using the Pandas library, which is designed for data preprocessing in data science.

Data Read/Write

# Read data from MySQL database
import pymysql
conn = pymysql.connect(host='localhost', user='root', password='test',
                       database='test', port=3306, charset='utf8')
user = pd.read_sql('select * from topy', conn)
conn.close()
User

Data Cleaning

# Data reading
sec_cars = pd.read_table(r'C:\Users\Administrator\Desktop\sec_cars.csv', sep=',')
sec_cars.head()
print('Dataset shape:
', sec_cars.shape)
print('Data types:
', sec_cars.dtypes)
sec_cars.describe()

# Missing value detection
print('Missing values present:
', any(df.isnull()))
# Delete rows with missing values
df.dropna()
# Delete a column
df.drop('age', axis=1)
# Forward fill
df.fillna(method='ffill')
# Backward fill
df.fillna(method='bfill')
# Fill with constant
df.fillna(value=0)
# Fill with statistical values
df.fillna(value={'gender': df.gender.mode()[0],
                'age': df.age.mean(),
                'income': df.income.median()})

Type Conversion and Operations

# Read Excel file
df = pd.read_excel(r'C:\Users\Administrator\Desktop\data_test03.xlsx')
# Convert birthday to datetime
df.birthday = pd.to_datetime(df.birthday, format='%Y/%m/%d')
# Convert phone number to string
df.tel = df.tel.astype('str')
# Add age and workage columns
df['age'] = pd.datetime.today().year - df.birthday.dt.year
df['workage'] = pd.datetime.today().year - df.start_work.dt.year
# Mask middle four digits of phone number
df.tel = df.tel.apply(lambda x: x.replace(x[3:7], '****'))
# Extract email domain
df['email_domain'] = df.email.apply(lambda x: x.split('@')[1])
# Extract profession information
df['profession'] = df.other.str.findall('专业：(.*?)，')
# Drop unnecessary columns
df.drop(['birthday', 'start_work', 'other'], axis=1, inplace=True)

Data Merge, Join and Summarize

# Construct dataframes df1 and df2
df1 = pd.DataFrame({'name': ['张三', '李四', '王二'],
                    'age': [21, 25, 22],
                    'gender': ['男', '女', '男']})
df2 = pd.DataFrame({'name': ['丁一', '赵五'],
                    'age': [23, 22],
                    'gender': ['女', '女']})
# Vertical concatenation
pd.concat([df1, df2], keys=['df1', 'df2'])

# If df2 uses a different column name
df2 = pd.DataFrame({'Name': ['丁一', '赵五'],
                    'age': [23, 22],
                    'gender': ['女', '女']})
pd.concat([df1, df2])

# Additional dataframes for merging
df3 = pd.DataFrame({'id': [1, 2, 3, 4, 5],
                    'name': ['张三', '李四', '王二', '丁一', '赵五'],
                    'age': [27, 24, 25, 23, 25],
                    'gender': ['男', '男', '男', '女', '女']})
df4 = pd.DataFrame({'Id': [1, 2, 2, 4, 4, 4, 5],
                    'score': [83, 81, 87, 75, 86, 74, 88],
                    'kemu': ['科目1', '科目1', '科目2', '科目1', '科目2', '科目3', '科目1']})
df5 = pd.DataFrame({'id': [1, 3, 5],
                    'name': ['张三', '王二', '赵五'],
                    'income': [13500, 18000, 15000]})
# Merge df3 and df4 (left join)
merge1 = pd.merge(left=df3, right=df4, how='left',
                  left_on='id', right_on='Id')
# Merge the result with df5
merge2 = pd.merge(left=merge1, right=df5, how='left')
merge2