按指定字符分行——MySQL与Python实现

SQL

• 原来的表结构

/*创建表*/
create table stand
with cte_stand as (
select 'ph值' FFactor,
       '水提法,盐提法' FMethods,
       '6.5~7.5' FRange
union
select 'ph值' FFactor,
       '试纸法' FMethods,
       '6.0~8.0' FRange
union
select '有机质' FFactor,
       '消毒法,速测法' FMethods,
       '3.0~5.0' FRange)
select * from cte_stand;
/*查看*/
select * from stand;

• 查询语句及结果

SELECT t.FFactor, 
       substring_index(substring_index(t.FMethods,',', b.help_topic_id + 1), ',', -1) method
FROM stand t inner join mysql.help_topic b 
ON b.help_topic_id <  (LENGTH(t.FMethods) - LENGTH(REPLACE(t.FMethods, ',', '')) + 1);

sql_str.png

注释：mysql.help_topic为MySQL自带的表，其中的help_topic_id字段用来产生自增id

Python实现

• 与上面的同等实现

import pandas as pd
import numpy as np

df = pd.DataFrame([
        ['ph值', '水提法,盐提法', '6.5~7.5'],
        ['ph值', '试纸法', '6.0~8.0'],
        ['有机质', '消毒法,速测法', '3.0~5.0'],
    ], columns=['FFactor', 'FMethods', 'FRange'])
a = pd.DataFrame({
    'FFactor': df['FFactor'].repeat(df['FMethods'].str.count(',')+1),
    'FMethods': np.concatenate(df['FMethods'].str.split(','))
    })
print(a)

python_str.png

• 当要切分的列不是str，而是list

import pandas as pd
import numpy as np

df = pd.DataFrame({'a': [1, 2], 'b': [[1, 2], [4, 5, 5]]})

a = pd.DataFrame({
    'a': df['a'].repeat(df['b'].str.len()),
    'b': np.concatenate(df['b'].values)
    })

print(df)
print(a)

python_list.png