保姆级教程 | Python科研数据分析专题(三)
缺失值填补
Sim_Jackson | 2023
导入必要的第三方库
# 导入需要的第三方库
import pandas as pd
import os
import warnings
warnings.filterwarnings('ignore')
dir_ = r'D:\科研论文\Python\数据分析'
os.chdir(dir_)
files = os.listdir(dir_) # 将该地址下的文件都列出来
files
['data.xlsx', 'google.csv', 'reaseach_data.xlsx']
# 数据读取
df = pd.read_excel(files[-1]) # -1即为倒数第一个,'reaseach_data.xlsx'
df
time | X1 | X2 | X3 | X4 | X5 | X6 | X7 | X8 | X9 | X10 | |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | 2020-01-01 | 7200.17 | NaN | NaN | 96.51 | NaN | NaN | 2.19 | 3 | 10 | 26 |
1 | 2020-01-02 | 6985.47 | 3257.85 | 1527.10 | 96.81 | 61.18 | 12.47 | 2.13 | 4 | 49 | 72 |
2 | 2020-01-03 | 7344.88 | 3234.85 | 1548.75 | 96.91 | 63.04 | 14.02 | 2.11 | 8 | 47 | 65 |
3 | 2020-01-04 | 7410.66 | NaN | NaN | NaN | NaN | NaN | NaN | 5 | 58 | 0 |
4 | 2020-01-05 | 7411.32 | NaN | NaN | NaN | NaN | NaN | NaN | 5 | 36 | 0 |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
1090 | 2022-12-27 | 16717.17 | 3829.25 | NaN | 104.20 | 79.87 | 21.65 | 5.21 | 47 | 58 | 0 |
1091 | 2022-12-28 | 16552.57 | 3783.22 | 1803.35 | 104.53 | 78.86 | 22.14 | 4.71 | 50 | 40 | 18 |
1092 | 2022-12-29 | 16642.34 | 3849.28 | 1813.75 | 103.97 | 78.71 | 21.44 | 4.56 | 51 | 67 | 44 |
1093 | 2022-12-30 | 16602.59 | 3839.50 | NaN | 103.49 | 80.51 | 21.67 | 4.43 | 42 | 53 | 25 |
1094 | 2022-12-31 | 16547.50 | NaN | NaN | NaN | NaN | NaN | NaN | 34 | 46 | 0 |
1095 rows × 11 columns
df.isna().sum() # 数据的缺失值情况
time 0
X1 0
X2 338
X3 344
X4 312
X5 319
X6 334
X7 315
X8 0
X9 0
X10 0
dtype: int64
提取出X2的非缺失的部分,即交易日数据
df['X2'].fillna(0,inplace=True) # 将空值填充为0,非交易日的SP500数据填补为0.
df
time | X1 | X2 | X3 | X4 | X5 | X6 | X7 | X8 | X9 | X10 | |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | 2020-01-01 | 7200.17 | 0.00 | NaN | 96.51 | NaN | NaN | 2.19 | 3 | 10 | 26 |
1 | 2020-01-02 | 6985.47 | 3257.85 | 1527.10 | 96.81 | 61.18 | 12.47 | 2.13 | 4 | 49 | 72 |
2 | 2020-01-03 | 7344.88 | 3234.85 | 1548.75 | 96.91 | 63.04 | 14.02 | 2.11 | 8 | 47 |