CODEWITHSUNDEEP
pythonnumpypandas
Tengis
Tengis

Reputation: 2809

Pandas: Insert column according to index values

I have the following example data

In [1]: table[['id', 'age']]
Out[1]: 
   id  age
0   1   12
1   2   13
2   3   14
3   4   15
4   5   16
5   6   17
6   7   18
7   8  NaN
8   9   20

ad the following np.array

In [2]: data
Out[2]: 
array([[  1.,   3.,  21.],
       [  2.,   4.,  21.],
       [  3.,   5.,  22.],
       [  4.,   5.,  22.],
       [  5.,   4.,   2.]])

and would like to concatenate the data to the table according to the id.

For example the result should look like:

id  age
0   1   12  3     21
1   2   13  4     21 
2   3   14  5     22
3   4   15  5     22
4   5   16  4     2
5   6   17  
6   7   18  
7   8  NaN  
8   9   20

I can loop over zip(table,data) and work line by line, but I thought it could be done in a more concise way

Upvotes: 0

Views: 1409

Answers (1)

EdChum
EdChum

Reputation: 393903

I'm posting this as an answer according to your text desired result rather than your posted result df, I can edit it easily if that's what you really want.

Firstly construct a df from the np array and then merge this. We merge the left side on 'id' and the right side on column '0' and perform an outer merge, we have to drop the '0' column as it's superfluous to what we want:

In [261]:

data = np.array([[  1.,   3.,  21.],
       [  2.,   4.,  21.],
       [  3.,   5.,  22.],
       [  4.,   5.,  22.],
       [  5.,   4.,   2.]])
data
Out[261]:
array([[  1.,   3.,  21.],
       [  2.,   4.,  21.],
       [  3.,   5.,  22.],
       [  4.,   5.,  22.],
       [  5.,   4.,   2.]])
In [265]:

data_df = pd.DataFrame(data)
data_df
Out[265]:
   0  1   2
0  1  3  21
1  2  4  21
2  3  5  22
3  4  5  22
4  5  4   2
In [268]:

df.merge(data_df, left_on='id', right_on=0, how='outer').drop(0, axis=1)
Out[268]:
   id  age   1   2
0   1   12   3  21
1   2   13   4  21
2   3   14   5  22
3   4   15   5  22
4   5   16   4   2
5   6   17 NaN NaN
6   7   18 NaN NaN
7   8  NaN NaN NaN
8   9   20 NaN NaN

Upvotes: 2

Related Questions