在Pandas中实现带M/B后缀的数值转整数的解决方案

Hey there, let's fix that Outstanding column conversion issue you're having. I noticed a couple of key problems in your existing code that are preventing it from working as expected, and I'll walk you through the solution step by step.

First, the main issues in your code #

• You're re-fetching data unnecessarily: In your debug code, every time you call testDf('152'), you're scraping Finviz again and creating a brand new DataFrame. When you modify this temporary DataFrame, the changes don't stick to the df variable you already created—they just get discarded immediately.
• Your converter returns formatted strings instead of integers: You mentioned wanting integer values (like 297500000), but your current logic returns strings with commas and decimals. We need to adjust this to output actual numeric integers.
• The initial replace approach doesn't convert values: Swapping 'M' with 'e5' only changes text, it doesn't parse the numeric part or convert the string to a proper number.

Step-by-step fix #

First, let's build a reliable conversion function that handles both 'M' (million) and 'B' (billion) suffixes, and returns clean integers:

def convert_outstanding(value):
    # Strip any extra whitespace first
    value = value.strip()
    # Check for suffix and calculate the integer value
    if value.endswith('M'):
        return int(float(value[:-1]) * 1_000_000)
    elif value.endswith('B'):
        return int(float(value[:-1]) * 1_000_000_000)
    else:
        # Handle edge cases with no suffix (if any)
        try:
            return int(float(value))
        except ValueError:
            return None  # Return None for invalid values, adjust as needed

Next, apply this function to your existing df variable (don't re-scrape the data!):

# After fetching your data with df = testDf('152') or df = get_screener('152')
df['Outstanding'] = df['Outstanding'].apply(convert_outstanding)

Full integrated code example #

Let's put this all together in your scraper code to ensure it works end-to-end:

import pandas as pd
import requests
import bs4
import time
import random

headers = {'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/50.0.2661.102 Safari/537.36'}

def get_screener(version):
    url = 'https://finviz.com/screener.ashx?v={version}&r={page}&f=all&c=0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70&f=ind_stocksonly&o=-marketcap'
    page = 1
    screen = requests.get(url.format(version=version, page=page), headers=headers)
    soup = bs4.BeautifulSoup(screen.text, features='lxml')
    pages = int(soup.find_all('a', {'class': 'screener-pages'})[-1].text)
    data = []
    for page in range(1, 20 * pages, 20):
        print(version, page)
        screen = requests.get(url.format(version=version, page=page), headers=headers).text
        tables = pd.read_html(screen)
        tables = tables[-2]
        tables.columns = tables.iloc[0]
        tables = tables[1:]
        data.append(tables)
        time.sleep(random.random())
    return pd.concat(data).reset_index(drop=True).rename_axis(columns=None)

def convert_outstanding(value):
    value = value.strip()
    if value.endswith('M'):
        return int(float(value[:-1]) * 1_000_000)
    elif value.endswith('B'):
        return int(float(value[:-1]) * 1_000_000_000)
    else:
        try:
            return int(float(value))
        except ValueError:
            return None

# Fetch the data once
df = get_screener('152')

# Convert the Outstanding column
df['Outstanding'] = df['Outstanding'].apply(convert_outstanding)

# Verify the conversion works
print(df['Outstanding'].head(20))

# Write to Excel with proper formatting
writer = pd.ExcelWriter("pandas_column_formats.xlsx", engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)

workbook = writer.book
worksheet = writer.sheets['Sheet1']

# Format headers
header_format = workbook.add_format()
header_format.set_font_name('Calibri')
header_format.set_font_color('green')
header_format.set_font_size(8)
header_format.set_italic()
header_format.set_underline()

for col_num, value in enumerate(df.columns.values):
    worksheet.write(0, col_num, value, header_format)

# Set format for the Outstanding column (integer with commas)
outstanding_col_idx = df.columns.get_loc('Outstanding')
worksheet.set_column(outstanding_col_idx, outstanding_col_idx, 18, workbook.add_format({'num_format': '#,##0'}))

writer.save()

Key notes #

• Reuse your DataFrame: Once you assign df = get_screener('152'), use that variable for all operations—don't re-call the scraper unless you need fresh data.
• Clean integer output: The function uses int() to ensure you get whole numbers, matching your requirement of values like 297500000 instead of floats.
• Error handling: The try/except block catches unexpected values (like non-numeric strings) and returns None—you can adjust this to log errors or use a default value if needed.

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