Pandas extract data from column

Question

I have a dataframe like this

import pandas as pd
d={'x':[8,5,6,7],
   'cord':['(3,0)','(2,0)','(6,0)','(1,0)']}
df=pd.DataFrame.from_dict(d)

I would like to create df['y'] which will have the first 'cord' value and shift to get index value.

    cord  x  y
0  (3,0)  8  1     #Index 3, First value in (1,0)
1  (2,0)  5  6     #Index 2, First value in (6,0)
2  (6,0)  6  NaN   #Index 6, does not exist, NaN
3  (1,0)  7  2     #Index 1, First value (1,0)

dermen · Accepted Answer · 2015-07-17 02:11:19Z

2

Make a separate column, which is the first element of cord

df['cord1'] = df.cord.map( lambda x: x.split(',')[0].split('(')[-1]).map(int)
df
#    cord  x  cord1
#0  (3,0)  8      3
#1  (2,0)  5      2
#2  (6,0)  6      6
#3  (1,0)  7      1

This might look confusing, but it simply splits the string '(a,b)' twice, first on ',' , and then on '('. Finally, it casts the remaining string ,'a' , to an integer.

Now use the cord1 column to make the y column

df['y'] =  df.cord1[ df.cord1.values].values

being careful to pass the values. Now drop the cord1 column

df.drop( labels='cord1', axis=1, inplace=True)

#df
#    cord  x   y
#0  (3,0)  8   1
#1  (2,0)  5   6
#2  (6,0)  6 NaN
#3  (1,0)  7   2

edited Jul 17, 2015 at 2:11

answered Jul 17, 2015 at 2:04

dermen

5,4024 gold badges25 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dermen Over a year ago

I had to make a slight adjustment, where y column is created, df['y'] = ... is the proper syntax I believe. If instead one does, df.y = ... it creates a hidden attribute df.y, which wont show up when you print df, at least in pandas 0.16.1.

NinjaGaiden Over a year ago

what if df.cord1.values sometimes produces an IndexError (the Index doesn't exist). I am using an iterrows() and catching the IndexError exception which works but I don't think its the most efficient. I was wondering if there is a way to avoid that

dermen Over a year ago

Hmm, you are looping over df.cord1.values and indexing the dataframe on each iteration ? Try indexing with an array, even if you are indexing a single element. For example df.cord1[[6]] returns np.nan however, df.cord1[6] will throw an error.

Collectives™ on Stack Overflow

Pandas extract data from column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related