-2
$\begingroup$

I've tried a lot of different methods, but I can't seem to find the right way to do this. I want to create a new column based on the time and id of the df. However, ids appear multiple times. Here's my dataframe:

df = pd.DataFrame({'time': [1,2,3, 1, 2 ,3], 'id': ['A', 'A', 'A', 'B', 'B', 'B'], 'num': [10,11,12, 20, 21, 22]} ) and its output:

id num time A 10 1 A 11 2 A 12 3 B 20 1 B 21 2 B 22 3

What I want is that for the new columns value to be the num value for time==1 for each unique id. Here's what I would like the output to be:

id num time y A 10 1 10 A 11 2 10 A 12 3 10 B 20 1 20 B 21 2 20 B 22 3 20

One attempt I've made is to make a reference table made like this:

df['y'] = np.where(df['time']==1, df['num'], None)

ref = df[['id','y']]

ref = ref.dropna()

But I still don't know where to go from here. Thank you!

$\endgroup$
0
$\begingroup$

One can create a new dataframe having only first entries of new ID, copying num to new column y and merging this with original dataframe:

newdf = df.drop_duplicates('id')
newdf['y'] = newdf['num']
newdf = df.merge(newdf, how='outer')

However, it will put NaN for non-first id rows:

print(newdf)
  id  num  time     y
0  A   10     1  10.0
1  A   11     2   NaN
2  A   12     3   NaN
3  B   20     1  20.0
4  B   21     2   NaN
5  B   22     3   NaN

One can change these NaN to previous values by following simple loop:

tempval = 0   # a variable to store value temporarily
newy=[]
for x in newdf['y']: 
    if not pd.isnull(x): tempval = x
    newy.append(tempval)
newdf['y'] = newy

The desired dataframe is obtained:

print(newdf)
  id  num  time     y
0  A   10     1  10.0
1  A   11     2  10.0
2  A   12     3  10.0
3  B   20     1  20.0
4  B   21     2  20.0
5  B   22     3  20.0

Actually, this question belongs to https://stackoverflow.com/

$\endgroup$

Not the answer you're looking for? Browse other questions tagged or ask your own question.