Pandas:查找一列中的所有唯一值并将另一列中的所有值归一化为其最后一个值 [英] Pandas: find all unique values in one column and normalize all values in another column to their last value

问题描述

我想在一列中找到所有唯一值并标准化另一列中的对应值与其最后一个值.我想通过使用 python3 的 pandas 模块来实现这一点.

I want to find all unique values in one column and normalize the corresponding values in another column to its last value. I want to achieve this via the pandas module using python3.

示例:

原始数据集

Fruit   | Amount
Orange  |     90
Orange  |     80
Orange  |     10
Apple   |    100
Apple   |     50
Orange  |     20
Orange  |     60   --> latest value of Orange. Use to normalize Orange
Apple   |     75
Apple   |     25
Apple   |     40   --> latest value of Apple. Used to normalize Apple

期望输出

具有水果"列中唯一值的标准化值的比率列

Ratio column with normalized values for unique values in the 'Fruit' column

Fruit   | Amount |  Ratio
Orange  |     90 |  90/60 --> 150%  
Orange  |     80 |  80/60 --> 133.3%
Orange  |     10 |  10/60 --> 16.7%
Apple   |    100 | 100/40 --> 250%
Apple   |     50 |  50/40 --> 125%
Orange  |     20 |  20/60 --> 33.3%
Orange  |     60 |  60/60 --> 100%
Apple   |     75 |  75/40 --> 187.5%
Apple   |     25 |  25/40 --> 62.5%
Apple   |     40 |  40/40 --> 100%

Python 代码尝试

import pandas as pd

filename = r'C:\fruitdata.dat'
df = pd.read_csv(filename, delimiter='|')
print(df)
print(df.loc[df['Fruit   '] == 'Orange  '])
print(df[df['Fruit   '] == 'Orange  '].tail(1))

Python 输出 (IPython)

In [1]: df
   Fruit      Amount
0  Orange         90
1  Orange         80
2  Orange         10
3  Apple         100
4  Apple          50
5  Orange         20
6  Orange         60
7  Apple          75
8  Apple          25
9  Apple          40

In [2]: df.loc[df['Fruit   '] == 'Orange  ']
   Fruit      Amount
0  Orange         90
1  Orange         80
2  Orange         10
5  Orange         20
6  Orange         60

In [3]: df[df['Fruit   '] == 'Orange  '].tail(1)
Out[3]: 
   Fruit      Amount
6  Orange         60

问题

如何遍历Fruit"中的每个唯一项目并根据其标准化所有值尾值?

How can I loop through each unique item in 'Fruit' and normalize all values against its tail value?

推荐答案

您可以将 iloc 与 groupby

df.groupby('Fruit').Amount.apply(lambda x: x/x.iloc[-1])
Out[38]: 
0    1.500000
1    1.333333
2    0.166667
3    2.500000
4    1.250000
5    0.333333
6    1.000000
7    1.875000
8    0.625000
9    1.000000
Name: Amount, dtype: float64

分配回来后

df['New']=df.groupby('Fruit').Amount.apply(lambda x: x/x.iloc[-1])
df
Out[40]: 
    Fruit  Amount       New
0  Orange      90  1.500000
1  Orange      80  1.333333
2  Orange      10  0.166667
3   Apple     100  2.500000
4   Apple      50  1.250000
5  Orange      20  0.333333
6  Orange      60  1.000000
7   Apple      75  1.875000
8   Apple      25  0.625000
9   Apple      40  1.000000

不使用lambda

df.Amount/df.groupby('Fruit',sort=False).Amount.transform('last')
Out[46]: 
0    1.500000
1    1.333333
2    0.166667
3    2.500000
4    1.250000
5    0.333333
6    1.000000
7    1.875000
8    0.625000
9    1.000000
Name: Amount, dtype: float64

这篇关于Pandas:查找一列中的所有唯一值并将另一列中的所有值归一化为其最后一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！