Switching dataframe integers to string so i can add text like '$' and '5.78 / Million' into the dataframe

huangapple go评论148阅读模式
英文:

Switching dataframe integers to string so i can add text like '$' and '5.78 / Million' into the dataframe

问题

我创建了一个包含所有必要信息的数据框,但是我的经理希望我删除除 col['REVENUE'] x index['Volume'] 外所有条目的小数部分。也就是保留 5.78 并删除其他所有内容的尾随零。

我还需要将像 '$' 和 'mm' 这样的文本添加到一些值中,例如 "5,388 MM" 和 "$2,965,773"。

说实话,我对接下来要做什么感到迷茫。感谢您的帮助。

如果您能把它分解成小步骤,即使只是问题的一部分,我将非常感激。

我尝试将一些值转换为字符串,但当我尝试打印数据框时,这会给我一个错误消息。我包含了一些代码,希望能更清楚一些。如果有什么可以澄清这个问题的方法,请告诉我!!!

RFQ_count = df['STATUS'].count()
# RFQ_count = str(RFQ_count)
# print(type(RFQ_count))
Vol_RAMT = df['R-AMT'].sum()
Vol_RAMT = int(round(Vol_RAMT))
DV01 = df['DV01'].sum() 
DV01 = int(round(DV01))

DF_data = {
    'Total': [RFQ_count, Vol_RAMT, DV01],
    'Count': [hit_count, hit_count_volume, hit_ount_DV01],
    'Ratio1 %': [hit_ratio_count, hit_ratio_volume, hit_ratio_DV01],
    'Count': [cover_plus_count, cover_plus_volume, cover_plus_DV01],
    'Ratio2 %': [cover_count_ratio, cover_volume_ratio, cover_DV01_ratio],
    'Wins': [tie_win_count, tie_win_volume, tie_win_DV01],
    'Away': [tie_away_count, tie_away_volume, tie_away_DV01],
    'Ratio3 %': [int(round((tie_win_count / tie_away_count) * 100)), 
                 int(round((tie_win_volume / tie_away_volume) * 100)), 
                 int(round((tie_win_DV01 / tie_away_DV01) * 100))],
    'Revenue': [rev_count, rev_vol, rev_DV01],
}

index_labels=["Count", "Volume", "DV01"] 
df1 = pd.DataFrame(DF_data, index=index_labels).round(2).style.format("{:,}")
# df1['RFQ Total'] = df1['RFQ Total'].astype(str) + '$'
print(type(DF_data))
df1

Switching dataframe integers to string so i can add text like '$' and '5.78 / Million' into the dataframe


<details>
<summary>英文:</summary>

I made a dataframe with all the necessary information, however, my manager wants me to remove decimals from all entries except col[&#39;REVENUE&#39;] x index[&#39;Volume&#39;]. Aka keep 5.78 and remove the trailing zero from everything else. 

I also need to add in text like &#39;$&#39; and &#39;mm&#39; to some values like &quot;5,388 MM&quot; and &quot;$2,965,773.&quot;

To put it nicely, I am lost in the sauce about what to do now. Thanks for your help.

If you could please break it down to baby steps, even if its just parts of the problem, I would be immensly grateful. 

I tried converting some values into strings, but that then gave me an error message when i tried to print the df. I included some of the code up to hopefully make things a bit more clear. Please let me know if there is anything I can do to clarify this question!!!

    RFQ_count = df[&#39;STATUS&#39;].count()
    # RFQ_count = str(RFQ_count)
    # print(type(RFQ_count))
    Vol_RAMT = df[&#39;R-AMT&#39;].sum()
    Vol_RAMT = int(round(Vol_RAMT))
    DV01 = df[&#39;DV01&#39;].sum() 
    DV01 = int(round(DV01))
    
    DF_data = {
        &#39;Total&#39;: [RFQ_count, Vol_RAMT, DV01],
                       &#39;Count&#39;: [hit_count, hit_count_volume, hit_ount_DV01],
                        &#39;Ratio1 %&#39;: [hit_ratio_count, hit_ratio_volume, hit_ratio_DV01],
                        &#39;Count&#39;: [cover_plus_count, cover_plus_volume, cover_plus_DV01],
                        &#39;Ratio2 %&#39;: [cover_count_ratio, cover_volume_ratio, cover_DV01_ratio],
                       &#39;Wins&#39;: [tie_win_count, tie_win_volume, tie_win_DV01],
                       &#39;Away&#39;: [tie_away_count, tie_away_volume, tie_away_DV01],
                        &#39;Ratio3 %&#39;: [int(round((tie_win_count / tie_away_count) * 100)), 
                                        int(round((tie_win_volume / tie_away_volume) * 100)), 
                                        int(round((tie_win_DV01 / tie_away_DV01) * 100))],
                        &#39;Revenue&#39;: [rev_count, rev_vol, rev_DV01],
                                             }                       
                            
    
    index_labels=[&quot;Count&quot;, &quot;Volume&quot;, &quot;DV01&quot;] 
    df1 = pd.DataFrame(DF_data, index=index_labels).round(2).style.format(&quot;{:,}&quot;)
    # df1[&#39;RFQ Total&#39;] = df1[&#39;RFQ Total&#39;].astype(str) + &#39;$&#39;
    print(type(DF_data))
    df1`

[![This is the output table](https://i.stack.imgur.com/JWlij.png)](https://i.stack.imgur.com/JWlij.png)


</details>


# 答案1
**得分**: 2

在没有其他上下文的情况下,最好的做法是避免在代码中进行转换,而是使用格式化器。这个字符串可以放在一个预格式化的块中。

```python
import locale
from functools import partial

import pandas as pd

df = pd.DataFrame(
    data={
        'Total': (785, 5_388, 2_965_773),
        'Count': (518, 3_729, 2_120_275),
        'Ratio1': (0.27, 0.30, 0.31),
        'Ratio2': (0.66, 0.69, 0.71),
        'Wins': (100, 906, 419_929),
        'Away': (82, 658, 310_545),
        'Ratio3': (1.22, 1.38, 1.35),
        'Revenue': (9_268.00, 578.00, 1_000.00),
    },
    index=('Count', 'Volume', 'DV01'),
)

locale.setlocale(locale.LC_MONETARY, '')
print(
    df.to_string(
        formatters={
            'Total': '{:,.0f}'.format,
            'Count': '{:,.0f}'.format,
            'Wins': '{:,.0f}'.format,
            'Away': '{:,.0f}'.format,
            'Ratio1': '{:.0%}'.format,
            'Ratio2': '{:.0%}'.format,
            'Ratio3': '{:.0%}'.format,
            'Revenue': partial(locale.currency, grouping=True),
        }
    )
)
           Total     Count Ratio1 Ratio2    Wins    Away Ratio3   Revenue
Count        785       518    27%    66%     100      82   122% $9,268.00
Volume     5,388     3,729    30%    69%     906     658   138%   $578.00
DV01   2,965,773 2,120,275    31%    71% 419,929 310,545   135% $1,000.00
英文:

With no other context, the preferable thing to do is avoid converting in the frame itself, and use formatters instead. This string can be put in a PDF in a pre-formatted block.

import locale
from functools import partial

import pandas as pd

df = pd.DataFrame(
    data={
        &#39;Total&#39;: (785, 5_388, 2_965_773),
        &#39;Count&#39;: (518, 3_729, 2_120_275),
        &#39;Ratio1&#39;: (0.27, 0.30, 0.31),
        &#39;Ratio2&#39;: (0.66, 0.69, 0.71),
        &#39;Wins&#39;: (100, 906, 419_929),
        &#39;Away&#39;: (82, 658, 310_545),
        &#39;Ratio3&#39;: (1.22, 1.38, 1.35),
        &#39;Revenue&#39;: (9_268.00, 578.00, 1_000.00),
    },
    index=(&#39;Count&#39;, &#39;Volume&#39;, &#39;DV01&#39;),
)

locale.setlocale(locale.LC_MONETARY, &#39;&#39;)
print(
    df.to_string(
        formatters={
            &#39;Total&#39;: &#39;{:,.0f}&#39;.format,
            &#39;Count&#39;: &#39;{:,.0f}&#39;.format,
            &#39;Wins&#39;: &#39;{:,.0f}&#39;.format,
            &#39;Away&#39;: &#39;{:,.0f}&#39;.format,
            &#39;Ratio1&#39;: &#39;{:.0%}&#39;.format,
            &#39;Ratio2&#39;: &#39;{:.0%}&#39;.format,
            &#39;Ratio3&#39;: &#39;{:.0%}&#39;.format,
            &#39;Revenue&#39;: partial(locale.currency, grouping=True),
        }
    )
)
           Total     Count Ratio1 Ratio2    Wins    Away Ratio3   Revenue
Count        785       518    27%    66%     100      82   122% $9,268.00
Volume     5,388     3,729    30%    69%     906     658   138%   $578.00
DV01   2,965,773 2,120,275    31%    71% 419,929 310,545   135% $1,000.00

huangapple
  • 本文由 发表于 2023年8月11日 01:20:14
  • 转载请务必保留本文链接:https://go.coder-hub.com/76877992.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定