如何使用脚本将xlsx表格数据进行拼接？

Question

使用脚本将xlsx表格数据进行拼接，将下图格式

转换为这种格式

Answer 1

这个意思？

import pandas as pd

def get_df(series, *col_name):
    series = series[1].tolist()
    d = pd.DataFrame()
    
    for j in range(3, 6):
        dict_data = {}
        tt = series[j]
        if tt:
            t =[col_name[j]+ i for i in tt.split(',')]
            len_ = len(t)
            dict_data['编码'] = [col_name[j]] * len_
            dict_data['号段'] = t
            dict_data['省份'] = [series[0] ]* len_
            dict_data['城市'] = [series[1]] * len_
            dict_data['运营商'] = ['中国广电'] * len_
            dt =  pd.DataFrame(data = dict_data)
            d = pd.concat([d, dt], axis = 0)
            
    return d
        

data = {'省份': ['安徽'] * 10,
        '城市': ['滁州', '合肥', '蚌埠', '芜湖', '淮南', '马鞍山', '安庆', '宿州', '阜阳', '亳州'],
        '城市区号': list(range(550, 559)) + [558],
        '1920': ['550,640,650', '551,560,565,569,644,651', '552, 642,652', '553,643,653', '554', '555,655,645', '556,646,656', '557,647,657', '558,568,641,648,658,659,649', '567'],
        '1921': ['550,640', '551,560,565,641', '552', '553,643', '554,569', '555', '556,646', '557,647', '648', '558,567,642,645'],
        '1922': ['', '350,401', '', '', '', '', '', '', '354,403', ''],
        }
df = pd.DataFrame(data)
print(df)
df_res = pd.DataFrame()
for dft in df.iterrows() :
    res = get_df(dft, *tuple(df.columns))
    res = res.reset_index(drop = True)
    df_res = pd.concat([df_res,res],axis=0)
df_res = df_res.reset_index(drop = True)
print(df_res)

结果