python - 在 Pandas 中，将一个数据框中的行转换为另一个数据框中的列的最佳方法？

Question

给定 df_people :

   Name
0  Tom
1  Jerry

和 df_colors (无标题行):

0  Red
1  Green
2  Blue

什么被认为是在 中获取数据的最佳方式df_colors 并将其添加到 df_people 使得 df_people 组合后看起来像这样:

   Name   Color_0  Color_1  Color_2
0  Tom    Red      Green    Blue
1  Jerry  Red      Green    Blue

以下是我到目前为止所拥有的，它有效，但我想知道是否有更好或更简洁的方法。

# Store data for new columns in a dictionary
new_columns = {}
for index_people, row_people in df_people.iterrows():
    for index_colors, row_colors in df_colors.iterrows():
        key = 'Color_' + str(index_colors)
        if (key in new_columns):
            new_columns[key].append(row_colors[0])
        else:
            new_columns[key] = [row_colors[0]]

# Add dictionary data as new columns
for key, value in new_columns.items():
    df_people[key] = value

更新

谢谢大家提供答案。由于实际数据帧的大小为 GB，因此速度至关重要，因此我最终采用了最快的方法。下面是测试用例的代码:

# Import required modules
import pandas as pd
import timeit

# Original
def method_1():
    df_people = pd.DataFrame([['Tom'], ['Jerry']], columns=['Name'])
    df_colors = pd.DataFrame([['Red'], ['Green'], ['Blue']], columns=None)
    # Store data for new columns in a dictionary
    new_columns = {}
    for index_people, row_people in df_people.iterrows():
        for index_colors, row_colors in df_colors.iterrows():
            key = 'Color_' + str(index_colors)
            if (key in new_columns):
                new_columns[key].append(row_colors[0])
            else:
                new_columns[key] = [row_colors[0]]
    # Add dictionary data as new columns
    for key, value in new_columns.items():
        df_people[key] = value

# YOBEN_S - https://stackoverflow.com/a/60805881/452587
def method_2():
    df_people = pd.DataFrame([['Tom'], ['Jerry']], columns=['Name'])
    df_colors = pd.DataFrame([['Red'], ['Green'], ['Blue']], columns=None)
    _s = pd.concat([df_colors]*len(df_people), axis=1)
    _s.columns = df_people.index
    df_people = df_people.join(_s.T.add_prefix('Color_'))

# Dani Mesejo - https://stackoverflow.com/a/60805898/452587
def method_3():
    df_people = pd.DataFrame([['Tom'], ['Jerry']], columns=['Name'])
    df_colors = pd.DataFrame([['Red'], ['Green'], ['Blue']], columns=None)
    # Create mock key
    _m1 = df_people.assign(key=1)
    # Set new column names, transpose, and create mock key
    _m2 = df_colors.set_index('Color_' + df_colors.index.astype(str)).T.assign(key=1)
    df_people =  _m1.merge(_m2, on='key').drop('key', axis=1)

# Erfan - https://stackoverflow.com/a/60806018/452587
def method_4():
    df_people = pd.DataFrame([['Tom'], ['Jerry']], columns=['Name'])
    df_colors = pd.DataFrame([['Red'], ['Green'], ['Blue']], columns=None)
    df_colors = df_colors.T.reindex(df_people.index).ffill().add_prefix('Color_')
    df_people = df_people.join(df_colors)

print('Method 1:', timeit.timeit(method_1, number=10000))
print('Method 2:', timeit.timeit(method_2, number=10000))
print('Method 3:', timeit.timeit(method_3, number=10000))
print('Method 4:', timeit.timeit(method_4, number=10000))

输出:

Method 1: 36.029883089
Method 2: 27.042384837999997
Method 3: 68.22421793800001
Method 4: 32.94155895

在我努力简化场景的过程中，不幸的是我过于简化了它。现在改写这个问题已经太晚了，所以我想我会在以后发布一个相关的问题。实际场景也涉及数学，因此不是简单地在 df_colors 中附加列。至 df_people ，我还需要对每个添加的单元格的相应行中的列执行一些计算。

更新 2

我已经使示例数据帧更大(感谢 jezrael)并添加了两种新方法。

# Import required modules
import numpy as np
import pandas as pd
import timeit

# Original
def method_1():
    df_people = pd.DataFrame(['Tom', 'Jerry', 'Bob', 'John', 'Bill', 'Tim', 'Harry', 'Rick'] * 1000, columns=['Name'])
    df_colors = pd.DataFrame(['Red', 'Green', 'Blue'] * 10, columns=None)
    # Store data for new columns in a dictionary
    new_columns = {}
    for index_people, row_people in df_people.iterrows():
        for index_colors, row_colors in df_colors.iterrows():
            key = 'Color_' + str(index_colors)
            if (key in new_columns):
                new_columns[key].append(row_colors[0])
            else:
                new_columns[key] = [row_colors[0]]
    # Add dictionary data as new columns
    for key, value in new_columns.items():
        df_people[key] = value

# YOBEN_S - https://stackoverflow.com/a/60805881/452587
def method_2():
    df_people = pd.DataFrame(['Tom', 'Jerry', 'Bob', 'John', 'Bill', 'Tim', 'Harry', 'Rick'] * 1000, columns=['Name'])
    df_colors = pd.DataFrame(['Red', 'Green', 'Blue'] * 10, columns=None)
    _s = pd.concat([df_colors]*len(df_people), axis=1)
    _s.columns = df_people.index
    df_people = df_people.join(_s.T.add_prefix('Color_'))

# sammywemmy - https://stackoverflow.com/a/60805964/452587
def method_3():
    df_people = pd.DataFrame(['Tom', 'Jerry', 'Bob', 'John', 'Bill', 'Tim', 'Harry', 'Rick'] * 1000, columns=['Name'])
    df_colors = pd.DataFrame(['Red', 'Green', 'Blue'] * 10, columns=None)
    # Create a new column in df_people with aggregate of df_colors;
    df_people['Colors'] = df_colors[0].str.cat(sep=',')
    # Concatenate df_people['Name'] and df_people['Colors'];
    # split column, expand into a dataframe, and add prefix
    df_people = pd.concat([df_people.Name, df_people.Colors.str.split(',', expand=True).add_prefix('Color_')], axis=1)

# Dani Mesejo - https://stackoverflow.com/a/60805898/452587
def method_4():
    df_people = pd.DataFrame(['Tom', 'Jerry', 'Bob', 'John', 'Bill', 'Tim', 'Harry', 'Rick'] * 1000, columns=['Name'])
    df_colors = pd.DataFrame(['Red', 'Green', 'Blue'] * 10, columns=None)
    # Create mock key
    _m1 = df_people.assign(key=1)
    # Set new column names, transpose, and create mock key
    _m2 = df_colors.set_index('Color_' + df_colors.index.astype(str)).T.assign(key=1)
    df_people =  _m1.merge(_m2, on='key').drop('key', axis=1)

# Erfan - https://stackoverflow.com/a/60806018/452587
def method_5():
    df_people = pd.DataFrame(['Tom', 'Jerry', 'Bob', 'John', 'Bill', 'Tim', 'Harry', 'Rick'] * 1000, columns=['Name'])
    df_colors = pd.DataFrame(['Red', 'Green', 'Blue'] * 10, columns=None)
    df_colors = df_colors.T.reindex(df_people.index).ffill().add_prefix('Color_')
    df_people = df_people.join(df_colors)

# jezrael - https://stackoverflow.com/a/60826723/452587
def method_6():
    df_people = pd.DataFrame(['Tom', 'Jerry', 'Bob', 'John', 'Bill', 'Tim', 'Harry', 'Rick'] * 1000, columns=['Name'])
    df_colors = pd.DataFrame(['Red', 'Green', 'Blue'] * 10, columns=None)
    _a = np.broadcast_to(df_colors[0], (len(df_people), len(df_colors)))
    df_people = df_people.join(pd.DataFrame(_a, index=df_people.index).add_prefix('Color_'))

print('Method 1:', timeit.timeit(method_1, number=3))
print('Method 2:', timeit.timeit(method_2, number=3))
print('Method 3:', timeit.timeit(method_3, number=3))
print('Method 4:', timeit.timeit(method_4, number=3))
print('Method 5:', timeit.timeit(method_5, number=3))
print('Method 6:', timeit.timeit(method_6, number=3))

输出:

Method 1: 74.512771493
Method 2: 1.0007798979999905
Method 3: 0.40823360299999933
Method 4: 0.08115736700000298
Method 5: 0.11704620100000795
Method 6: 0.04700596800000767

更新 3

我已经发布了一个有关转置和计算的相关问题，它更准确地反射(reflect)了真实数据集:

Fastest way to transpose and calculate in Pandas?

Answer 1

您可以通过 numpy.broadcast_to 方法提高性能:

df_people = pd.DataFrame([['Tom'], ['Jerry']], columns=['Name'])
df_colors = pd.DataFrame([['Red'], ['Green'], ['Blue']], columns=None)

a = np.broadcast_to(df_colors[0], (len(df_people), len(df_colors)))
df = df_people.join(pd.DataFrame(a, index=df_people.index).add_prefix('Color_'))
print (df)
    Name Color_0 Color_1 Color_2
0    Tom     Red   Green    Blue
1  Jerry     Red   Green    Blue

import timeit

def method_2():
    df_people = pd.DataFrame([['Tom'], ['Jerry']], columns=['Name'])
    df_colors = pd.DataFrame([['Red'], ['Green'], ['Blue']], columns=None)
    _s = pd.concat([df_colors]*len(df_people), axis=1)
    _s.columns = df_people.index
    df_people = df_people.join(_s.T.add_prefix('Color_'))

def method_5():
    df_people = pd.DataFrame([['Tom'], ['Jerry']], columns=['Name'])
    df_colors = pd.DataFrame([['Red'], ['Green'], ['Blue']], columns=None)
    a = np.broadcast_to(df_colors[0], (len(df_people), len(df_colors)))
    df_people = df_people.join(pd.DataFrame(a, index=df_people.index).add_prefix('Color_'))

print('Method 2:', timeit.timeit(method_2, number=10000))
Method 2: 27.919169027998578

print('Method 5:', timeit.timeit(method_5, number=10000))
Method 5: 21.452649746001043

但我认为更好的是在大型 DataFrame 中进行测试，例如这里有 3k 行和 30 列，那么时间是不同的:

# Import required modules
import pandas as pd
import timeit

# Original
def method_1():
    df_people = pd.DataFrame(['Tom','Jerry','Bob'] * 1000, columns=['Name'])
    df_colors = pd.DataFrame(['Red','Green', 'Blue'] * 10, columns=None)
    # Store data for new columns in a dictionary
    new_columns = {}
    for index_people, row_people in df_people.iterrows():
        for index_colors, row_colors in df_colors.iterrows():
            key = 'Color_' + str(index_colors)
            if (key in new_columns):
                new_columns[key].append(row_colors[0])
            else:
                new_columns[key] = [row_colors[0]]
    # Add dictionary data as new columns
    for key, value in new_columns.items():
        df_people[key] = value

# YOBEN_S - https://stackoverflow.com/a/60805881/452587
def method_2():
    df_people = pd.DataFrame(['Tom','Jerry','Bob'] * 1000, columns=['Name'])
    df_colors = pd.DataFrame(['Red','Green', 'Blue'] * 10, columns=None)
    _s = pd.concat([df_colors]*len(df_people), axis=1)
    _s.columns = df_people.index
    df_people = df_people.join(_s.T.add_prefix('Color_'))

# Dani Mesejo - https://stackoverflow.com/a/60805898/452587
def method_3():
    df_people = pd.DataFrame(['Tom','Jerry','Bob'] * 1000, columns=['Name'])
    df_colors = pd.DataFrame(['Red','Green', 'Blue'] * 10, columns=None)
    # Create mock key
    _m1 = df_people.assign(key=1)
    # Set new column names, transpose, and create mock key
    _m2 = df_colors.set_index('Color_' + df_colors.index.astype(str)).T.assign(key=1)
    df_people =  _m1.merge(_m2, on='key').drop('key', axis=1)

# Erfan - https://stackoverflow.com/a/60806018/452587
def method_4():
    df_people = pd.DataFrame(['Tom','Jerry','Bob'] * 1000, columns=['Name'])
    df_colors = pd.DataFrame(['Red','Green', 'Blue'] * 10, columns=None)
    df_colors = df_colors.T.reindex(df_people.index).ffill().add_prefix('Color_')
    df_people = df_people.join(df_colors)

def method_5():
    df_people = pd.DataFrame(['Tom','Jerry','Bob'] * 1000, columns=['Name'])
    df_colors = pd.DataFrame(['Red','Green', 'Blue'] * 10, columns=None)
    a = np.broadcast_to(df_colors[0], (len(df_people), len(df_colors)))
    df_people = df_people.join(pd.DataFrame(a, index=df_people.index).add_prefix('Color_'))

print('Method 1:', timeit.timeit(method_1, number=3))
print('Method 2:', timeit.timeit(method_2, number=3))
print('Method 3:', timeit.timeit(method_3, number=3))
print('Method 4:', timeit.timeit(method_4, number=3))
print('Method 5:', timeit.timeit(method_5, number=3))

Method 1: 34.91457201199955
Method 2: 0.7901797180002177
Method 3: 0.05690281799979857
Method 4: 0.05774562500118918
Method 5: 0.026483284000278218