After extracting numbers from dataframe, how can I calculate the average price after converting to one unit of measure uniformly(从数据框中提取数字后，如何统一计算换算成一个计量单位后的平均价格)-6ren

After extracting numbers from dataframe, how can I calculate the average price after converting to one unit of measure uniformly(从数据框中提取数字后，如何统一计算换算成一个计量单位后的平均价格)

转载作者：bug小助手更新时间：2023-10-25 12:58:21

I have a dataset which contains different properties rent price. It looks like this:

我有一个包含不同房产租金价格的数据集。它看起来是这样的：

data = {
    'prices': [
        '$350.00',
        '$450.00 pw',
        '$325 per week',
        '$495pw - Views! White goods!',
        '$460p/w + gst and outgoings',
        '$300 wk',
        '$390pw / $1695pcm',
        '$180 pw / $782 pm',
        '$375 Per Week/Fully Furnished',
        '$350 pw + GST & Outgoings',
        'APPLY NOW - From $270 per week',
        '$185 per night',
        '$400pw incl. power',
        '$500 weekly',
        '$600 per week pw',
        '$850 per week (Fully furnished)',
        'FROM $400PW, FURNITURE AND BILLS INCLUDED',
        'THE DEAL- $780 PER WEEK',
        'THE DEAL: $1,400 PER WEEK',
        '$750/W Unfurnished',
        '$320 - fully furnished pw',
        '$330 PER WEEK | $1,430 P.C.M',
        'Enquire Now: $690 per week',
        '$460 per week / $1999 per month',
        '$490 per week/Under Application approved',
        '$1550pw - Location! Rare gem!',
        '295 per week',  # Example without a dollar sign
        'unit 2 - $780pw unit 3 - $760pw',  # Example with multiple prices
        '$2500 pw high, $1600pw low,$380 pn',  # Example with multiple prices
        'from $786 - $1572 per week',  # Example with multiple prices
        '$590 to $639',  # Example with a range
        '$280 - $290 pw'  # Example with a range
    ]
}

My aim is to clean this "prices" col so that only the weekly rent price is presented.

我的目标是清理这个“价格”列，以便只显示每周的租金价格。

I failed to manage the last five kinds of data, this is what I have done:

我未能管理最后五种数据，以下是我所做的：

df = pd.DataFrame(data)

def extract_weekly_price(text):
    price_match = re.search(r'\$?([\d,]+)', text)
    if price_match:
        price_str = price_match.group(1)
        price = int(price_str.replace(',', ''))
        
        # convert to weekly if not
        if re.search(r'(per week|p\.w\.|p/w|pw|/w|weekly)', text):
            return price  
        elif 'p.a' in text:
            return price / 52  
        elif re.search(r'(p\.c\.m|pcm|mth|pm)', text):
            return price / 4.33 
        elif 'per night' in text:
            return price * 7
        else:
            return price  
    else:
        return None

df['prices'] = df['prices'].str.lower()
df['Weekly_rent'] = df['prices'].apply(extract_weekly_price).round(3)

How can I modify my code so that I can get the average weekly price for those data with a range such as '$590 to $639' or '$280 - $290 pw'? I would be really appreciate if you could help.

我如何修改我的代码，以便我可以获得这些数据的平均每周价格，范围如‘590美元到639美元’或‘$280-$290 PW’？如果你能帮忙，我将不胜感激。

更多回答

How should be calculated the average price for this one '$2500 pw high, $1600pw low,$380 pn' ? (where there is two weekly prices and one nightly/daily price).

应该如何计算这一个‘$2500 pw高，$1600pw低，$380 pn’的平均价格？(其中有两个周价和一个夜间/日价)。

优秀答案推荐

Here is an example how you can use re + match keyword to get the prices from the text (link to regex101):

下面是一个如何使用re+Match关键字从文本(指向regex101的链接)获取价格的示例：

import re

data = {
    "prices": [
        "$350.00",
        "$450.00 pw",
        "$325 per week",
        "$495pw - Views! White goods!",
        "$460p/w + gst and outgoings",
        "$300 wk",
        "$390pw / $1695pcm",
        "$180 pw / $782 pm",
        "$375 Per Week/Fully Furnished",
        "$350 pw + GST & Outgoings",
        "APPLY NOW - From $270 per week",
        "$185 per night",
        "$400pw incl. power",
        "$500 weekly",
        "$600 per week pw",
        "$850 per week (Fully furnished)",
        "FROM $400PW, FURNITURE AND BILLS INCLUDED",
        "THE DEAL- $780 PER WEEK",
        "THE DEAL: $1,400 PER WEEK",
        "$750/W Unfurnished",
        "$320 - fully furnished pw",
        "$330 PER WEEK | $1,430 P.C.M",
        "Enquire Now: $690 per week",
        "$460 per week / $1999 per month",
        "$490 per week/Under Application approved",
        "$1550pw - Location! Rare gem!",
        "295 per week",  # Example without a dollar sign
        "unit 2 - $780pw unit 3 - $760pw",  # Example with multiple prices
        "$2500 pw high, $1600pw low,$380 pn",  # Example with multiple prices
        "from $786 - $1572 per week",  # Example with multiple prices
        "$590 to $639",  # Example with a range
        "$280 - $290 pw",  # Example with a range
    ]
}

pat = re.compile(
    r"(?i)(?:\$([\d+,.]+)\s*(?:-|to)\s*)?\$?([\d+,.]+)(?=\s*(?:(pw|per week|wk|p?/w|weekly|- fully furnished pw$|$)|(per night|pn)|(pm|per month|p\.c\.m|pcm)))"
)

for i, p in enumerate(data["prices"]):
    for price1, price2, week, night, month in pat.findall(p):
        match [price1, price2, week, night, month]:
            # single price/week
            case ["", _, _, "", ""]:
                price = float(price2.replace(",", ""))
                print(f"{i:<3} CASE 1: {p:<50} {price:<10} / week")

            # single price/night
            case ["", _, "", _, ""]:
                price = float(price2.replace(",", "").replace(".", ""))
                print(f"{i:<3} CASE 2: {p:<50} {price:<10} / night")

            # single price/month
            case ["", _, "", "", _]:
                price = float(price2.replace(",", "").replace(".", ""))
                print(f"{i:<3} CASE 3: {p:<50} {price:<10} / month")

            # range price/week
            case [_, _, _, "", ""]:
                price1 = float(price1.replace(",", "").replace(".", ""))
                price2 = float(price2.replace(",", "").replace(".", ""))
                price = (price1 + price2) / 2
                print(f"{i:<3} CASE 4: {p:<50} {price:<10} / week (mean)")

            # add cases here
            # ...

            case _:
                print(f"{i:<3} UNKNOWN: {p:<50}")

Prints:

打印：

0   CASE 1: $350.00                                            350.0      / week
1   CASE 1: $450.00 pw                                         450.0      / week
2   CASE 1: $325 per week                                      325.0      / week
3   CASE 1: $495pw - Views! White goods!                       495.0      / week
4   CASE 1: $460p/w + gst and outgoings                        460.0      / week
5   CASE 1: $300 wk                                            300.0      / week
6   CASE 1: $390pw / $1695pcm                                  390.0      / week
6   CASE 3: $390pw / $1695pcm                                  1695.0     / month
7   CASE 1: $180 pw / $782 pm                                  180.0      / week
7   CASE 3: $180 pw / $782 pm                                  782.0      / month
8   CASE 1: $375 Per Week/Fully Furnished                      375.0      / week
9   CASE 1: $350 pw + GST & Outgoings                          350.0      / week
10  CASE 1: APPLY NOW - From $270 per week                     270.0      / week
11  CASE 2: $185 per night                                     185.0      / night
12  CASE 1: $400pw incl. power                                 400.0      / week
13  CASE 1: $500 weekly                                        500.0      / week
14  CASE 1: $600 per week pw                                   600.0      / week
15  CASE 1: $850 per week (Fully furnished)                    850.0      / week
16  CASE 1: FROM $400PW, FURNITURE AND BILLS INCLUDED          400.0      / week
17  CASE 1: THE DEAL- $780 PER WEEK                            780.0      / week
18  CASE 1: THE DEAL: $1,400 PER WEEK                          1400.0     / week
19  CASE 1: $750/W Unfurnished                                 750.0      / week
20  CASE 1: $320 - fully furnished pw                          320.0      / week
21  CASE 1: $330 PER WEEK | $1,430 P.C.M                       330.0      / week
21  CASE 3: $330 PER WEEK | $1,430 P.C.M                       1430.0     / month
22  CASE 1: Enquire Now: $690 per week                         690.0      / week
23  CASE 1: $460 per week / $1999 per month                    460.0      / week
23  CASE 3: $460 per week / $1999 per month                    1999.0     / month
24  CASE 1: $490 per week/Under Application approved           490.0      / week
25  CASE 1: $1550pw - Location! Rare gem!                      1550.0     / week
26  CASE 1: 295 per week                                       295.0      / week
27  CASE 1: unit 2 - $780pw unit 3 - $760pw                    780.0      / week
27  CASE 1: unit 2 - $780pw unit 3 - $760pw                    760.0      / week
28  CASE 1: $2500 pw high, $1600pw low,$380 pn                 2500.0     / week
28  CASE 1: $2500 pw high, $1600pw low,$380 pn                 1600.0     / week
28  CASE 2: $2500 pw high, $1600pw low,$380 pn                 380.0      / night
29  CASE 4: from $786 - $1572 per week                         1179.0     / week (mean)
30  CASE 4: $590 to $639                                       614.5      / week (mean)
31  CASE 4: $280 - $290 pw                                     285.0      / week (mean)

You can test this approach and see if the average prices are computed correctly :

您可以测试此方法，并查看是否正确计算了平均价格：

import numpy as np

pat = (
    r"(?i)"
    r"(\d+[.,]?\d+)\s*" # prices
    r"(?:-.*\bfurnished\s*)?" # optional text
    r"(p/?w|wdk|per week|weekly|/w|" # weekly
    r"p\.?c\.?m|mth|pm|per month|" # monthly
    r"per night|pn|" # daily
    r"p.a)?" # ??
)

tmp = df["prices"].str.extractall(pat)
fn = lambda x: tmp[1].str.contains(x, case=False, na=False)
s0 = tmp[0].replace(",", "", regex=True).astype(float)

averge = np.select(
    [fn("w"), fn("n"), fn("m"), fn("p.a")],
    [s0, s0.mul(7), s0.div(4.33), s0.div(52)], default=s0
)

out = (
    df[["prices"]].join(
        tmp.assign(all_prices=averge.round(2)).groupby(level=0)
            .agg(
                computed_prices=("all_prices", list), # optional
                average=("all_prices", "mean")
            )
    )
)

Regex : [demo]

Output :

输出：

print(out)


                                       prices           computed_prices  average
0                                     $350.00                   [350.0]   350.00
1                                  $450.00 pw                   [450.0]   450.00
2                               $325 per week                   [325.0]   325.00
3                $495pw - Views! White goods!                   [495.0]   495.00
4                 $460p/w + gst and outgoings                   [460.0]   460.00
5                                     $300 wk                   [300.0]   300.00
6                           $390pw / $1695pcm           [390.0, 391.45]   390.73
7                           $180 pw / $782 pm            [180.0, 180.6]   180.30
8               $375 Per Week/Fully Furnished                   [375.0]   375.00
9                   $350 pw + GST & Outgoings                   [350.0]   350.00
10             APPLY NOW - From $270 per week                   [270.0]   270.00
11                             $185 per night                  [1295.0]  1295.00
12                         $400pw incl. power                   [400.0]   400.00
13                                $500 weekly                   [500.0]   500.00
14                           $600 per week pw                   [600.0]   600.00
15            $850 per week (Fully furnished)                   [850.0]   850.00
16  FROM $400PW, FURNITURE AND BILLS INCLUDED                   [400.0]   400.00
17                    THE DEAL- $780 PER WEEK                   [780.0]   780.00
18                  THE DEAL: $1,400 PER WEEK                  [1400.0]  1400.00
19                         $750/W Unfurnished                   [750.0]   750.00
20                  $320 - fully furnished pw                   [320.0]   320.00
21               $330 PER WEEK | $1,430 P.C.M           [330.0, 1430.0]   880.00
22                 Enquire Now: $690 per week                   [690.0]   690.00
23            $460 per week / $1999 per month          [460.0, 13993.0]  7226.50
24   $490 per week/Under Application approved                   [490.0]   490.00
25              $1550pw - Location! Rare gem!                  [1550.0]  1550.00
26                               295 per week                   [295.0]   295.00
27            unit 2 - $780pw unit 3 - $760pw            [780.0, 760.0]   770.00
28         $2500 pw high, $1600pw low,$380 pn  [2500.0, 1600.0, 2660.0]  2253.33
29                 from $786 - $1572 per week           [786.0, 1572.0]  1179.00
30                               $590 to $639            [590.0, 639.0]   614.50
31                             $280 - $290 pw            [280.0, 290.0]   285.00

更多回答

Thank you so much! This helped a lot! I did some modify based on your code, basically it's just for the np.select part

非常感谢!这帮了大忙了！我根据你的代码做了一些修改，基本上只是针对np.select部分

average - 关于reduce part of RavenDB index, average calculation的语法问题
我正在努力寻找平均列的正确语法。我所拥有的——来自 RavenDB Studio 编辑器: map : from area in docs.Level5_AdministrativeAreas sel
java - 为什么 IntStream.average() 总是返回正确的结果而 LongStream.average() 有时却不能？
double average = LongStream .of(-4480186093928204294L, -1340542863544260591L, -600429628624003927
php - 梦幻足球: Compare a team's average points to the league average?
我在 MySQL 表中有梦幻足球联赛的数据。我想查询一个 php 页面的数据。我如何创建一个查询来计算球队得分与该特定年份联盟平均得分的比较？我的(简化的)数据表如下所示: 游戏 table :
javascript - 关于 "var avg = array.average()"中 array.average() 功能的混淆
干杯，伙计们。我被要求让这段代码在学习挑战中发挥作用，但我不确定如何处理“array.average()”部分，因为它不是一个函数。我被问到的是: var array = [5,44,23,11,5
java - 数组和搜索算法 : How is the "average N/2 steps to search an array" average value calculated?
我刚刚开始学习 Java 中的数据结构和算法(从数组开始)。我有两个问题。在我看来，算法执行中的“步骤”是实际上是算法访问的数组的位置。因为他们说数组中的插入一步发生，因为数据项被简单地插入到第一个
php - 如何使用谷歌分析 api 获取 'average session duration' 和 'Average pages viewed per visit'
嗨，我正在使用谷歌分析 api gapi查找网站的“平均 session 持续时间”和“每次访问浏览的平均页面” 我为此创建了一个仪表板，其值为 00:02:30和 4.58分别 ... 我使用以下代
sql - MySQL 查询 : work out the average rating for each user then order the results by average rating and number of ratings
SELECT username, (SUM(rating)/count(*)) as TheAverage, count(*) as TheCount FROM ratings WHERE month
excel - Office Excel平均函数: what's the difference between AVERAGE(A2:C2 B1:B10) and AVERAGE(A2:C2, B1 :B10)?
在 Office Excel AVERAGE 函数示例中，参数始终使用逗号作为分隔符。但是，AVERAGE(A2:C2 B1:B10) 在 Excel 中也有效。我的问题:有什么区别以及为什么？谢谢
python - (对于循环): How to put average values beside each number of the corresponding avg value and print the number(s) with the highest average?
我的代码: name = ["AAAAA 4 2 1 2 4 2 4 4 5 2 2 1 5 2 4 3 1 1 3 3 5", "BBB 5 2 1 2 4 5 4 4 1 2 2 2 4 4
android - 火力地堡分析 : Show Average of 'Value' Parameter and average of time like 'hh:mm:ss' in the Console for events sent from Android app
我一直在探索适用于 Android 的 Firebase Analytics，发现控制台的仪表板显示用户参与事件，该事件显示平均屏幕时间，如“hh:mm:ss”，还通过获取以下总和来显示“值”参数的平
average - 计算平均评分
很难说出这里问的是什么。这个问题是含糊的、模糊的、不完整的、过于宽泛的或修辞性的，无法以目前的形式得到合理的回答。如需帮助澄清此问题以便重新打开它，visit the help center 。已关
average - 平均倍数变化的问题
我使用维基百科文章中定义的折叠更改: http://en.wikipedia.org/wiki/Fold_change 我现在处理倍数变化已经有一段时间了，但从来没有真正需要计算我所有倍数变化的平均倍
计算两种颜色的 "average"
这仅与编程相关 - 与颜色及其表示有更多关系。我正在开发一个非常底层的应用程序。我在内存中有一个字节数组。那些是字符。它们是用抗锯齿渲染的:它们的值从 0 到 255，0 表示完全透明，255 完全
average - NetLogo:查找一组海龟的平均值
我正在尝试在用户界面中实现一个监视器，该监视器显示由海龟品种(海龟自己)共享的变量的平均值。有谁知道收集所有值的方法，将它们加在一起并除以海龟的数量以获得值或知道更简单的方法？最佳答案如果每只海龟
math - "Average"多个四元数？
我试图在我的 OpenGL 程序中将骨架动画从矩阵切换到四元数，但我遇到了一个问题: 给定多个单位四元数，我需要得到一个四元数，当用于变换向量时，将给出一个向量，该向量是每个四元数单独变换的向量的平均
moving-average - Clickhouse移动平均线
输入: Clickhouse 表A business_dttm(日期时间) 金额( float ) 我需要在每个 business_dttm 上计算 15 分钟(或最后 3 条记录)的移动总和例如
moving-average - 如何在不保留计数和数据总计的情况下计算移动平均线？
我正在尝试找到一种方法来计算移动累积平均值，而不存储迄今为止收到的计数和总数据。我想出了两种算法，但都需要存储计数: 新平均值 = ((旧计数 * 旧数据) + 下一个数据)/下一个计数新平均值
mysql - 如何优化SQL an/average(a)？
在我的 SQL 脚本中，我想标准化来自这样的子查询的值 select y/avg(y) from ( select x*z as y from test_table )T 我知道这个解决方案会起
python - 获取同一文件的多个随机损坏副本的 "average"
由于 Controller 损坏，文件在从 USB 闪存驱动器下载时会随机损坏。我下载了同一个文件的多个副本，每个副本的错误似乎通常是唯一且随机的。因此，我需要一个脚本来比较同一文件的几个(3 到
algorithm - Average-case算法分析
我正在尝试解决一个非常简单的算法分析(显然对我来说不是那么简单)。算法是这样的: int findIndexOfN(int A[], int n) { // this algorithm looks

bug小助手

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

After extracting numbers from dataframe, how can I calculate the average price after converting to one unit of measure uniformly(从数据框中提取数字后，如何统一计算换算成一个计量单位后的平均价格)