I have a dataframe and a list of variables which is called customerids:

List of variables:

customerids = [1028598965607080, 1020304050607099, 8423413884965465, 8948423132187895, 8798432154654555, 1231897897421258]

Other variable: date = datetime.strptime(date_str, '%Y-%m-%d %H:%M:%S') - timedelta(days=1)

Basically, I need to do the select below in the dataframe, taking the variables of date and customerids to be able to generate a new dataframe with the column count:

SELECT count(1) FROM dataframe WHERE createdate <'{date} 21:00:00' and customerid = '{customerids}

How can I do this?

Expected output:

CodePudding user response：

Dataframe:

from datetime import datetime, timedelta
from pyspark.sql import functions as F

df = spark.createDataFrame(
    [(1028598965607080, '2022-06-20 15:03:01'),
     (1020304050607099, '2022-06-20 15:03:01'),
     (8423413884965465, '2022-06-20 15:03:01'),
     (8948423132187895, '2022-06-20 15:03:01'),
     (1028598965607080, '2022-06-20 15:03:01'),
     (1028598965607080, '2022-06-20 15:03:01'),
     (8798432154654555, '2022-06-20 15:03:01'),
     (1231897897421258, '2022-06-20 15:03:01')],
    ['customerid', 'createdate'])

Your date variable has another variable inside (date_str). So I created it myself.

date_str = '2022-07-01 12:58:58'

customerids = [1028598965607080, 1020304050607099, 8423413884965465, 8948423132187895, 8798432154654555, 1231897897421258]
date = datetime.strptime(date_str, '%Y-%m-%d %H:%M:%S') - timedelta(days=1)

ts = f"{date.year}, {date.month}, {date.day}, 21, 0, 0"
df = (df.filter(F.col('customerid').isin(customerids) &
                (F.col('createdate') < F.expr(f"make_timestamp({ts})")))
        .groupBy('customerid')
        .count()
)
df.show()
#  ---------------- ----- 
# |      customerid|count|
#  ---------------- ----- 
# |1028598965607080|    3|
# |1020304050607099|    1|
# |8423413884965465|    1|
# |8948423132187895|    1|
# |8798432154654555|    1|
# |1231897897421258|    1|
#  ---------------- -----