python - 如何使用两个现有列在 Pandas 中创建一个新列*并将新列定位在数据框中的特定位置

Question

我在csv 文件 中有以下数据:

from StringIO import StringIO
import pandas as pd

the_data = """
ABC,2016-6-9 0:00,95,"{'//PurpleCar': [115L], '//YellowCar': [403L]}","{'GBP/NOK PAWS': [151L], 'CAD/EUR': [41L], 'EDM8-EDM9': [1833L]}"   
ABC,2016-6-10 0:00,100,"{'//PurpleCar': [219L], '//YellowCar': [381L]}","{'FBTPM5 2015-06-08': [472L], 'HKD/MXN': [0L], 'AUD/SEK DEWS': [19482L]}"   
ABC,2016-6-11 0:00,27,"{'//PurpleCar': [572L], '//YellowCar': [184L]}","{'V 2.000 03/31/25': [759L], 'AUD/JPY': [742L], 'AUD/SEK PAWS': [1784L]}"   
ABC,2016-6-12 0:00,41,"{'//PurpleCar': [80L], '//YellowCar': [2011L]}","{'CAR/FIN SWAP': [151L], 'HKD/MXN': [41L], 'RU4': [5829L]}"   
ABC,2016-6-13 0:00,19,"{'//PurpleCar': [32L], '//YellowCar': [15L]}","{'TRY/CHY OIS': [673L], 'NZD/MXN': [582L], 'AUD/SEK PAPS': [4846242L]}"   
DEF,2016-6-9 0:00,8,"{'//PurpleCar': [19L], '//BlackCar': [17L]}","{'ULM5-ULU5 2015-06-19': [18L], 'HKD/MXN': [64L], 'USD/JPY OPTS': [14714L]}"   
DEF,2016-6-10 0:00,2,"{'//PurpleCar': [32L], '//BlackCar': [15L]}","{'U 4.500 2/15/14': [151L], 'FVU6-FVZ6 2016-09-30': [194], 'AUD/SEK': [0L]}"   
DEF,2016-6-11 0:00,20,"{'//PurpleCar': [32L], '//BlackCar': [15L]}","{'EUR/JPY': [158L], 'ARS/MXN': [562L], 'GBP/JPY PAWS': [1759L]}"   
DEF,2016-6-12 0:00,241,"{'//PurpleCar': [28L], '//BlackCar': [96L]}","{'GBP/NOK OIS': [319], 'HKD/SAG': [103L], 'USD/INR': [3L]}"  
DEF,2016-6-13 0:00,400,"{'//PurpleCar': [32L], '//BlackCar': [15L]}","{'TNM6 2016-06-21': [193], 'EDH9': [1713L], 'GZ5': [0]}"
"""

从这个数据集的第一行我们可以看到，双引号内有两个字典，用逗号隔开:

"{'//PurpleCar': [115L], '//YellowCar': [403L]}"

和

"{'GBP/NOK PAWS': [151L], 'CAD/EUR': [41L], 'EDM8-EDM9': [1833L]}"

然后我按如下方式操作数据框来处理字典本身是可变长度并且键值是动态的这一事实:

fixed_columns = pd.read_csv(StringIO(the_data),
                            names=["Company", "Date", "Value", "Cars_str",
                                       "Currency_str"])


cars = fixed_columns["Cars_str"].apply(ast.literal_eval)
del fixed_columns["Cars_str"]

currencies = fixed_columns["Currency_str"].apply(ast.literal_eval)
del fixed_columns["Currency_str"]

def get_single_item(list_that_always_has_single_item):
    v, = list_that_always_has_single_item
    return v

def extract_car_name(car_str):
    assert car_str.startswith("//"), car_str
    return car_str[2:]

def extract_instrument_name(currency_str):
    assert currency_str.startswith(""), currency_str
    return currency_str[2:]


dynamic_column_01 = cars.apply(
    lambda x: pd.Series({
            extract_car_name(k): get_single_item(v) 
            for k, v in x.items()
    }))

dynamic_column_02 = currencies.apply(
    lambda x: pd.Series({
            extract_instrument_name(k): get_single_item(v) 
            for k, v in x.items()
    }))


result = pd.concat([fixed_columns, dynamic_column_01, dynamic_column_02], axis=1)
result 

我的问题:我希望能够将 Value 列乘以某个系数，然后将新列紧跟在 之后>Value 列(就在第一个字典之前)。有办法做到这一点吗？

谢谢!

Answer 1

在您解析并删除 Cars_str 和 之后，通过从 fixed_columns 计算 MultipliedValue 来利用新列放在末尾>Currency_str 列:

...

cars = fixed_columns["Cars_str"].apply(ast.literal_eval)
del fixed_columns["Cars_str"]

currencies = fixed_columns["Currency_str"].apply(ast.literal_eval)
del fixed_columns["Currency_str"]

coeff = 1.3
fixed_columns['MultipliedValue'] = coeff * fixed_columns["Value"]

...

result = pd.concat([fixed_columns, dynamic_column_01, dynamic_column_02], axis=1)
result.columns

输出:

Index(['Company', 'Date', 'Value', 'MultipliedValue', 'BlackCar', 'PurpleCar',
       'YellowCar', '2.000 03/31/25', '4', '4.500 2/15/14', '5', 'D/EUR',
       'D/INR', 'D/JPY', 'D/JPY OPTS', 'D/MXN', 'D/SAG', 'D/SEK', 'D/SEK DEWS',
       'D/SEK PAPS', 'D/SEK PAWS', 'H9', 'M5-ULU5 2015-06-19', 'M6 2016-06-21',
       'M8-EDM9', 'P/JPY PAWS', 'P/NOK OIS', 'P/NOK PAWS', 'R/FIN SWAP',
       'R/JPY', 'S/MXN', 'TPM5 2015-06-08', 'U6-FVZ6 2016-09-30', 'Y/CHY OIS'],
      dtype='object')