Pandas group by using for loop, editing, then combining

Question

I have the following Table

      time                   userid   market   device    query      querytype   browser
0     2020-07-01  04:47:21   A        EN-US    PC        WEATHER    WEATHER     EDGE
1     2020-07-01  07:23:52   C        ZH-CN    MOBILE    RECIPIES   FOOD        SAFARI
2     2020-07-01  15:32:57   D        EN-GB    TABLET    DOGS       ANIMALS     CHROME
3     2020-07-01  17:16:21   A        EN-CA    PC        SEATTLE    CITY        EDGE
4     2020-07-01  21:07:21   D        EN-GB    TABLET    DOG FOOD   ANIMAL      CHROME
5     2020-07-01  22:26:21   E        DE-DE    MOBILE    IPHONE     PRODUCTS    CHROME

And I am trying to get the last query for userid.

  last_query  
0        NaN  
1        NaN 
2        NaN 
3    WEATHER  
4       DOGS
5        NaN

I am using the following code to achieve the problem but I don't get any values.

s = pd.Series()
for name, value in df.groupby('userid'):
    userid = name
    last_query = value['query'].shift()
    s.append(last_query)

Is there any way to combine the values together or to achieve this at all?

Please also add that what values do you expect to see at the end. — AKS, Jul 08 '21 at 02:54
Try `df.groupby('userid').max().reset_index()` , Also share the expected result else everything is assumption. — Abhi, Jul 08 '21 at 03:39
So you want to get the value of the `query` column for the previous timestamp separately each user? — Shaido, Jul 08 '21 at 06:11
Does this answer your question? [Get the row(s) which have the max value in groups using groupby](https://stackoverflow.com/questions/15705630/get-the-rows-which-have-the-max-value-in-groups-using-groupby) and also you asked this again [pandas-select-another-column-using-the-result-from-groupby](https://stackoverflow.com/questions/68296380/pandas-select-another-column-using-the-result-from-groupby) — Anurag Dabas, Jul 08 '21 at 06:11

score 0 · Accepted Answer · answered Jul 08 '21 at 09:28

You can do the following:

df_last_queries = df.groupby('userid').shift(1)['query']

Result:

0        NaN
1        NaN
2        NaN
3    WEATHER
4       DOGS
5        NaN

Important: The time column needs to be in ascending order (looks like this is the case).

Pandas group by using for loop, editing, then combining

1 Answers1