jqgrid如何渲染表格数据_如何用Pandas花式拼接表格数据

源&＃xff1a;菜鸟学Python

通常来说&＃xff0c;我们需要的数据不可能都来自同一张表格&＃xff0c;所以了解如何对不同格式的表格进行拼接、合并是非常重要的。

本文将介绍Pandas库中常用的合并表格的方法&＃xff0c;包括.append(), pd.concat(), pd.merge(), 并配合实例进行讲解。

01上下拼接

用.append()【1】方法可以实现表格的上下拼接&＃xff0c;一般来说它们会有相同的列名&＃xff0c;比如&＃xff0c;上下拼接两只股票的日线数据。

import tushare as tsimport pandas as pdpd.set_option(&＃39;expand_frame_repr&＃39;, False) # 显示所有列ts.set_token(&＃39;your token&＃39;)pro &＃61; ts.pro_api()df &＃61; pro.daily(ts_code&＃61;&＃39;000001.SZ&＃39;, start_date&＃61;&＃39;20180701&＃39;, end_date&＃61;&＃39;20180705&＃39;)df1 &＃61; pro.daily(ts_code&＃61;&＃39;000002.SZ&＃39;, start_date&＃61;&＃39;20180701&＃39;, end_date&＃61;&＃39;20180705&＃39;)print(df)print(df1) ts_code trade_date open high low close pre_close change pct_chg vol amount0 000001.SZ 20180705 8.62 8.73 8.55 8.60 8.61 -0.01 -0.12 835768.77 722169.5791 000001.SZ 20180704 8.63 8.75 8.61 8.61 8.67 -0.06 -0.69 711153.37 617278.5592 000001.SZ 20180703 8.69 8.70 8.45 8.67 8.61 0.06 0.70 1274838.57 1096657.0333 000001.SZ 20180702 9.05 9.05 8.55 8.61 9.09 -0.48 -5.28 1315520.13 1158545.868 ts_code trade_date open high low close pre_close change pct_chg vol amount0 000002.SZ 20180705 23.02 23.41 22.85 23.05 23.00 0.05 0.22 267278.61 619393.0071 000002.SZ 20180704 23.46 23.75 23.00 23.00 23.42 -0.42 -1.79 249881.03 582470.2142 000002.SZ 20180703 23.10 23.48 22.80 23.42 22.80 0.62 2.72 549964.88 1274023.5753 000002.SZ 20180702 24.50 24.55 22.52 22.80 24.60 -1.80 -7.32 846203.86 1981131.638print(df.append(df1)) ts_code trade_date open high low close pre_close change pct_chg vol amount0 000001.SZ 20180705 8.62 8.73 8.55 8.60 8.61 -0.01 -0.12 835768.77 722169.5791 000001.SZ 20180704 8.63 8.75 8.61 8.61 8.67 -0.06 -0.69 711153.37 617278.5592 000001.SZ 20180703 8.69 8.70 8.45 8.67 8.61 0.06 0.70 1274838.57 1096657.0333 000001.SZ 20180702 9.05 9.05 8.55 8.61 9.09 -0.48 -5.28 1315520.13 1158545.8680 000002.SZ 20180705 23.02 23.41 22.85 23.05 23.00 0.05 0.22 267278.61 619393.0071 000002.SZ 20180704 23.46 23.75 23.00 23.00 23.42 -0.42 -1.79 249881.03 582470.2142 000002.SZ 20180703 23.10 23.48 22.80 23.42 22.80 0.62 2.72 549964.88 1274023.5753 000002.SZ 20180702 24.50 24.55 22.52 22.80 24.60 -1.80 -7.32 846203.86 1981131.638

表格df的数据在上&＃xff0c;表格df1的数据在下&＃xff0c;注意到拼接之后的索引并没有随新表更新&＃xff0c;这一问题可以通过设置参数ignore_index&＃61;True来解决。

print(df.append(df1, ignore_index&＃61;True)) ts_code trade_date open high low close pre_close change pct_chg vol amount0 000001.SZ 20180705 8.62 8.73 8.55 8.60 8.61 -0.01 -0.12 835768.77 722169.5791 000001.SZ 20180704 8.63 8.75 8.61 8.61 8.67 -0.06 -0.69 711153.37 617278.5592 000001.SZ 20180703 8.69 8.70 8.45 8.67 8.61 0.06 0.70 1274838.57 1096657.0333 000001.SZ 20180702 9.05 9.05 8.55 8.61 9.09 -0.48 -5.28 1315520.13 1158545.8684 000002.SZ 20180705 23.02 23.41 22.85 23.05 23.00 0.05 0.22 267278.61 619393.0075 000002.SZ 20180704 23.46 23.75 23.00 23.00 23.42 -0.42 -1.79 249881.03 582470.2146 000002.SZ 20180703 23.10 23.48 22.80 23.42 22.80 0.62 2.72 549964.88 1274023.5757 000002.SZ 20180702 24.50 24.55 22.52 22.80 24.60 -1.80 -7.32 846203.86 1981131.638

如果想要批量拼接&＃xff0c;可以写一个循环&＃xff0c;如将截至某日的所有上市公司股票日线数据拼接成一个大表格&＃xff0c;示例中选取的时间段为&＃39;20180101&＃39;-&＃39;20180105&＃39;&＃xff0c;并只选取了前5只股票&＃xff0c;效果如下。

df &＃61; pro.daily(trade_date&＃61;&＃39;20180105&＃39;)code_list &＃61; df[&＃39;ts_code&＃39;].tolist()[:5]stock_data &＃61; pd.DataFrame()for code in code_list: print(code) df &＃61; pro.daily(ts_code&＃61;code, start_date&＃61;&＃39;20180101&＃39;, end_date&＃61;&＃39;20180105&＃39;) stock_data &＃61; stock_data.append(df, ignore_index&＃61;True)print(stock_data)600863.SH000001.SZ000002.SZ000004.SZ000005.SZ ts_code trade_date open high low close pre_close change pct_chg vol amount0 600863.SH 20180105 3.04 3.04 3.00 3.01 3.02 -0.01 -0.33 101317.50 30551.9821 600863.SH 20180104 3.01 3.03 2.99 3.02 3.00 0.02 0.67 139274.37 41969.2492 600863.SH 20180103 2.99 3.01 2.98 3.00 2.98 0.02 0.67 113068.36 33859.8693 600863.SH 20180102 2.97 2.99 2.97 2.98 2.97 0.01 0.34 87204.89 25997.3384 000001.SZ 20180105 13.21 13.35 13.15 13.30 13.25 0.05 0.38 1210312.72 1603289.5175 000001.SZ 20180104 13.32 13.37 13.13 13.25 13.33 -0.08 -0.60 1854509.48 2454543.5166 000001.SZ 20180103 13.73 13.86 13.20 13.33 13.70 -0.37 -2.70 2962498.38 4006220.7667 000001.SZ 20180102 13.35 13.93 13.32 13.70 13.30 0.40 3.01 2081592.55 2856543.8228 000002.SZ 20180105 32.98 35.88 32.80 34.76 33.12 1.64 4.95 843101.96 2916787.8719 000002.SZ 20180104 32.76 33.53 32.10 33.12 32.33 0.79 2.44 529085.80 1740602.53310 000002.SZ 20180103 32.50 33.78 32.23 32.33 32.56 -0.23 -0.71 646870.20 2130249.69111 000002.SZ 20180102 31.45 32.99 31.45 32.56 31.06 1.50 4.83 683433.50 2218502.76612 000004.SZ 20180105 23.23 23.47 22.85 23.18 23.24 -0.06 -0.26 10444.04 24273.30713 000004.SZ 20180104 23.80 23.83 23.12 23.24 23.80 -0.56 -2.35 14540.66 33908.54814 000004.SZ 20180103 22.42 23.89 22.27 23.80 22.34 1.46 6.54 18795.39 43218.41615 000004.SZ 20180102 22.29 22.49 22.00 22.34 22.38 -0.04 -0.18 6261.81 13951.00416 000005.SZ 20180105 4.26 4.45 4.26 4.34 4.29 0.05 1.17 85226.27 37286.93517 000005.SZ 20180104 4.27 4.33 4.23 4.29 4.27 0.02 0.47 43809.78 18732.17818 000005.SZ 20180103 4.35 4.35 4.22 4.27 4.32 -0.05 -1.16 67990.65 28966.79119 000005.SZ 20180102 4.15 4.50 4.15 4.32 4.14 0.18 4.35 71539.34 30529.757

用pd.concat()【2】也能实现上面的效果&＃xff0c;同样通过设置参数ignore_index&＃61;True来解决索引问题&＃xff0c;这里的axis&＃61;0为默认值&＃xff0c;默认按行拼接。

print(pd.concat([df, df1], axis&＃61;0, ignore_index&＃61;True)) ts_code trade_date open high low close pre_close change pct_chg vol amount0 000001.SZ 20180705 8.62 8.73 8.55 8.60 8.61 -0.01 -0.12 835768.77 722169.5791 000001.SZ 20180704 8.63 8.75 8.61 8.61 8.67 -0.06 -0.69 711153.37 617278.5592 000001.SZ 20180703 8.69 8.70 8.45 8.67 8.61 0.06 0.70 1274838.57 1096657.0333 000001.SZ 20180702 9.05 9.05 8.55 8.61 9.09 -0.48 -5.28 1315520.13 1158545.8684 000002.SZ 20180705 23.02 23.41 22.85 23.05 23.00 0.05 0.22 267278.61 619393.0075 000002.SZ 20180704 23.46 23.75 23.00 23.00 23.42 -0.42 -1.79 249881.03 582470.2146 000002.SZ 20180703 23.10 23.48 22.80 23.42 22.80 0.62 2.72 549964.88 1274023.5757 000002.SZ 20180702 24.50 24.55 22.52 22.80 24.60 -1.80 -7.32 846203.86 1981131.638

02左右拼接

pd.concat()不仅能够实现上下拼接&＃xff0c;而且还能通过设置参数axis&＃61;1实现左右拼接。以拼接两个不同长度的表格为例&＃xff0c;没有值的位置会自动填充为NaN。

print(df) ts_code trade_date close0 000001.SZ 20180705 8.601 000001.SZ 20180704 8.612 000001.SZ 20180703 8.673 000001.SZ 20180702 8.61print(df1) ts_code trade_date close0 000002.SZ 20180709 24.011 000002.SZ 20180706 23.212 000002.SZ 20180705 23.053 000002.SZ 20180704 23.004 000002.SZ 20180703 23.425 000002.SZ 20180702 22.80print(pd.concat([df, df1], axis&＃61;1)) ts_code trade_date close ts_code trade_date close0 000001.SZ 20180705 8.60 000002.SZ 20180709 24.011 000001.SZ 20180704 8.61 000002.SZ 20180706 23.212 000001.SZ 20180703 8.67 000002.SZ 20180705 23.053 000001.SZ 20180702 8.61 000002.SZ 20180704 23.004 NaN NaN NaN 000002.SZ 20180703 23.425 NaN NaN NaN 000002.SZ 20180702 22.80

如果想要按列拼接有相同索引的行&＃xff0c;可以设置参数join&＃61;&＃39;inner&＃39;&＃xff0c;设置参数sort&＃61;True升序排列。以两个索引为时间的表格为例&＃xff0c;效果如下。

print(df) ts_code closetrade_date 20180705 000001.SZ 8.6020180704 000001.SZ 8.6120180703 000001.SZ 8.6720180702 000001.SZ 8.61print(df1) ts_code closetrade_date 20180709 000002.SZ 24.0120180706 000002.SZ 23.2120180705 000002.SZ 23.0520180704 000002.SZ 23.0020180703 000002.SZ 23.4220180702 000002.SZ 22.80print(pd.concat([df, df1], axis&＃61;1, join&＃61;&＃39;inner&＃39;, sort&＃61;True)) ts_code close ts_code closetrade_date 20180702 000001.SZ 8.61 000002.SZ 22.8020180703 000001.SZ 8.67 000002.SZ 23.4220180704 000001.SZ 8.61 000002.SZ 23.0020180705 000001.SZ 8.60 000002.SZ 23.05

03 合并表格

pd.merge()【3】方法可以实现表格之间的合并操作&＃xff0c;类似于SQL中的连接JOIN的用法。通过设置参数how&＃61;&＃39;left&＃39;, &＃39;right&＃39;, &＃39;outer&＃39;, &＃39;inner&＃39;&＃xff0c;默认为 &＃39;inner&＃39; &＃xff0c;实现不同形式的合并。

how&＃61;&＃39;inner&＃39;

设置参数on&＃61;&＃39;trade_date&＃39; 表示两个表格将按照列&＃39;trade_date&＃39; 中的值进行合并&＃xff0c;当参数how为默认值&＃39;inner&＃39;时&＃xff0c;结果和用pd.concat()方法设置参数join&＃61;&＃39;inner&＃39;得到的类似。

区别在于&＃xff0c;pd.merge()操作会自动为合并前有相同列名、不同值的列名添加后缀&＃xff0c;以便我们进行区分&＃xff0c;如下所示的&＃39;close_x&＃39;和&＃39;close_y&＃39;。

如果想要让后缀名变得更有意义&＃xff0c;可以通过设置参数suffixes&＃61;[&＃39;_000001&＃39;, &＃39;_000002&＃39;]实现。

print(df) ts_code closetrade_date 20180705 000001.SZ 8.6020180704 000001.SZ 8.6120180703 000001.SZ 8.6720180702 000001.SZ 8.61print(df1) ts_code closetrade_date 20180709 000002.SZ 24.0120180706 000002.SZ 23.2120180705 000002.SZ 23.0520180704 000002.SZ 23.0020180703 000002.SZ 23.4220180702 000002.SZ 22.80print(df.merge(df1, on&＃61;&＃39;trade_date&＃39;, sort&＃61;True)) ts_code_x close_x ts_code_y close_ytrade_date 20180702 000001.SZ 8.61 000002.SZ 22.8020180703 000001.SZ 8.67 000002.SZ 23.4220180704 000001.SZ 8.61 000002.SZ 23.0020180705 000001.SZ 8.60 000002.SZ 23.05print(df.merge(df1, on&＃61;&＃39;trade_date&＃39;, sort&＃61;True, suffixes&＃61;[&＃39;_000001&＃39;, &＃39;_000002&＃39;])) ts_code_000001 close_000001 ts_code_000002 close_000002trade_date 20180702 000001.SZ 8.61 000002.SZ 22.8020180703 000001.SZ 8.67 000002.SZ 23.4220180704 000001.SZ 8.61 000002.SZ 23.0020180705 000001.SZ 8.60 000002.SZ 23.05

如果两个表格中想要进行合并的列名不同&＃xff0c;如下所示的表格df中的交易日期列名为&＃39;trade_date_stock&＃39;&＃xff0c;表格df_index中的交易日期列名为&＃39;trade_date&＃39;&＃xff0c;这时需要我们设置参数left_on和right_on指定要进行合并的列名。

print(df) ts_code trade_date_stock close0 000001.SZ 20180704 8.611 000001.SZ 20180703 8.672 000001.SZ 20180702 8.61print(df1) ts_code trade_date close0 399300.SZ 20180706 3365.12271 399300.SZ 20180705 3342.43792 399300.SZ 20180704 3363.74733 399300.SZ 20180703 3409.28014 399300.SZ 20180702 3407.9638print(df.merge(df_index, left_on&＃61;&＃39;trade_date_stock&＃39;, right_on&＃61;&＃39;trade_date&＃39;, sort&＃61;True)) ts_code_x trade_date_stock close_x ts_code_y trade_date close_y0 000001.SZ 20180702 8.61 399300.SZ 20180702 3407.96381 000001.SZ 20180703 8.67 399300.SZ 20180703 3409.28012 000001.SZ 20180704 8.61 399300.SZ 20180704 3363.7473

参数on也可以传入一个包含多个列名的list&＃xff0c;如[&＃39;ts_code&＃39;, &＃39;trade_date&＃39;]&＃xff0c;此时在默认how&＃61;&＃39;inner&＃39;的情况下, 合并后只会返回[&＃39;ts_code&＃39;, &＃39;trade_date&＃39;]值在两个表格中都相等的行。

print(df) ts_code trade_date close0 000001.SZ 20180706 8.661 000001.SZ 20180705 8.602 000001.SZ 20180704 8.613 000001.SZ 20180703 8.674 000001.SZ 20180702 8.61print(df1) ts_code trade_date turnover_rate volume_ratio pe pb0 000001.SZ 20180702 0.7662 1.28 6.3753 0.72670 000001.SZ 20180703 0.7425 1.21 6.4197 0.7318combined &＃61; pd.merge(left&＃61;df, right&＃61;df1, on&＃61;[&＃39;ts_code&＃39;, &＃39;trade_date&＃39;], sort&＃61;True)print(combined) ts_code trade_date close turnover_rate volume_ratio pe pb0 000001.SZ 20180702 8.61 0.7662 1.28 6.3753 0.72671 000001.SZ 20180703 8.67 0.7425 1.21 6.4197 0.7318

我们还可以通过设置参数how&＃61;&＃39;left&＃39;, how&＃61;&＃39;right&＃39;, how&＃61;&＃39;outer&＃39;, 分别进行左连接、右连接和外连接。

how&＃61;&＃39;left&＃39;

左连接的示意图如上所示&＃xff0c;从下面示例代码返回的结果可以观察到&＃xff0c;左连接会保留左侧表格的所有数据&＃xff0c;以及两个表格按照on设置的条件合并后重合的部分&＃xff0c;没有数据的地方会自动填充NaN值。

print(df) ts_code trade_date close0 000001.SZ 20180706 8.661 000001.SZ 20180705 8.602 000001.SZ 20180704 8.613 000001.SZ 20180703 8.674 000001.SZ 20180702 8.61print(df1) ts_code trade_date turnover_rate volume_ratio pe pb0 000001.SZ 20180702 0.7662 1.28 6.3753 0.72670 000001.SZ 20180703 0.7425 1.21 6.4197 0.7318combined &＃61; pd.merge(df, df1, how&＃61;&＃39;left&＃39;, on&＃61;[&＃39;ts_code&＃39;, &＃39;trade_date&＃39;], sort&＃61;True)print(combined) ts_code trade_date close turnover_rate volume_ratio pe pb0 000001.SZ 20180702 8.61 0.7662 1.28 6.3753 0.72671 000001.SZ 20180703 8.67 0.7425 1.21 6.4197 0.73182 000001.SZ 20180704 8.61 NaN NaN NaN NaN3 000001.SZ 20180705 8.60 NaN NaN NaN NaN4 000001.SZ 20180706 8.66 NaN NaN NaN NaN

how&＃61;&＃39;right&＃39;

同理&＃xff0c;右连接则会保留右侧表格的所有数据&＃xff0c;以及两个表格按照on设置的条件合并后重合的部分&＃xff0c;没有数据的地方会自动填充NaN值。

print(df) ts_code trade_date close0 000001.SZ 20180706 8.661 000001.SZ 20180705 8.602 000001.SZ 20180704 8.613 000001.SZ 20180703 8.674 000001.SZ 20180702 8.61print(df1) ts_code trade_date turnover_rate volume_ratio pe pb0 000001.SZ 20180702 0.7662 1.28 6.3753 0.72670 000001.SZ 20180703 0.7425 1.21 6.4197 0.7318combined &＃61; pd.merge(df, df1, how&＃61;&＃39;right&＃39;, on&＃61;[&＃39;ts_code&＃39;, &＃39;trade_date&＃39;], sort&＃61;True)print(combined) ts_code trade_date close turnover_rate volume_ratio pe pb0 000001.SZ 20180702 8.61 0.7662 1.28 6.3753 0.72671 000001.SZ 20180703 8.67 0.7425 1.21 6.4197 0.7318

how&＃61;&＃39;outer&＃39;

外连接的示意图如上所示&＃xff0c;返回满足合并条件的所有行&＃xff0c;没有数据的地方会自动填充NaN值。

print(df) ts_code trade_date close0 000001.SZ 20180706 8.661 000001.SZ 20180705 8.602 000001.SZ 20180704 8.613 000001.SZ 20180703 8.674 000001.SZ 20180702 8.61print(df1) ts_code trade_date turnover_rate volume_ratio pe pb0 000001.SZ 20180706 0.5756 1.03 6.4123 0.73090 000001.SZ 20180709 0.8212 1.38 6.6863 0.76210 000001.SZ 20180710 0.5223 0.86 6.6493 0.7579combined &＃61; pd.merge(df, df1, how&＃61;&＃39;outer&＃39;, on&＃61;[&＃39;ts_code&＃39;, &＃39;trade_date&＃39;], sort&＃61;True)print(combined) ts_code trade_date close turnover_rate volume_ratio pe pb0 000001.SZ 20180702 8.61 NaN NaN NaN NaN1 000001.SZ 20180703 8.67 NaN NaN NaN NaN2 000001.SZ 20180704 8.61 NaN NaN NaN NaN3 000001.SZ 20180705 8.60 NaN NaN NaN NaN4 000001.SZ 20180706 8.66 0.5756 1.03 6.4123 0.73095 000001.SZ 20180709 NaN 0.8212 1.38 6.6863 0.76216 000001.SZ 20180710 NaN 0.5223 0.86 6.6493 0.7579

04总结

本文介绍了Pandas中常用的合并表格的方法&＃xff0c;分别为.append(), pd.concat()和pd.merge()&＃xff0c;我们观察到&＃xff0c;通过设置不同的参数值&＃xff0c;可以对表格进行不同形式的拼接、合并操作。

最后&＃xff0c;我自己是一名从事了多年开发的Python老程序员&＃xff0c;辞职目前在做自己的Python私人定制课程&＃xff0c;今年年初我花了一个月整理了一份最适合2019年学习的Python学习干货&＃xff0c;可以送给每一位喜欢Python的小伙伴&＃xff0c;想要获取的可以关注我的头条号并在后台私信我&＃xff1a;01&＃xff0c;即可免费获取。