Group by fields in pandas dataframe

Question

I have a dataframe with the following fields. For each Id, I have two records, that represent different latitude and longitudes. I'm trying to achieve a resultant dataframe that groups by current dataframe based on id and put its latitude and longitude into different fields.

I tried with the group by function but I do not get the intended results. Any help would be greatly appreciated.

Id  StartTime   StopTime    Latitude    Longitude
101 14:42:28    14:47:56    53.51       118.12
101 22:10:01    22:12:49    33.32       333.11

Result:

Id  StartLat    StartLong   DestLat DestLong
101 53.51       118.12      33.32       333.11

jezrael · Accepted Answer · 2016-12-07 14:50:29Z

1

You can use groupby with apply function for return flatten DataFrame to Series:

df = df.groupby('Id')['Latitude','Longitude'].apply(lambda x: pd.Series(x.values.ravel()))
df.columns = ['StartLat', 'StartLong', 'DestLat', 'DestLong']
df = df.reset_index()
print (df)
    Id  StartLat  StartLong  DestLat  DestLong
0  101     53.51     118.12    33.32    333.11

If problem:

TypeError: Series.name must be a hashable type

try change Series to DataFrame, but then need unstack with droplevel:

df = df.groupby('Id')['Latitude','Longitude']
       .apply(lambda x: pd.DataFrame(x.values.ravel()))
       .unstack()
df.columns = df.columns.droplevel(0)
df.columns = ['StartLat', 'StartLong', 'DestLat', 'DestLong']
df = df.reset_index()
print (df)
    Id  StartLat  StartLong  DestLat  DestLong
0  101     53.51     118.12    33.32    333.11

edited Dec 7, 2016 at 14:50

answered Dec 7, 2016 at 14:27

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

2 Comments

user3447653 Over a year ago

My dataframe has 400000 records. This group by works perfectly for 100 records. For more than 100 records itself, I get the error "TypeError: Series.name must be a hashable type". Not sure why I get this error with increased number of records in the dataframe? Are these restricted by any specific size?

jezrael Over a year ago

I have this problem also and solution is, give me a minute.

Collectives™ on Stack Overflow

Group by fields in pandas dataframe

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related