PySpark之金融数据分析（Spark RDD、SQL练习题）

目录

一、数据来源

二、PySpark RDD编程

1、查询特定日期的资金流入和流出情况

2、活跃用户分析

三、PySpark SQL编程

1、按城市统计2014年3月1日的平均余额

2、统计每个城市总流量前3高的用户

四、总结

一、数据来源

本文使用的数据来源于天池大赛数据集，由蚂蚁金服提供，包含用户基本信息、申购赎回记录、收益率、银行间拆借利率等多个维度，本文通过PySpark实现对该数据集的简单分析。

二、PySpark RDD编程

数据都已上传到HDFS的/data目录下，对于分析结果，保存至/output目录下。

1、查询特定日期的资金流入和流出情况

使用user_balance_table，计算出所有用户在每一天的总资金流入和总资金流出量。

输出格式如下：

<日期> <资金流入量> <资金流出量>

代码如下：

  1. from pyspark.sql.functions import col, mean, round, row_number, sum
  2. from pyspark import SparkConf, SparkContext
  3. from pyspark.sql import SparkSession
  4. from pyspark.sql.window import Window
  5. from datetime import datetime
  6.  
  7. # 创建对象
  8. conf = SparkConf().setAppName("data analysis")
  9. sc = SparkContext(conf=conf)
  10.  
  11. # 读取CSV文件为RDD
  12. lines_rdd = sc.textFile("/data/user_balance_table.csv")
  13. # 获取表头
  14. header = lines_rdd.first()
  15. data_rdd = lines_rdd.filter(lambda row: row != header)
  16. # 每行转为列表
  17. split_rdd = data_rdd.map(lambda line: line.split(","))
  18.  
  19. # 提取列索引
  20. extracted_rdd = split_rdd.map(lambda fields: (fields[1], (int(fields[4]), int(fields[8]))))
  21. # 分组聚合，分别计算流入和流出总额
  22. aggregated_rdd = extracted_rdd.reduceByKey(lambda x, y: (x[0] + y[0], x[1] + y[1]))
  23. # 结果转换
  24. result_rdd = aggregated_rdd.map(lambda x: "\t".join([x[0], str(x[1][0]), str(x[1][1])]))
  25.  
  26. # 插入表头行
  27. final_rdd = sc.parallelize(["日期\t资金流入量\t资金流出量"]) + result_rdd
  28. final_rdd.saveAsTextFile("/output/result1.txt")
  29.  
  30. print("已完成1、查询特定日期的资金流入和流出情况")

输出结果如下：

  1. 日期	资金流入量	资金流出量
  2. 20140805	394780870	221706539
  3. 20140808	233903717	311648757
  4. 20140811	331550471	418603336
  5. 20140814	257702660	211939431
  6. 20140820	308378692	202452782
  7. 20140823	141412027	199377531
  8. 20140826	306945089	285478563
  9. 20140829	267554713	273756380
  10. 20140830	199708772	196374134
  11. 20140827	302194801	468164147
  12. 20140821	251763517	219963356
  13. 20140818	298499146	259169016
  14. 20140815	244551620	236516007
  15. 20140812	258493673	309754858
  16. 20140803	173825397	127125217
  17. 20140728	371762756	345986909
  18. 20140725	181641088	262874791
  19. 20140722	243084133	369043423
  20. 20140719	210318023	155464283
  21. 20140716	394890140	234775948
  22. 20140713	179759885	199459990
  23. 20140710	283095921	326009240
  24. 20140707	272182847	317612569
  25. 20140409	383347565	289330278
  26. 20140403	363877120	266605457
  27. 20140331	398884905	423852634
  28. 20140328	225966355	405443946
  29. 20140831	275090213	292943033
  30. 20140825	309574223	312413411
  31. 20140822	246316056	179349206
  32. 20140816	215059736	219214339
  33. 20140813	261506619	303975517
  34. 20140810	259534870	189909225
  35. 20140804	330640884	322907524
  36. 20140729	228093046	303480103
  37. 20140723	265461894	308353077
  38. 20140717	253011280	298279385
  39. 20140714	254797524	284753279
  40. 20140711	208671021	240050748
  41. 20140708	224240103	340453063
  42. 20140630	334054112	456547794
  43. 20140321	282351818	259655286
  44. 20140324	313180334	437825259
  45. 20140402	355347118	272612066
  46. 20140414	309853269	415986984
  47. 20140417	355792647	265341592
  48. 20140420	191259529	161057781
  49. 20140501	193045106	143362755
  50. 20140504	303087562	413222034
  51. 20140513	275241493	257918375
  52. 20140519	259077930	293791406
  53. 20140522	344636549	251108485
  54. 20140603	270887462	385622582
  55. 20140612	332365185	236467885
  56. 20140615	166080126	116623756
  57. 20140323	167456369	186443311
  58. 20140326	272935544	450254233
  59. 20140329	160250985	155006056
  60. 20140407	196936223	176966561
  61. 20140410	386567460	286914864
  62. 20140416	387847838	255914640
  63. 20140422	285248757	268810141
  64. 20140425	220927432	227764292
  65. 20140506	318002728	341108696
  66. 20140515	313367089	238307643
  67. 20140524	160073254	154409868
  68. 20140527	299049555	321965845
  69. 20140530	226547701	312802179
  70. 20140605	380042567	355645445
  71. 20140614	181574530	229916191
  72. 20140623	232227670	373779624
  73. 20140415	428681231	285293076
  74. 20140427	146837951	191915377
  75. 20140502	125336258	121222064
  76. 20140505	370924149	309330781
  77. 20140508	392838756	273187499
  78. 20140514	305474522	316225442
  79. 20140517	170983868	145854372
  80. 20140526	344890868	274707572
  81. 20140529	320549863	313827800
  82. 20140622	147180819	361285652
  83. 20140625	264663201	547295931
  84. 20140628	153826161	283400236
  85. 20140706	199569025	195530758
  86. 20140709	278005555	269642881
  87. 20140712	177644343	149081488
  88. 20140718	208959595	208671287
  89. 20140724	277044480	347622431
  90. 20140727	151406251	166610652
  91. 20140730	209917272	250117716
  92. 20140802	189092130	172250225
  93. 20140217	706118060	405809267
  94. 20131120	166163002	86951218
  95. 20131111	147986439	227124951
  96. 20131102	62132300	26705506
  97. 20131024	69530830	58183921
  98. 20131015	85704304	35099198
  99. 20131012	81901136	31322983
  100. 20131013	71149981	31850475
  101. 20131029	102050144	43052468
  102. 20131104	300027403	130970051
  103. 20131110	164813471	85293644
  104. 20131113	174554976	79121440
  105. 20131116	118085705	28996272
  106. 20131119	131365419	70308235
  107. 20131122	128528687	82717921
  108. 20131125	147143602	70113127
  109. 20131203	268666856	101662045
  110. 20131209	174652812	133355299
  111. 20131214	129045596	52234651
  112. 20131208	124886903	76314292
  113. 20131205	190825287	118256819
  114. 20131124	102394991	85555947
  115. 20131121	221166938	74733962
  116. 20131118	195766477	136916782
  117. 20131112	148469647	131263524
  118. 20131106	181474910	131707820
  119. 20131028	129993375	55089464
  120. 20131025	69936453	52225802
  121. 20131022	145611496	96172537
  122. 20131019	53148443	26684697
  123. 20140216	331324628	183057224
  124. 20131216	196489931	156157326
  125. 20131213	164672690	90778214
  126. 20131210	179471612	147147341
  127. 20131207	138882605	71916047
  128. 20131123	89109018	48882131
  129. 20140223	337053711	195214052
  130. 20140313	346286910	316973542
  131. 20140310	497338076	308040624
  132. 20140317	339008082	440624592
  133. 20140314	315897431	311575572
  134. 20140311	430500816	496039886
  135. 20140302	276202230	246199417
  136. 20140225	563505889	299155352
  137. 20140309	244752519	206312503
  138. 20140315	287407002	242799048
  139. 20140318	435479117	410240726
  140. 20140221	353981687	178914448
  141. 20140218	593563145	271125324
  142. 20140212	763009770	354585919
  143. 20140209	341911234	262954049
  144. 20140206	227860276	111635336
  145. 20140205	209043990	95407704
  146. 20140208	358255468	183432237
  147. 20140211	818803205	243058162
  148. 20140220	478925191	230855410
  149. 20140207	578818221	273482213
  150. 20140201	64287172	61527060
  151. 20140117	345426853	127385210
  152. 20140116	374947083	153167426
  153. 20140119	185082022	117629351
  154. 20140125	440854623	185524856
  155. 20140127	657199484	280645861
  156. 20131220	133867400	153678963
  157. 20131217	193980797	116537843
  158. 20140112	228255344	141505812
  159. 20131221	112000779	83151594
  160. 20131222	154543217	83998249
  161. 20131228	192808006	61383220
  162. 20140102	434956739	190155450
  163. 20140105	206030707	156781996
  164. 20140113	447207050	178923772
  165. 20140110	237797636	117259153
  166. 20140107	589726496	137972793
  167. 20131224	305406334	118607911
  168. 20130824	44367268	14316598
  169. 20131011	82674757	27666473
  170. 20131002	11562708	7233874
  171. 20130930	28398050	30181353
  172. 20130921	32406340	26596179
  173. 20130906	38923186	33417903
  174. 20130831	47655303	22012016
  175. 20130828	46602319	33696861
  176. 20130816	41683536	33739844
  177. 20130813	48720919	28790328
  178. 20130804	45745254	8263965
  179. 20130729	53512076	18599364
  180. 20130723	58136147	24404051
  181. 20131010	72094613	52244531
  182. 20130820	53675509	30131225
  183. 20130817	17670519	4674983
  184. 20130811	55519543	15372680
  185. 20130805	43632203	15797507
  186. 20130730	47481243	13048582
  187. 20130724	48422518	36258592
  188. 20130725	57433418	38212836
  189. 20130731	54569637	9534040
  190. 20130803	21595789	20701797
  191. 20130806	50866184	16401387
  192. 20130815	42421222	52659327
  193. 20130818	59703092	15218300
  194. 20130827	45268028	60137633
  195. 20130830	56488844	20630752
  196. 20130908	38796909	14919122
  197. 20130911	98944459	71674082
  198. 20130914	30975443	36312660
  199. 20131004	19907689	23412350
  200. 20130904	33366708	27452600
  201. 20130907	34076183	20344424
  202. 20130910	94684143	37128363
  203. 20130919	24778048	11418512
  204. 20131003	10101194	6223263
  205. 20131009	72422299	33999376
  206. 20130711	44075197	3508800
  207. 20130714	22615303	2784107
  208. 20130720	20439079	4601143
  209. 20130718	24234505	11765356
  210. 20130715	48128555	13107943
  211. 20130712	34183904	8492573
  212. 20130706	36751272	1616635
  213. 20130710	30696506	2597169
  214. 20130713	15164717	3482829
  215. 20130719	33680124	9244769
  216. 20130722	40448896	19144267
  217. 20140817	149978271	139564084
  218. 20140824	130195484	191080151
  219. 20140809	160262764	163611708
  220. 20140806	288821016	282346594
  221. 20140731	191728916	277194379
  222. 20140704	211649838	264494550
  223. 20140412	177642053	123295320
  224. 20140406	129477254	139576683
  225. 20140325	314345006	312710515
  226. 20140322	191700135	138039412
  227. 20140828	245082751	297893861
  228. 20140819	266401973	254929877
  229. 20140807	247646474	253659514
  230. 20140801	374884735	252540858
  231. 20140726	128268053	282653341
  232. 20140720	176449304	174462836
  233. 20140705	169383796	272535138
  234. 20140702	384555819	328950951
  235. 20140327	266231082	359071642
  236. 20140330	205533934	264714811
  237. 20140405	202336542	163199682
  238. 20140408	354770149	250015131
  239. 20140411	237829882	277077434
  240. 20140423	313677307	278470936
  241. 20140426	151625415	158122962
  242. 20140429	330607104	307578349
  243. 20140507	417327518	239372999
  244. 20140510	287240171	147248328
  245. 20140516	231967423	282094916
  246. 20140525	166943526	231004758
  247. 20140528	276134813	415891684
  248. 20140531	146823669	142862063
  249. 20140606	301413900	274862067
  250. 20140609	366114374	287520152
  251. 20140618	335262709	421920230
  252. 20140621	177999186	231003775
  253. 20140624	245450766	428471509
  254. 20140627	264282703	399444352
  255. 20140320	365011495	336076380
  256. 20140401	453320585	277429358
  257. 20140404	251895894	200192637
  258. 20140413	208172985	178934722
  259. 20140419	268729366	146374940
  260. 20140428	324937272	327724735
  261. 20140503	185094488	199568043
  262. 20140509	281479009	247743971
  263. 20140512	325108597	293952908
  264. 20140518	164419642	153440019
  265. 20140521	297799722	250223726
  266. 20140602	158219402	170409506
  267. 20140608	302171269	169525332
  268. 20140611	327661453	246127540
  269. 20140617	270350693	502560223
  270. 20140620	251582530	286583065
  271. 20140626	297628039	418742109
  272. 20140629	158801540	261170799
  273. 20140701	384428753	374164541
  274. 20140418	239300383	225952909
  275. 20140421	301134667	295635256
  276. 20140424	318358891	224536754
  277. 20140430	260091330	281835975
  278. 20140511	182424063	152945581
  279. 20140520	453955303	260040720
  280. 20140523	249546195	229000787
  281. 20140601	183489775	149829253
  282. 20140604	274460744	303978838
  283. 20140607	187801995	146203577
  284. 20140610	354031597	298190025
  285. 20140613	216923770	386799040
  286. 20140616	387308484	492349489
  287. 20140619	338609087	284956260
  288. 20140703	297894643	289009780
  289. 20140715	334810012	261722182
  290. 20140721	378088594	434191479
  291. 20140213	626698794	362757986
  292. 20131117	111786622	42358155
  293. 20131114	167996058	52554150
  294. 20131108	231749471	92856307
  295. 20131105	180904814	82689864
  296. 20131030	109663260	63908701
  297. 20131027	68007543	46520570
  298. 20131021	106030290	45781403
  299. 20131018	98151450	15938355
  300. 20131014	130315300	40652625
  301. 20131017	87203217	23274764
  302. 20131020	47766681	50884342
  303. 20131023	106093112	73788462
  304. 20131026	55752506	49357211
  305. 20131101	150904945	66933119
  306. 20131107	167963464	83306452
  307. 20131128	139760425	61453591
  308. 20131206	152197159	82625571
  309. 20131212	140360007	92090275
  310. 20131215	205882387	54757146
  311. 20140215	255393179	121463693
  312. 20131211	239916977	110314592
  313. 20131202	345313258	128465086
  314. 20131130	116865492	119660311
  315. 20131127	134774128	86129498
  316. 20131115	124270704	58210254
  317. 20131109	126467028	45885771
  318. 20131103	68707870	30895754
  319. 20131031	90926701	55607833
  320. 20131016	100948091	51261982
  321. 20131204	219456372	69981329
  322. 20131201	137889992	94632233
  323. 20131129	122536344	123982166
  324. 20131126	258037702	65802983
  325. 20140226	680801145	284819682
  326. 20140301	362865580	211279011
  327. 20140304	524146340	250562978
  328. 20140319	359014064	429298917
  329. 20140316	269387391	390584547
  330. 20140307	380139779	291087220
  331. 20140308	243274169	140323202
  332. 20140305	454295491	209072753
  333. 20140227	672909288	492786036
  334. 20140224	656317045	473470156
  335. 20140204	83653646	44927333
  336. 20140222	282022706	134735118
  337. 20140228	428721754	322030204
  338. 20140303	505305862	513017360
  339. 20140306	561787770	243149884
  340. 20140312	377007897	416491268
  341. 20140203	84329848	46109194
  342. 20140131	87324175	48132389
  343. 20140202	57912761	24395720
  344. 20140214	490710434	263261899
  345. 20140219	408367966	323990451
  346. 20140210	647465140	431753411
  347. 20140126	528402520	331752519
  348. 20130929	34433848	40295202
  349. 20140129	952479658	215164666
  350. 20140123	481450097	209024328
  351. 20140120	335761027	442996084
  352. 20140114	356907128	159778389
  353. 20130721	21142394	2681331
  354. 20140122	498730606	292856188
  355. 20140128	843424742	248334316
  356. 20140130	227916211	105288105
  357. 20140124	614103894	345829001
  358. 20140121	412854611	302825136
  359. 20140118	233200688	142869842
  360. 20140115	391408718	177618247
  361. 20131229	161473347	55129989
  362. 20131226	220287409	241852564
  363. 20140103	342074805	127714255
  364. 20131223	198747367	138478245
  365. 20140106	442494042	190917629
  366. 20140109	280698487	140391237
  367. 20131218	180215804	202220872
  368. 20131219	233631357	164023344
  369. 20131225	380076148	172755480
  370. 20131231	265851934	134232530
  371. 20140108	264025160	213880074
  372. 20140111	243403438	71530182
  373. 20140104	186085910	99869074
  374. 20140101	330926565	77367755
  375. 20131230	286358778	179826624
  376. 20131227	211118967	163522548
  377. 20130925	47274168	74838162
  378. 20131008	109806912	77856096
  379. 20131005	26364686	13966453
  380. 20130927	42166121	27639603
  381. 20130924	74250859	56680792
  382. 20130918	42926608	47203221
  383. 20130915	37059683	25020715
  384. 20130912	68573684	45147220
  385. 20130909	49312473	45621186
  386. 20130903	80507880	39328144
  387. 20130825	43306288	18117808
  388. 20130822	89130737	25000672
  389. 20130819	109184209	28339260
  390. 20130810	20615307	26614408
  391. 20130807	43908081	29708706
  392. 20130801	53369962	18864468
  393. 20130726	44721817	39192369
  394. 20130829	47518666	35944968
  395. 20130901	56239802	59339949
  396. 20130826	74496541	20637986
  397. 20130823	37878575	25680323
  398. 20130814	30541167	15031683
  399. 20130808	44493490	29551691
  400. 20130802	38064536	40150769
  401. 20130727	17194451	15058893
  402. 20130728	36255382	7683211
  403. 20130809	33425186	30131015
  404. 20130812	94520502	28586669
  405. 20130821	38184446	29983342
  406. 20130902	140844739	17785524
  407. 20130905	38716505	21800904
  408. 20130917	76204815	58260798
  409. 20130920	28365762	15188254
  410. 20130923	54658160	58285879
  411. 20130926	68718693	48467529
  412. 20131001	19137499	12813267
  413. 20131007	42797733	28894400
  414. 20130913	71655946	60512675
  415. 20130916	161656210	45184589
  416. 20130922	67514169	47962055
  417. 20130928	39995798	58105720
  418. 20131006	38730653	13464528
  419. 20130717	29015682	10911513
  420. 20130705	11648749	2763587
  421. 20130702	29037390	2554548
  422. 20130708	57258266	8347729
  423. 20130709	26798941	3473059
  424. 20130703	27270770	5953867
  425. 20130701	32488348	5525022
  426. 20130704	18321185	6410729
  427. 20130707	8962232	3982735
  428. 20130716	50622847	11864981

2、活跃用户分析

使用 user_balance_table ，定义活跃用户为在指定月份内有至少5天记录的用户，统计2014年8月的活跃用户总数。

输出格式如下：

<活跃用户总数>

代码如下：

  1. # 提取user_id、report_date字段
  2. mapped_rdd = split_rdd.map(lambda fields: (fields[0], datetime.strptime(fields[1], "%Y%m%d")))
  3. # 筛选出2014年8月的记录
  4. filtered_rdd = mapped_rdd.filter(lambda x: x[1].year == 2014 and x[1].month == 8)
  5. # 聚合
  6. grouped_rdd = filtered_rdd.groupByKey()
  7. # 统计记录数量，过滤活跃用户
  8. active_users_rdd = grouped_rdd.filter(lambda x: len(list(x[1])) >= 5)
  9.  
  10. # 统计活跃用户总数
  11. total_active_users = active_users_rdd.map(lambda x: x[0]).distinct().count()
  12.  
  13. print("已完成2、活跃用户分析\n2014年8月期间的活跃用户总数为：", total_active_users)

三、PySpark SQL编程

1、按城市统计2014年3月1日的平均余额

计算每个城市在2014年3月1日的用户平均余额(tBalance)，按平均余额降序排列。

输出格式如下：

<城市ID> <平均余额>

代码如下：

  1. spark = SparkSession.builder.getOrCreate()
  2. # 从HDFS读取CSV文件
  3. df_bal = spark.read.csv("/data/user_balance_table.csv", header=True, inferSchema=True)
  4. df_city = spark.read.csv("/data/user_profile_table.csv", header=True, inferSchema=True)
  5.  
  6. df = df_bal.select("user_id", "report_date", "tBalance")\
  7.            .filter(col("report_date") == "20140301")\
  8.            .join(df_city.select("user_id","city"), on="user_id")
  9.  
  10. result_df = df.groupBy("city").agg(mean(col("tBalance")).cast("int").alias("tBalance_mean"))\
  11.            .sort(col("tBalance_mean").desc())
  12.  
  13. # 将结果转换为RDD，并进行格式调整（添加表头，并以tab分隔每列）
  14. data_rdd = result_df.rdd.map(lambda row: "\t".join(map(str, row)))
  15. result2 = sc.parallelize(["城市ID\t平均余额"] + data_rdd.collect())
  16. result2.saveAsTextFile("/output/result2.txt")
  17. print("已完成1、按城市统计2014年3月1日的平均余额")

输出结果如下：

  1. 城市ID	平均余额
  2. 6281949	2795923
  3. 6301949	2650775
  4. 6081949	2643912
  5. 6481949	2087617
  6. 6411949	1929838
  7. 6412149	1896363
  8. 6581949	1526555

2、统计每个城市总流量前3高的用户

统计每个城市中每个用户在2014年8月的总流量(定义为total purchase_amt+total_redeem_amt)，并输出每个城市总流量排名前三的用户ID及其总流量。

输出格式如下：

<城市ID> <用户ID> <总流量>

代码如下：

  1. windowSpec = Window.partitionBy("city").orderBy(col("total_amt").desc())
  2. df = df_bal.select("user_id", "report_date", "total_purchase_amt", "total_redeem_amt")\
  3.            .filter(df_bal.report_date.like("201408%"))\
  4.            .join(df_city.select("user_id","city"), on="user_id")
  5.  
  6. result_df = df.groupBy("city", "user_id") \
  7.             .agg((sum(col("total_purchase_amt")) + sum(col("total_redeem_amt"))).alias("total_amt"))\
  8.             .filter(col("total_amt") > 0)\
  9.             .withColumn("rn", row_number().over(windowSpec))\
  10.             .filter(col("rn") <= 3)\
  11.             .sort(col("city"), col("rn"))\
  12.             .select("city", "user_id", "total_amt")
  13.  
  14. # 输出结果
  15. data_rdd = result_df.rdd.map(lambda row: "\t".join(map(str, row)))
  16. result3 = sc.parallelize(["城市ID\t用户ID\t总流量"] + data_rdd.collect())
  17. result3.saveAsTextFile("/output/result3.txt")
  18. print("已完成2、统计每个城市总流量前3高的用户")
  19.  
  20. sc.stop()
  21. spark.stop()

输出结果如下：

  1. 城市ID	用户ID	总流量
  2. 6081949	27235	108475680
  3. 6081949	27746	76065458
  4. 6081949	18945	55304049
  5. 6281949	15118	149311909
  6. 6281949	11397	124293438
  7. 6281949	25814	104428054
  8. 6301949	2429	109171121
  9. 6301949	26825	95374030
  10. 6301949	10932	74016744
  11. 6411949	662	75162566
  12. 6411949	21030	49933641
  13. 6411949	16769	49383506
  14. 6412149	22585	200516731
  15. 6412149	14472	138262790
  16. 6412149	25147	70594902
  17. 6481949	12026	51161825
  18. 6481949	670	49626204
  19. 6481949	14877	34488733
  20. 6581949	9494	38854436
  21. 6581949	26876	23449539
  22. 6581949	21761	21136440

四、总结

RDD编程主要练习了filter、map、reduceByKey、saveAsTextFile、groupByKey等算子的使用，Spark SQL编程主要练习了DataFrame操作、聚合函数、窗口函数等内容。

注：本文转载自blog.csdn.net的唯余木叶下弦声的文章"https://blog.csdn.net/weixin_44458771/article/details/145260559"。版权归原作者所有，此博客不拥有其著作权，亦不承担相应法律责任。如有侵权，请联系我们删除。
复制链接
相关推荐
发表评论

评论记录：

未查询到任何数据！