Pandas DataFrame拼接结果不符合预期，需合并为单行并保留正确表头

问题 #

我通过AlphaVantage API获取IBM的公司概况、损益表、资产负债表、现金流及年度收益数据，生成多个DataFrame后用pd.concat()拼接，但拼接后输出是多行，希望合并为单行并保留正确表头。

代码如下：

import requests
import pandas as pd

companies = ["IBM"]

def fetchData(func, symbol):
    key = "key"
    url = f'https://www.alphavantage.co/query?function={func}&symbol={symbol}&apikey={key}'
    r = requests.get(url)
    data = r.json()
    return data

dfs = []

#COMPANY OVERVIEW

for company in companies:
        ov = fetchData("OVERVIEW", company)
        dfo = pd.DataFrame.from_dict(ov, orient='index').transpose()
        dfo = dfo.drop(columns = ['AssetType', 'Description', 'CIK', 'Sector', 'Address', 'FiscalYearEnd',
        'LatestQuarter', 'PEGRatio', 'BookValue', 'DividendYield',
        'RevenuePerShareTTM', 'ProfitMargin', 'OperatingMarginTTM',
        'ReturnOnAssetsTTM', 'ReturnOnEquityTTM', 'RevenueTTM',
        'GrossProfitTTM', 'DilutedEPSTTM', 'AnalystTargetPrice',
        'PriceToSalesRatioTTM', 'EVToRevenue','EVToEBITDA',
        '50DayMovingAverage','200DayMovingAverage', 'SharesOutstanding',
        'DividendDate','ExDividendDate'])
        print(dfo)
        dfs.append(dfo)

#INCOME STATEMENT

for company in companies:
        inc = fetchData("INCOME_STATEMENT", company)
        dfi = pd.DataFrame(inc["annualReports"])
        dfi = dfi.drop(columns = ['reportedCurrency'])
        dfi_2022 = pd.DataFrame(dfi.iloc[0]).transpose()
        dfi_2021 = pd.DataFrame(dfi.iloc[1]).transpose()
        dfi_2020 = pd.DataFrame(dfi.iloc[2]).transpose()
        print(dfi_2022)
        dfs.append(dfi_2022)

#BALANCE SHEET

for company in companies:
        bal = fetchData("BALANCE_SHEET", company)
        dfb = pd.DataFrame(bal["annualReports"])
        dfb = dfb.drop(columns = ['reportedCurrency'])
        dfb_2022 = pd.DataFrame(dfb.iloc[0]).transpose()
        dfb_2021 = pd.DataFrame(dfb.iloc[1]).transpose()
        dfb_2020 = pd.DataFrame(dfb.iloc[2]).transpose()
        print(dfb_2022)
        dfs.append(dfb_2022)

#CASH FLOWS

for company in companies:
        cf = fetchData("CASH_FLOW", company)
        dfc = pd.DataFrame(cf["annualReports"])
        dfc = dfc.drop(columns = ['reportedCurrency'])
        dfc_2022 = pd.DataFrame(dfc.iloc[0]).transpose()
        dfc_2021 = pd.DataFrame(dfc.iloc[1]).transpose()
        dfc_2020 = pd.DataFrame(dfc.iloc[2]).transpose()
        print(dfc_2022)
        dfs.append(dfc_2022)

#EARNINGS

for company in companies:
        earn = fetchData("EARNINGS", company)
        dfe = pd.DataFrame(earn["annualEarnings"])
        dfe_2022 = pd.DataFrame(dfe.iloc[1]).transpose()
        print(dfe_2022)
        dfs.append(dfe_2022)

#CONCAT DFs

df = pd.concat(dfs, axis=0, ignore_index=True)
print(df)

解决方案 #

原代码用pd.concat(dfs, axis=0)是按行方向拼接，因此生成多行结果。要合并为单行，需改为列方向拼接（axis=1），同时给不同模块的列名添加前缀，避免重复冲突。

修改后的代码：

import requests
import pandas as pd

companies = ["IBM"]

def fetchData(func, symbol):
    key = "key"
    url = f'https://www.alphavantage.co/query?function={func}&symbol={symbol}&apikey={key}'
    r = requests.get(url)
    data = r.json()
    return data

dfs = []

# COMPANY OVERVIEW
for company in companies:
    ov = fetchData("OVERVIEW", company)
    dfo = pd.DataFrame.from_dict(ov, orient='index').transpose()
    dfo = dfo.drop(columns = ['AssetType', 'Description', 'CIK', 'Sector', 'Address', 'FiscalYearEnd',
    'LatestQuarter', 'PEGRatio', 'BookValue', 'DividendYield',
    'RevenuePerShareTTM', 'ProfitMargin', 'OperatingMarginTTM',
    'ReturnOnAssetsTTM', 'ReturnOnEquityTTM', 'RevenueTTM',
    'GrossProfitTTM', 'DilutedEPSTTM', 'AnalystTargetPrice',
    'PriceToSalesRatioTTM', 'EVToRevenue','EVToEBITDA',
    '50DayMovingAverage','200DayMovingAverage', 'SharesOutstanding',
    'DividendDate','ExDividendDate'])
    # 给公司概况列加前缀
    dfo.columns = [f"OV_{col}" for col in dfo.columns]
    dfs.append(dfo)

# INCOME STATEMENT
for company in companies:
    inc = fetchData("INCOME_STATEMENT", company)
    dfi = pd.DataFrame(inc["annualReports"])
    dfi = dfi.drop(columns = ['reportedCurrency'])
    dfi_2022 = pd.DataFrame(dfi.iloc[0]).transpose()
    # 给损益表列加前缀
    dfi_2022.columns = [f"IS_{col}" for col in dfi_2022.columns]
    dfs.append(dfi_2022)

# BALANCE SHEET
for company in companies:
    bal = fetchData("BALANCE_SHEET", company)
    dfb = pd.DataFrame(bal["annualReports"])
    dfb = dfb.drop(columns = ['reportedCurrency'])
    dfb_2022 = pd.DataFrame(dfb.iloc[0]).transpose()
    # 给资产负债表列加前缀
    dfb_2022.columns = [f"BS_{col}" for col in dfb_2022.columns]
    dfs.append(dfb_2022)

# CASH FLOWS
for company in companies:
    cf = fetchData("CASH_FLOW", company)
    dfc = pd.DataFrame(cf["annualReports"])
    dfc = dfc.drop(columns = ['reportedCurrency'])
    dfc_2022 = pd.DataFrame(dfc.iloc[0]).transpose()
    # 给现金流列加前缀
    dfc_2022.columns = [f"CF_{col}" for col in dfc_2022.columns]
    dfs.append(dfc_2022)

# EARNINGS
for company in companies:
    earn = fetchData("EARNINGS", company)
    dfe = pd.DataFrame(earn["annualEarnings"])
    dfe_2022 = pd.DataFrame(dfe.iloc[1]).transpose()
    # 给年度收益列加前缀
    dfe_2022.columns = [f"EARN_{col}" for col in dfe_2022.columns]
    dfs.append(dfe_2022)

# 按列方向拼接，合并为单行
df = pd.concat(dfs, axis=1, ignore_index=False)
print(df)

关键修改说明： #

1. 将pd.concat的axis参数从0改为1，实现列方向拼接，把所有DataFrame合并为一行。
2. 给每个模块的列名添加专属前缀（如OV_代表公司概况、IS_代表损益表），避免不同模块的列名重复导致数据覆盖。
3. 保留ignore_index=False，确保表头为修改后的带前缀列名，不会被重置。

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