Is there any fast way to convert all of the values in dataframe - python

Question

There is a data frame like below:

A	B
1	12
84	15
51	42
2	10

Each value shows the position of a string in a list. For example, list A=[Cat, Dog, Cow, ...] Therefore, the first value in column A should be Dog. How can I replace this values in this data frame fast. This data frame has more than 1 million rows. I wrote the code below, but it seems that it takes ages to run!!

for i in range (0, len(df)):
   a = df.iloc[i,0]
   df.iloc[i,0] = A[a]
   b = df.iloc[i,1]
   df.iloc[i,1] = B[b]

Sounds like a job for [`map`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.map.html), [`apply`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.apply.html#pandas.DataFrame.apply), [`applymap`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.applymap.html) or similar — Jiří Baum, Jul 20 '21 at 12:32

score 1 · Accepted Answer · answered Jul 20 '21 at 12:36

1

So I don't believe your code is particularly bad from an efficiency point of view. It's likely to take a while given that you have such a large dataframe.

I would suggest though that the below code is more elegant when applying a function to a column in a dataframe:

df['A'] = df['A'].map(lambda x: A[x])
df['B'] = df['B'].map(lambda x: B[x])

answered Jul 20 '21 at 12:36

Daniel Wyatt

960
1
10
29

another option is to replace `map` by `apply` . I don't know which is more efficient, so it might be interesting to try both approaches – Avandale Jul 20 '21 at 12:53

IoaTzimas · Answer 2 · 2021-07-20T12:56:35.147

1

You can use numpy which is much faster than Pandas. Try the following:

valsA=['Cat', 'Dog', 'Cow'] * 100
valsA=np.array(valsA)
valsB=['Dog', 'Cat', 'Cow'] * 100
valsB=np.array(valsB)

df['A']=valsA.take(df['A'])
df['B']=valsB.take(df['B'])

>>> print(df)

     A    B
0  Dog  Dog
1  Cat  Dog
2  Cat  Dog
3  Cow  Cat

edited Jul 20 '21 at 12:56

answered Jul 20 '21 at 12:36

IoaTzimas

10,538
2
13
30

1

Nice, creative solution. First time seeing such usecase of numpy +1 – eroot163pi Jul 20 '21 at 12:38

eroot163pi · Answer 3 · 2021-07-20T12:45:41.640

You can Use apply.

Generally when using apply output is of type pd.Series, but when result_type='expand', the result of apply is unwrapped over columns and returns a pd.DataFrame

Below example is an illustration

>>> A = ['Cat', 'Dog', 'Cow']
>>> B = ['Catb', 'Dogb', 'Cowb']
>>> import pandas as pd
>>> df = pd.DataFrame([[1, 2]] * 3, columns=['A', 'B'])
>>> df.apply(lambda x: [A[x['A']], B[x['B']]], axis=1, result_type='expand')
     0    1
0  Dog  Cowb
1  Dog  Cowb
2  Dog  Cowb

Also one more method using map but without using lambda List comprehension vs map

>>> df['A'] = df['A'].map(A.__getitem__)
>>> df['B'] = df['B'].map(B.__getitem__)

Is there any fast way to convert all of the values in dataframe - python

3 Answers3