将制表符分隔的字符串拆分成不同的列。

huangapple go评论102阅读模式
英文:

split tabs string into different columns

问题

在我的铸造环境中,我有一个名为"data"的pyspark数据集。

每一行都包含一个看起来像TSV的字符串。每一行都有一个类似这样的值:

ott-akamai-logs-processor	srv	2023-07-29 17:46:50.134	2023-07-29 17:46:49.358		unstruct	103b9271-777		ott	node-3.13.1	ssc-2.8.2-kinesis	snowplow-enrich-kinesis-3.7.0		3.65.234.x				12345679	DE	HE	Karachi	60313	50.1188	8.6843	Malta																																		{"schema":"iglu:com.xxx/1-0-0","data":{"schema":"xxx/hls_manifest_requested/jsonschema/1-0-1","data":{"channel":"bildtv-broadcast","session_id":"xxx","request_id":"xxx","total_bytes":351,"referrer":"^","geo_country":"DE","geo_state":"Berlin","geo_city":"-","variant_name":"6.m3u8"}}}																			snowplow-nodejs-tracker/3.13.1																																						Europe/Berlin				2023-07-29 17:46:49.281			{"schema":"xxx/contexts/jsonschema/1-0-1","data":[{"schema":"iglu:nl.basjes/yauaa_context/jsonschema/1-0-4","data":{"deviceBrand":"Unknown","deviceName":"Unknown","operatingSystemVersionMajor":"??","layoutEngineNameVersion":"Unknown ??","operatingSystemNameVersion":"Unknown ??","agentInformationEmail":"Unknown","networkType":"Unknown","webviewAppNameVersionMajor":"Unknown ??","layoutEngineNameVersionMajor":"Unknown ??","operatingSystemName":"Unknown","agentVersionMajor":"3","layoutEngineVersionMajor":"??","webviewAppName":"Unknown","deviceClass":"Unknown","agentNameVersionMajor":"Snowplow-Nodejs-Tracker 3","operatingSystemNameVersionMajor":"Unknown ??","webviewAppVersionMajor":"??","operatingSystemClass":"Unknown","webviewAppVersion":"??","layoutEngineName":"Unknown","agentName":"Snowplow-Nodejs-Tracker","agentVersion":"3.13.1","layoutEngineClass":"Unknown","agentNameVersion":"Snowplow-Nodejs-Tracker 3.13.1","operatingSystemVersion":"??","agentClass":"Special","layoutEngineVersion":"??","agentInformationUrl":"Unknown"}},{"schema":"iglu:com.snowplowanalytics.snowplow/ua_parser_context/jsonschema/1-0-0","data":{"useragentFamily":"Other","useragentMajor":null,"useragentMinor":null,"useragentPatch":null,"useragentVersion":"Other","osFamily":"Other","osMajor":null,"osMinor":null,"osPatch":null,"osPatchMinor":null,"osVersion":"Other","deviceFamily":"Other"}}]}		2023-07-29 17:46:09.938	com.axelspringer.ott	hls_manifest_requested	jsonschema	1-0-1		2023-07-29 17:46:09.938

在这里,各个部分由制表符分隔。对于每个制表符分隔,我想将值放入不同的列中。我该如何做?

def unnamed_1(my_df):
    df = my_df

    return df
英文:

In my foundry environment, I have a pyspark dataset with only one column called "data".

Each row has a string that looks like a TSV. Each row has a value like this:

ott-akamai-logs-processor	srv	2023-07-29 17:46:50.134	2023-07-29 17:46:49.358		unstruct	103b9271-777		ott	node-3.13.1	ssc-2.8.2-kinesis	snowplow-enrich-kinesis-3.7.0		3.65.234.x				12345679	DE	HE	Karachi	60313	50.1188	8.6843	Malta																																		{"schema":"iglu:com.xxx/1-0-0","data":{"schema":"xxx/hls_manifest_requested/jsonschema/1-0-1","data":{"channel":"bildtv-broadcast","session_id":"xxx","request_id":"xxx","total_bytes":351,"referrer":"^","geo_country":"DE","geo_state":"Berlin","geo_city":"-","variant_name":"6.m3u8"}}}																			snowplow-nodejs-tracker/3.13.1																																						Europe/Berlin				2023-07-29 17:46:49.281			{"schema":"xxx/contexts/jsonschema/1-0-1","data":[{"schema":"iglu:nl.basjes/yauaa_context/jsonschema/1-0-4","data":{"deviceBrand":"Unknown","deviceName":"Unknown","operatingSystemVersionMajor":"??","layoutEngineNameVersion":"Unknown ??","operatingSystemNameVersion":"Unknown ??","agentInformationEmail":"Unknown","networkType":"Unknown","webviewAppNameVersionMajor":"Unknown ??","layoutEngineNameVersionMajor":"Unknown ??","operatingSystemName":"Unknown","agentVersionMajor":"3","layoutEngineVersionMajor":"??","webviewAppName":"Unknown","deviceClass":"Unknown","agentNameVersionMajor":"Snowplow-Nodejs-Tracker 3","operatingSystemNameVersionMajor":"Unknown ??","webviewAppVersionMajor":"??","operatingSystemClass":"Unknown","webviewAppVersion":"??","layoutEngineName":"Unknown","agentName":"Snowplow-Nodejs-Tracker","agentVersion":"3.13.1","layoutEngineClass":"Unknown","agentNameVersion":"Snowplow-Nodejs-Tracker 3.13.1","operatingSystemVersion":"??","agentClass":"Special","layoutEngineVersion":"??","agentInformationUrl":"Unknown"}},{"schema":"iglu:com.snowplowanalytics.snowplow/ua_parser_context/jsonschema/1-0-0","data":{"useragentFamily":"Other","useragentMajor":null,"useragentMinor":null,"useragentPatch":null,"useragentVersion":"Other","osFamily":"Other","osMajor":null,"osMinor":null,"osPatch":null,"osPatchMinor":null,"osVersion":"Other","deviceFamily":"Other"}}]}		2023-07-29 17:46:09.938	com.axelspringer.ott	hls_manifest_requested	jsonschema	1-0-1		2023-07-29 17:46:09.938

Here, things are separated by tabs. For each tab separation, I want to put the values into different columns. How can I do so?

def unnamed_1(my_df):
    df = my_df

    return df

答案1

得分: 2

你可以使用Spark中的split函数来完成这个任务:
https://spark.apache.org/docs/3.1.2/api/python/reference/api/pyspark.sql.functions.split.html

import pyspark.sql.functions as f

split_col = f.split(df['data'], '\t')

formatted_df = (
    df
    .withColumn('column_a', split_col.getItem(0))
    .withColumn('column_b', split_col.getItem(1))
    .withColumn('column_c', split_col.getItem(2))
    # ...
    .drop('data')
)

如果你有一个按正确顺序排列的所需列的列表,你可以使用迭代来自动化这个过程:

columns = [
    'column_a',
    'column_b',
    'column_c',
    # ...
]

formatted_df = df
for i in range(len(columns)):
    column_name = columns[i]
    column_values = split_col.getItem(i)
    formatted_df = formatted_df.withColumn(column_name, column_values)

formatted_df = formatted_df.drop('data')

如果你的数据以正确格式的TSV形式接收,你可以在读取数据时使用delimiter选项来解析它:

df = (
    spark.read.format("csv")
    .option("delimiter", "\t")
    .load('data.tsv')
)
英文:

You can use the split function in spark to accomplish this:
https://spark.apache.org/docs/3.1.2/api/python/reference/api/pyspark.sql.functions.split.html

import pyspark.sql.functions as f

split_col = f.split(df['data'], '\t')

formatted_df = (
    df
    .withColumn('column_a', split_col.getItem(0))
    .withColumn('column_b', split_col.getItem(1))
    .withColumn('column_c', split_col.getItem(2))
    # ...
    .drop('data')
)

If you have a list of the desired columns in the correct order, you can automate it with iteration:

columns = [
    'column_a',
    'column_b',
    'column_c',
    # ...
]

formatted_df = df
for i in range(len(columns)):
    column_name = columns[i]
    column_values = split_col.getItem(i)
    formatted_df = formatted_df.withColumn(column_name, column_values)

formatted_df = formatted_df.drop('data')

If you're receiving this data as a properly formatted TSV you could instead use the delimiter option while reading in the data to parse this.

df = (
    spark.read.format("csv")
    .option("delimiter", "\t")
    .load('data.tsv')
)

答案2

得分: 1

你可以使用 pandas.read_csv 来实现:

import pandas as pd
from io import StringIO

string = 'ott-akamai-logs-processor   srv 2023-07-29 17:46:50.134 2023-07-29 17:46:49.358     unstruct    103b9271-777        ott node-3.13.1 ssc-2.8.2-kinesis   snowplow-enrich-kinesis-3.7.0       3.65.234.x              12345679    DE  HE  Karachi 60313   50.1188 8.6843  Malta                                                                                                                                       {"schema":"iglu:com.xxx/1-0-0","data":{"schema":"xxx/hls_manifest_requested/jsonschema/1-0-1","data":{"channel":"bildtv-broadcast","session_id":"xxx","request_id":"xxx","total_bytes":351,"referrer":"^","geo_country":"DE","geo_state":"Berlin","geo_city":"-","variant_name":"6.m3u8"}}}                                                                         snowplow-nodejs-tracker/3.13.1                                                                                                                                                      Europe/Berlin               2023-07-29 17:46:49.281         {"schema":"xxx/contexts/jsonschema/1-0-1","data":[{"schema":"iglu:nl.basjes/yauaa_context/jsonschema/1-0-4","data":{"deviceBrand":"Unknown","deviceName":"Unknown","operatingSystemVersionMajor":"??","layoutEngineNameVersion":"Unknown ??","operatingSystemNameVersion":"Unknown ??","agentInformationEmail":"Unknown","networkType":"Unknown","webviewAppNameVersionMajor":"Unknown ??","layoutEngineNameVersionMajor":"Unknown ??","operatingSystemName":"Unknown","agentVersionMajor":"3","layoutEngineVersionMajor":"??","webviewAppName":"Unknown","deviceClass":"Unknown","agentNameVersionMajor":"Snowplow-Nodejs-Tracker 3","operatingSystemNameVersionMajor":"Unknown ??","webviewAppVersionMajor":"??","operatingSystemClass":"Unknown","webviewAppVersion":"??","layoutEngineName":"Unknown","agentName":"Snowplow-Nodejs-Tracker","agentVersion":"3.13.1","layoutEngineClass":"Unknown","agentNameVersion":"Snowplow-Nodejs-Tracker 3.13.1","operatingSystemVersion":"??","agentClass":"Special","layoutEngineVersion":"??","agentInformationUrl":"Unknown"}},{"schema":"iglu:com.snowplowanalytics.snowplow/ua_parser_context/jsonschema/1-0-0","data":{"useragentFamily":"Other","useragentMajor":null,"useragentMinor":null,"useragentPatch":null,"useragentVersion":"Other","osFamily":"Other","osMajor":null,"osMinor":null,"osPatch":null,"osPatchMinor":null,"osVersion":"Other","deviceFamily":"Other"}}]}        2023-07-29 17:46:09.938 com.axelspringer.ott    hls_manifest_requested  jsonschema  1-0-1       2023-07-29 17:46:09.938'
tsvString = StringIO(string)
df = pd.read_csv(tsvString, sep='\t')
英文:

You can use pandas.read_csv for that:

import pandas as pd
from io import StringIO

string = 'ott-akamai-logs-processor   srv 2023-07-29 17:46:50.134 2023-07-29 17:46:49.358     unstruct    103b9271-777        ott node-3.13.1 ssc-2.8.2-kinesis   snowplow-enrich-kinesis-3.7.0       3.65.234.x              12345679    DE  HE  Karachi 60313   50.1188 8.6843  Malta                                                                                                                                       {"schema":"iglu:com.xxx/1-0-0","data":{"schema":"xxx/hls_manifest_requested/jsonschema/1-0-1","data":{"channel":"bildtv-broadcast","session_id":"xxx","request_id":"xxx","total_bytes":351,"referrer":"^","geo_country":"DE","geo_state":"Berlin","geo_city":"-","variant_name":"6.m3u8"}}}                                                                         snowplow-nodejs-tracker/3.13.1                                                                                                                                                      Europe/Berlin               2023-07-29 17:46:49.281         {"schema":"xxx/contexts/jsonschema/1-0-1","data":[{"schema":"iglu:nl.basjes/yauaa_context/jsonschema/1-0-4","data":{"deviceBrand":"Unknown","deviceName":"Unknown","operatingSystemVersionMajor":"??","layoutEngineNameVersion":"Unknown ??","operatingSystemNameVersion":"Unknown ??","agentInformationEmail":"Unknown","networkType":"Unknown","webviewAppNameVersionMajor":"Unknown ??","layoutEngineNameVersionMajor":"Unknown ??","operatingSystemName":"Unknown","agentVersionMajor":"3","layoutEngineVersionMajor":"??","webviewAppName":"Unknown","deviceClass":"Unknown","agentNameVersionMajor":"Snowplow-Nodejs-Tracker 3","operatingSystemNameVersionMajor":"Unknown ??","webviewAppVersionMajor":"??","operatingSystemClass":"Unknown","webviewAppVersion":"??","layoutEngineName":"Unknown","agentName":"Snowplow-Nodejs-Tracker","agentVersion":"3.13.1","layoutEngineClass":"Unknown","agentNameVersion":"Snowplow-Nodejs-Tracker 3.13.1","operatingSystemVersion":"??","agentClass":"Special","layoutEngineVersion":"??","agentInformationUrl":"Unknown"}},{"schema":"iglu:com.snowplowanalytics.snowplow/ua_parser_context/jsonschema/1-0-0","data":{"useragentFamily":"Other","useragentMajor":null,"useragentMinor":null,"useragentPatch":null,"useragentVersion":"Other","osFamily":"Other","osMajor":null,"osMinor":null,"osPatch":null,"osPatchMinor":null,"osVersion":"Other","deviceFamily":"Other"}}]}        2023-07-29 17:46:09.938 com.axelspringer.ott    hls_manifest_requested  jsonschema  1-0-1       2023-07-29 17:46:09.938'
tsvString = StringIO(string)
df = pd.read_csv(tsvString,sep='\t')

huangapple
  • 本文由 发表于 2023年8月10日 21:39:18
  • 转载请务必保留本文链接:https://go.coder-hub.com/76876280.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定