在dataframe中组合两列文本

w46czmvw  于 2021-08-25  发布在  Java
关注(0)|答案(19)|浏览(408)

我有一个使用pandas的python 20 x 4000 Dataframe 。其中两列被命名为 Yearquarter . 我想创建一个名为 period 那就 Year = 2000quarter= q2 进入 2000q2 .
有人能帮忙吗?

ss2ws0br

ss2ws0br1#

可以使用dataframe的分配方法:

df= (pd.DataFrame({'Year': ['2014', '2015'], 'quarter': ['q1', 'q2']}).
  assign(period=lambda x: x.Year+x.quarter ))
ikfrs5lh

ikfrs5lh2#

def madd(x):
    """Performs element-wise string concatenation with multiple input arrays.

    Args:
        x: iterable of np.array.

    Returns: np.array.
    """
    for i, arr in enumerate(x):
        if type(arr.item(0)) is not str:
            x[i] = x[i].astype(str)
    return reduce(np.core.defchararray.add, x)

例如:

data = list(zip([2000]*4, ['q1', 'q2', 'q3', 'q4']))
df = pd.DataFrame(data=data, columns=['Year', 'quarter'])
df['period'] = madd([df[col].values for col in ['Year', 'quarter']])

df

    Year    quarter period
0   2000    q1  2000q1
1   2000    q2  2000q2
2   2000    q3  2000q3
3   2000    q4  2000q4
hmmo2u0o

hmmo2u0o3#

使用 .combine_first .

df['Period'] = df['Year'].combine_first(df['Quarter'])
zaq34kh6

zaq34kh64#

正如前面提到的,您必须将每个列转换为字符串,然后使用加号运算符组合两个字符串列。通过使用numpy,您可以获得很大的性能改进。

%timeit df['Year'].values.astype(str) + df.quarter
71.1 ms ± 3.76 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

%timeit df['Year'].astype(str) + df['quarter']
565 ms ± 22.3 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
ruoxqz4g

ruoxqz4g5#

我的看法。。。。

listofcols = ['col1','col2','col3']
df['combined_cols'] = ''

for column in listofcols:
    df['combined_cols'] = df['combined_cols'] + ' ' + df[column]
'''
wydwbb8l

wydwbb8l6#

下面是我对上述解决方案的总结,使用列值之间的分隔符,将两个具有int和str值的列连接/组合到一个新列中。为此,有三种解决方案。


# be cautious about the separator, some symbols may cause "SyntaxError: EOL while scanning string literal".

# e.g. ";;" as separator would raise the SyntaxError

separator = "&&" 

# pd.Series.str.cat() method does not work to concatenate / combine two columns with int value and str value. This would raise "AttributeError: Can only use .cat accessor with a 'category' dtype"

df["period"] = df["Year"].map(str) + separator + df["quarter"]
df["period"] = df[['Year','quarter']].apply(lambda x : '{} && {}'.format(x[0],x[1]), axis=1)
df["period"] = df.apply(lambda x: f'{x["Year"]} && {x["quarter"]}', axis=1)
jm2pwxwz

jm2pwxwz7#

更有效的是

def concat_df_str1(df):
    """ run time: 1.3416s """
    return pd.Series([''.join(row.astype(str)) for row in df.values], index=df.index)

下面是一个时间测试:

import numpy as np
import pandas as pd

from time import time

def concat_df_str1(df):
    """ run time: 1.3416s """
    return pd.Series([''.join(row.astype(str)) for row in df.values], index=df.index)

def concat_df_str2(df):
    """ run time: 5.2758s """
    return df.astype(str).sum(axis=1)

def concat_df_str3(df):
    """ run time: 5.0076s """
    df = df.astype(str)
    return df[0] + df[1] + df[2] + df[3] + df[4] + \
           df[5] + df[6] + df[7] + df[8] + df[9]

def concat_df_str4(df):
    """ run time: 7.8624s """
    return df.astype(str).apply(lambda x: ''.join(x), axis=1)

def main():
    df = pd.DataFrame(np.zeros(1000000).reshape(100000, 10))
    df = df.astype(int)

    time1 = time()
    df_en = concat_df_str4(df)
    print('run time: %.4fs' % (time() - time1))
    print(df_en.head(10))

if __name__ == '__main__':
    main()

最后,什么时候 sum 使用(concat_df_str2),结果不仅仅是concat,它将转换为整数。

yizd12fk

yizd12fk8#

此解决方案使用一个中间步骤,将 Dataframe 的两列压缩为包含值列表的一列。这不仅适用于字符串,而且适用于所有类型的列数据类型

import pandas as pd
df = pd.DataFrame({'Year': ['2014', '2015'], 'quarter': ['q1', 'q2']})
df['list']=df[['Year','quarter']].values.tolist()
df['period']=df['list'].apply(''.join)
print(df)

结果:

Year quarter        list  period
0  2014      q1  [2014, q1]  2014q1
1  2015      q2  [2015, q2]  2015q2
0mkxixxg

0mkxixxg9#

使用 zip 可能会更快:

df["period"] = [''.join(i) for i in zip(df["Year"].map(str),df["quarter"])]

图表:

import pandas as pd
import numpy as np
import timeit
import matplotlib.pyplot as plt
from collections import defaultdict

df = pd.DataFrame({'Year': ['2014', '2015'], 'quarter': ['q1', 'q2']})

myfuncs = {
"df['Year'].astype(str) + df['quarter']":
    lambda: df['Year'].astype(str) + df['quarter'],
"df['Year'].map(str) + df['quarter']":
    lambda: df['Year'].map(str) + df['quarter'],
"df.Year.str.cat(df.quarter)":
    lambda: df.Year.str.cat(df.quarter),
"df.loc[:, ['Year','quarter']].astype(str).sum(axis=1)":
    lambda: df.loc[:, ['Year','quarter']].astype(str).sum(axis=1),
"df[['Year','quarter']].astype(str).sum(axis=1)":
    lambda: df[['Year','quarter']].astype(str).sum(axis=1),
    "df[['Year','quarter']].apply(lambda x : '{}{}'.format(x[0],x[1]), axis=1)":
    lambda: df[['Year','quarter']].apply(lambda x : '{}{}'.format(x[0],x[1]), axis=1),
    "[''.join(i) for i in zip(dataframe['Year'].map(str),dataframe['quarter'])]":
    lambda: [''.join(i) for i in zip(df["Year"].map(str),df["quarter"])]
}

d = defaultdict(dict)
step = 10
cont = True
while cont:
    lendf = len(df); print(lendf)
    for k,v in myfuncs.items():
        iters = 1
        t = 0
        while t < 0.2:
            ts = timeit.repeat(v, number=iters, repeat=3)
            t = min(ts)
            iters *= 10
        d[k][lendf] = t/iters
        if t > 2: cont = False
    df = pd.concat([df]*step)

pd.DataFrame(d).plot().legend(loc='upper center', bbox_to_anchor=(0.5, -0.15))
plt.yscale('log'); plt.xscale('log'); plt.ylabel('seconds'); plt.xlabel('df rows')
plt.show()
d6kp6zgx

d6kp6zgx10#

您可以使用lambda:

combine_lambda = lambda x: '{}{}'.format(x.Year, x.quarter)

然后将其用于创建新列:

df['period'] = df.apply(combine_lambda, axis = 1)
a5g8bdjr

a5g8bdjr11#

如果两列都是字符串,则可以直接连接它们:

df["period"] = df["Year"] + df["quarter"]

如果其中一列(或两列)不是字符串类型,则应首先转换该列,

df["period"] = df["Year"].astype(str) + df["quarter"]

做这件事时要当心南斯!

如果需要连接多个字符串列,可以使用 agg :

df['period'] = df[['Year', 'quarter', ...]].agg('-'.join, axis=1)

其中“-”是分隔符。

yi0zb3m4

yi0zb3m412#

以下是一个我认为非常通用的实现:

In [1]: import pandas as pd 

In [2]: df = pd.DataFrame([[0, 'the', 'quick', 'brown'],
   ...:                    [1, 'fox', 'jumps', 'over'], 
   ...:                    [2, 'the', 'lazy', 'dog']],
   ...:                   columns=['c0', 'c1', 'c2', 'c3'])

In [3]: def str_join(df, sep, *cols):
   ...:     from functools import reduce
   ...:     return reduce(lambda x, y: x.astype(str).str.cat(y.astype(str), sep=sep), 
   ...:                   [df[col] for col in cols])
   ...: 

In [4]: df['cat'] = str_join(df, '-', 'c0', 'c1', 'c2', 'c3')

In [5]: df
Out[5]: 
   c0   c1     c2     c3                cat
0   0  the  quick  brown  0-the-quick-brown
1   1  fox  jumps   over   1-fox-jumps-over
2   2  the   lazy    dog     2-the-lazy-dog
weylhg0b

weylhg0b13#

让我们假设你的 dataframedf 带柱 YearQuarter .

import pandas as pd
df = pd.DataFrame({'Quarter':'q1 q2 q3 q4'.split(), 'Year':'2000'})

假设我们想要看到 Dataframe ;

df
>>>  Quarter    Year
   0    q1      2000
   1    q2      2000
   2    q3      2000
   3    q4      2000

最后,连接 YearQuarter 具体如下。

df['Period'] = df['Year'] + ' ' + df['Quarter']

你现在可以
print df 查看生成的 Dataframe 。

df
>>>  Quarter    Year    Period
    0   q1      2000    2000 q1
    1   q2      2000    2000 q2
    2   q3      2000    2000 q3
    3   q4      2000    2000 q4

如果你不想要年和季度之间的空间,只需通过这样做来移除它;

df['Period'] = df['Year'] + df['Quarter']
qacovj5a

qacovj5a14#

尽管@silvado的答案是好的,如果你改变 df.map(str)df.astype(str) 它将更快:

import pandas as pd
df = pd.DataFrame({'Year': ['2014', '2015'], 'quarter': ['q1', 'q2']})

In [131]: %timeit df["Year"].map(str)
10000 loops, best of 3: 132 us per loop

In [132]: %timeit df["Year"].astype(str)
10000 loops, best of 3: 82.2 us per loop
2hh7jdfx

2hh7jdfx15#

概括到多个列,为什么不:

columns = ['whatever', 'columns', 'you', 'choose']
df['period'] = df[columns].astype(str).sum(axis=1)

相关问题