根据条件组合 Spark 数据框列中的多行 [英] combining multiple rows in Spark dataframe column based on condition

问题描述

我正在尝试根据条件组合火花数据框中的多行:

I am trying to combine multiple rows in a spark dataframe based on a condition:

这是我拥有的数据框(df):

This is the dataframe I have(df):

|username | qid | row_no | text  |
 ---------------------------------
|  a      | 1   |  1     | this  |
|  a      | 1   |  2     |  is   |
|  d      | 2   |  1     |  the  |
|  a      | 1   |  3     | text  |
|  d      | 2   |  2     |  ball |

我希望它看起来像这样

|username | qid | row_no | text        |
 ---------------------------------------
|   a     | 1   |  1,2,3 | This is text|
|   b     | 2   |  1,2   | The ball    |

我使用的是 spark 1.5.2 它没有 collect_list 功能

I am using spark 1.5.2 it does not have collect_list function

推荐答案

collect_list 仅在 1.6 中出现.

collect_list showed up only in 1.6.

我会检查底层的 RDD.方法如下:

I'd go through the underlying RDD. Here's how:

data_df.show()
+--------+---+------+----+
|username|qid|row_no|text|
+--------+---+------+----+
|       d|  2|     2|ball|
|       a|  1|     1|this|
|       a|  1|     3|text|
|       a|  1|     2|  is|
|       d|  2|     1| the|
+--------+---+------+----+

那么这个

reduced = data_df\
    .rdd\
    .map(lambda row: ((row[0], row[1]), [(row[2], row[3])]))\
    .reduceByKey(lambda x,y: x+y)\
    .map(lambda row: (row[0], sorted(row[1], key=lambda text: text[0]))) \
    .map(lambda row: (
            row[0][0], 
            row[0][1], 
            ','.join([str(e[0]) for e in row[1]]),
            ' '.join([str(e[1]) for e in row[1]])
        )
    )

schema_red = typ.StructType([
        typ.StructField('username', typ.StringType(), False),
        typ.StructField('qid', typ.IntegerType(), False),
        typ.StructField('row_no', typ.StringType(), False),
        typ.StructField('text', typ.StringType(), False)
    ])

df_red = sqlContext.createDataFrame(reduced, schema_red)
df_red.show()

以上产生了以下内容:

+--------+---+------+------------+
|username|qid|row_no|        text|
+--------+---+------+------------+
|       d|  2|   1,2|    the ball|
|       a|  1| 1,2,3|this is text|
+--------+---+------+------------+

在熊猫中

df4 = pd.DataFrame([
        ['a', 1, 1, 'this'],
        ['a', 1, 2, 'is'],
        ['d', 2, 1, 'the'],
        ['a', 1, 3, 'text'],
        ['d', 2, 2, 'ball']        
    ], columns=['username', 'qid', 'row_no', 'text'])

df_groupped=df4.sort_values(by=['qid', 'row_no']).groupby(['username', 'qid'])

df3 = pd.DataFrame()
df3['row_no'] = df_groupped.apply(lambda row: ','.join([str(e) for e in row['row_no']]))
df3['text']   = df_groupped.apply(lambda row: ' '.join(row['text']))

df3 = df3.reset_index()

这篇关于根据条件组合 Spark 数据框列中的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！