数据处理常用工具

发表于 | 分类于

配置环境

1
2
3
4
5
6
7
hostnamectl set-hostname slave3 && bash
vim /etc/hosts #windows: c:\windows\system32\drivers\etc\hosts
tzselect
crontab -e 
*/30 10-17 * * * /usr/sbin/ntpdate master
ssh-keygen -t rsa   #~/.ssh
ssh-copy-id -i ~/.ssh/id_rsa.pub slave3

Hadoop

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#init hadoop
hadoop namenode -format
start-all.sh

hdfs dfs -mkdir /crash
hdfs dfs -put /root/abc.txt /crash

#Map/Reduce(V2) Master端口号填dfs.datanode.ipc.address,默认50020
mapred-site.xml
<name>mapred.job.tracker</name>
yarn-site.xml
<name>yarn.resourcemanager.address</name>
<value>master:50020</value>
#DFS Master端口号填core-site.xml，fs.default.name is Deprecated
<name>fs.defaultFS</name>
<value>hdfs://master:9000</value>

Excel

1
2
3
4
5
6
7
8
9
10
=DATE(YEAR(C2),MONTH(C2),DAY(C2))
=EVALUATE(A2) #计算表达式
counta
锁定 $C$1 (快捷键F4) 连接：&
countifs(j1:j10,h1, k2:k10,">="&$k14)
countifs() sumifs() if() 
vlookup()最后一个参数flase精确匹配，true时模糊匹配（搜索范围内的值是需要按升序排序的，左闭右开）
match(o15, a1:a10,0)找行列 index(a1:a10, 3, 5)指定行列的值(0表示整行/列)
数据-》分列，列数据格式选日期YMD，目标区域
日期处理
1
2
3
4
TEXT(e7,"yyyy-MM-dd")
RIGHT(e3, 2*len(e3)-lenb(e3))
LEFT(e3, lenb(e3)-len(e3))
MIDB(e3, searchb("?", e3), 2*len(e3)-lenb(e3))
数据分析
1
2
3
4
5
6
7
8
9
Ctrl+shift+上箭头 选中需要填充列-> ctrl + D快速填充；ctrl + E智能提取
条件格式-》项目选取规则-》文本包含，筛选-》颜色筛选
数据-》插入下拉列表
按什么字段分析，将其拉到行，如果只是针对某个值分析，添加到筛选器，选择对应的值进行筛选
值显示方式-》总计的百分比
点击求和项-》值字段设置-》计数
透视表-》右键-》组合 （选择按月统计）
1.添加字段     单元格-》分析-》字段、项目-》计算字段
2.插入切片器（筛选）  单元格-》分析-》插入切片器
图形
1
2
进度条：条件格式-》突出显示单元格规则-》其他规则-》基于各自值设置所有单元格格式-》（格式样式：选数据条，最小最大值：选数字，填充：渐变填充）
视图-》网格线

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
systemctl start mysqld
limit n, m; n+1开始返回m行
having 对group by之后的数据进行过滤，对统计结果筛选
select (math+chinese+english) as total from score
select avg(math) as m from score group by class having m > 80;
select english, (case when a in (1,3,5) then
     when english > 90 then 'A'
     when english > 69 then 'b'
     else 'C' end) from score;
select column from table1 [inner/left/right/full] join table2 on t1.c1 = t2.c2
Mysql不支持full join，通过left union right方式
union去重，union all保留重复值

CREATE VIEW table3 AS 
select u1.user_name as user_name, u2.user_name as f_name from friend f
left join user u1 on f.user_id = u1.user_id 
left join user u2 on f.friend_id =u2.user_id

Python

Numpy：科学计算
1
2
3
4
5
6
arr = np.array() arr.ndim 维度 arr.size 元素个数
arr[0:2,1:2] #二维数组切片
np.add/substract/multiply/divide
type = np.dtype([('name', 'S20'), ('age', 'i1'), ('kpi', 'f4')])
arr = np.array([('Mary', 22, 95.5)], dtype=type)
np.sort(arr, order='kpi')
Pandas：数据分析，基于Numpy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
pd.set_option('display.unicode.east_asian_width', True) #设置对齐
pd.read_excel()
df.loc['Mary'] #行名  df.iloc[0, 1]#索引
df.loc[:'增加列']=[1,2,3]
df.insert(2,'增加列',[1,2,3])
df['增加列']=[1,2,3]
df.loc['增加行']=[a,b,c]
df.append(df_insert)
df.rename(columns={'old':'new'}, inplace=True)
df.drop(columns='', inplace=True)
df.drop(df[(df['hour'] <= 6 | df['hour'] >= 23)].index, inplace=True)
df.isnull().sum() #查缺失值
df.info() #查看数据类型
df.describe() #数值类型的统计量，均值、最大最小值等
df.dropna() #删除缺失值
df['column']=df['column'].fillna(method='ffill') #填充缺失值为上一个非缺失值
df['sum'] = df.sum(axis=1)#按行求和
df.append(pd.Series(df.mean(),name='mean'))#max/min
house['price'] = house['price'].map(lambda d: d.replace('per', ''))
house['price'] = house['price'].astype(float)
df['列名'].value_counts()['某个值'] #统计值的个数
pd.to_datetime(df['time'], format='%Y-%m-%d %H:%M:%S').dt.strftime('%H:%M:%S')
df.groupby('lineID')['stationID'].nunique() #统计每条线路站点数
df.groupby('lineID')['stationID'].value_counts() #线路各站点记录数
df.sample(n=19000, axis=0) #按行随机抽取19000条数据
Fraud.loc[:, Fradu.columns != 'fraud'].corr() #计算相关性
Matplotlib：数据可视化绘图
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
plt.figure(figsize=(6,4),facecolor='c')
plt.plot([x0,x1], [y0,y1], color='r', linestyle='-', marker='o')
plt.title('title', fontsize=18)
plt.legend(('legend',), loc='lower right', fontsize=10)
plt.xlabel('xlabel') #ylabel
plt.xlim(0,14) #ylim
plt.grid(color='0.5', linestyle='--', linewidth=1) #网格
plt.boxplot([d1,d2,d3], labels=label) #箱图
for a,b in zip(x,y):
  plt.text(a, b, '%d'%b, ha='center', va=bottom, fontsize=12)
plt.annotate('annote', xy=(10, 4), xytext(11.5, 2.5), color='g', fontsize=14, arrowprops=dict(facecolor='g', shrink=0.08))

sns.distplot(data['repeat'][data['fraud'] == 1], bins=10)

from sklearn.decomposition import PCA
sklearn.decomposition.PCA(n_components=None, copy=True, whiten=False, svd_solver='auto', tol=0.0, iterated_power='auto', random_state=None)