将报表格式转换为数据集Python

Question

我正在尝试将报表输出转换为数据集进行分析。我无法访问从中拉入报表的数据库，因此需要使用Python进行此转换。你知道吗

所需的输入数据集和最终数据集示例如下。你知道吗

这份报告按月提交。我怎样才能为这个月增加一个专栏呢？你知道吗

import pandas as pd
data_input = [['2015. Aug'], 
        ['VESSEL', 'ARR', 'DEP', 'CARGO', 'QTY'], 
        ['C.DIGNITY', '01ST JUL', '02ND JUL', 'QATAR LAND', '1 MB'],
        ['MARNA CENTAURUS', '06TH AUG', '07TH AUG', 'BASRAH HEAVY CRUDE OIL', '1 MB'],
        ['C.MIGHTY', '05TH AUG', '06TH AUG', 'ARABIAN MEDIUM,ARABIAN HEAVY,ARABIAN LIGHT', '1.5 MB'],
        ['PAVEL CHERNYSH', '07TH AUG', '08TH AUG', 'SOKOL CRUDE OIL', '790 KB'],
        ['2015. Sep'], 
        ['VESSEL', 'ARR', 'DEP', 'CARGO', 'QTY'],
        ['C.EMPEROR', '01ST SEP', '03RD SEP', 'ARABIAN HEAVY,ARABIAN LIGHT', '1.53 MB'],
        ['DIONA', '03RD SEP', '05TH SEP', 'FOROZAN CRUDE OIL', '2 MB'],
        ['C.FREEDOM', '11TH SEP', '13TH SEP', 'KUWAIT CRUDE OIL,MURBAN CRUDE OIL', '1.27 MB'],
        ['IDEMITSU MARU', '13TH SEP', '15TH SEP', 'QATAR LAND CRUDE,QATAR MARINE CRUDE OIL,MURBAN CRUDE OIL', '2 MB']]
df_input = pd.DataFrame(data_input)

data_final = [['C.DIGNITY', '01ST JUL', '02ND JUL', 'QATAR LAND', '1 MB', '2015. Aug'],
        ['MARNA CENTAURUS', '06TH AUG', '07TH AUG', 'BASRAH HEAVY CRUDE OIL', '1 MB', '2015. Aug'],
        ['C.MIGHTY', '05TH AUG', '06TH AUG', 'ARABIAN MEDIUM,ARABIAN HEAVY,ARABIAN LIGHT', '1.5 MB', '2015. Aug'],
        ['PAVEL CHERNYSH', '07TH AUG', '08TH AUG', 'SOKOL CRUDE OIL', '790 KB', '2015. Aug'],
        ['C.EMPEROR', '01ST SEP', '03RD SEP', 'ARABIAN HEAVY,ARABIAN LIGHT', '1.53 MB', '2015. Sep'],
        ['DIONA', '03RD SEP', '05TH SEP', 'FOROZAN CRUDE OIL', '2 MB', '2015. Sep'],
        ['C.FREEDOM', '11TH SEP', '13TH SEP', 'KUWAIT CRUDE OIL,MURBAN CRUDE OIL', '1.27 MB', '2015. Sep'],
        ['IDEMITSU MARU', '13TH SEP', '15TH SEP', 'QATAR LAND CRUDE,QATAR MARINE CRUDE OIL,MURBAN CRUDE OIL', '2 MB', '2015. Sep']]

df_final = pd.DataFrame(data_final , columns = ['VESSEL', 'ARR', 'DEP', 'CARGO', 'QTY', 'REP_MONTH'])

Answer 1

首先需要规范化数据。执行：

header = ['VESSEL', 'ARR', 'DEP', 'CARGO', 'QTY']
data_final = []
for row in data_input:
    if row == header: #If this is row in the data just contains the header, it's not needed
        continue
    if len(row) == 1: #If this row in the data has 1 item, than it's the month for the next rows
        month = row[0]
        continue
    data_final.append(row + [month]) #Add the last month founded to the end of the row
df_final = pd.DataFrame(data_final, columns=header + ['REP_MONTH'])