网页抓取数据转DataFrame：合并与格式重塑问题求助

核心思路 #

将现有两个6行2列的DataFrame分别转置，提取指标作为列名，流量数据作为单行值，再将两行合并为最终的2行6列结构。

修改后的完整代码 #

import numpy as np
import pandas as pd
import requests
from bs4 import BeautifulSoup

url = 'https://www.civilaviation.gov.in/'
resp = requests.get(url)
soup = BeautifulSoup(resp.content.decode(), 'html.parser')

div = soup.find('div', {'class':'airport-col vande-bharat-col'})
div2 = soup.find('div', {'class':'airport-col airport-widget'})

dom = div.get_text()
intl = div2.get_text()


def str2frame(estr, sep = '\n', lineterm = '\n\n\n\n\n', set_header = True):
    dat = [x.split(sep) for x in estr.split(lineterm)][0:-1]
    df = pd.DataFrame(dat)
    if set_header:
        df = df.T.set_index(0, drop = True).T # flip, set ix, flip back
    return df


df1 = str2frame(dom)
df2 = str2frame(intl)

# 清理空列并标准化列名
df1 = df1.dropna(axis=1, how='all')
df2 = df2.dropna(axis=1, how='all')
df1.columns = ['Indicator', 'Domestic Traffic']
df2.columns = ['Indicator', 'International Traffic']

# 转置为单行结构，标记流量类型索引
df_domestic = df1.set_index('Indicator').T
df_domestic.index = ['Domestic']

df_international = df2.set_index('Indicator').T
df_international.index = ['International']

# 合并得到目标结构
final_df = pd.concat([df_domestic, df_international])

print(final_df)

关键步骤说明 #

• 清理空列：用dropna(axis=1, how='all')移除原DataFrame中的无效空列，避免干扰后续处理
• 转置与索引设置：通过set_index('Indicator').T将指标转为列名，流量数据转为单行；设置索引明确区分国内/国际流量
• 合并DataFrame：使用pd.concat将两个单行DataFrame纵向合并，直接得到2行6列的目标结构

内容的提问来源于stack exchange，提问作者Mickey Couvertier