CODEWITHSUNDEEP
pythonpandasdataframe
mane
mane

Reputation: 2243

Add column in dataframe from list

I have a dataframe with some columns like this:

A   B   C  
0   
4
5
6
7
7
6
5

The possible range of values in A are only from 0 to 7.

Also, I have a list of 8 elements like this:

List=[2,5,6,8,12,16,26,32]  //There are only 8 elements in this list

If the element in column A is n, I need to insert the n th element from the List in a new column, say 'D'.

How can I do this in one go without looping over the whole dataframe?

The resulting dataframe would look like this:

A   B   C   D
0           2
4           12
5           16
6           26
7           32
7           32
6           26
5           16

Note: The dataframe is huge and iteration is the last option option. But I can also arrange the elements in 'List' in any other data structure like dict if necessary.

Upvotes: 183

Views: 592950

Answers (6)

Mayank Porwal
Mayank Porwal

Reputation: 34086

You can also use df.assign:

In [1559]: df
Out[1559]: 
   A   B   C
0  0 NaN NaN
1  4 NaN NaN
2  5 NaN NaN
3  6 NaN NaN
4  7 NaN NaN
5  7 NaN NaN
6  6 NaN NaN
7  5 NaN NaN

In [1560]: mylist = [2,5,6,8,12,16,26,32]

In [1567]: df = df.assign(D=mylist)

In [1568]: df
Out[1568]: 
   A   B   C   D
0  0 NaN NaN   2
1  4 NaN NaN   5
2  5 NaN NaN   6
3  6 NaN NaN   8
4  7 NaN NaN  12
5  7 NaN NaN  16
6  6 NaN NaN  26
7  5 NaN NaN  32

Upvotes: 7

Mehdi
Mehdi

Reputation: 1187

Old question; but I always try to use fastest code!

I had a huge list with 69 millions of uint64. np.array() was fastest for me.

df['hashes'] = hashes
Time spent: 17.034842014312744

df['hashes'] = pd.Series(hashes).values
Time spent: 17.141014337539673

df['key'] = np.array(hashes)
Time spent: 10.724546194076538

Upvotes: 10

sparrow
sparrow

Reputation: 11470

Just assign the list directly:

df['new_col'] = mylist

Alternative
Convert the list to a series or array and then assign:

se = pd.Series(mylist)
df['new_col'] = se.values

or

df['new_col'] = np.array(mylist)

Upvotes: 444

DSM
DSM

Reputation: 353569

IIUC, if you make your (unfortunately named) List into an ndarray, you can simply index into it naturally.

>>> import numpy as np
>>> m = np.arange(16)*10
>>> m[df.A]
array([  0,  40,  50,  60, 150, 150, 140, 130])
>>> df["D"] = m[df.A]
>>> df
    A   B   C    D
0   0 NaN NaN    0
1   4 NaN NaN   40
2   5 NaN NaN   50
3   6 NaN NaN   60
4  15 NaN NaN  150
5  15 NaN NaN  150
6  14 NaN NaN  140
7  13 NaN NaN  130

Here I built a new m, but if you use m = np.asarray(List), the same thing should work: the values in df.A will pick out the appropriate elements of m.

Note that if you're using an old version of numpy, you might have to use m[df.A.values] instead-- in the past, numpy didn't play well with others, and some refactoring in pandas caused some headaches. Things have improved now.

Upvotes: 61

Salvatore Cosentino
Salvatore Cosentino

Reputation: 7260

A solution improving on the great one from @sparrow.

Let df, be your dataset, and mylist the list with the values you want to add to the dataframe.

Let's suppose you want to call your new column simply, new_column

First make the list into a Series:

column_values = pd.Series(mylist)

Then use the insert function to add the column. This function has the advantage to let you choose in which position you want to place the column. In the following example we will position the new column in the first position from left (by setting loc=0)

df.insert(loc=0, column='new_column', value=column_values)

Upvotes: 20

Phil Cooper
Phil Cooper

Reputation: 5877

First let's create the dataframe you had, I'll ignore columns B and C as they are not relevant.

df = pd.DataFrame({'A': [0, 4, 5, 6, 7, 7, 6,5]})

And the mapping that you desire:

mapping = dict(enumerate([2,5,6,8,12,16,26,32]))

df['D'] = df['A'].map(mapping)

Done!

print df

Output:

   A   D
0  0   2
1  4  12
2  5  16
3  6  26
4  7  32
5  7  32
6  6  26
7  5  16

Upvotes: 8

Related Questions