Filter a dataframe to create another dataframe

Question

I have a data frame already which is something like

x     label     word

10      1        is
20      2        goal
15      2        left
13      0        am
9       1        are
7       0        I
6       1        hello
2       0        world

I'm trying to create another data frame which, once I have performed the operations to extract from this data frame, looks like this:

label    min    max    words
 0        2     13     I, world, am
 1        6     10     hello, are, is
 2        15    20     goal, left

The words in the words column can be in any order. They represent the words for that specific label.

I have tried using df.groupby but i don't seem to understand how it works. Can someone guide me as to what method should I be looking for?

jezrael · Accepted Answer · 2018-07-10 11:15:18Z

Use DataFrameGroupBy.agg by dictionary of columns names and aggregated functions. Then get MultiIndex in columns, so need flatten it - here by map and join, last if necessary rename columns:

df = df.groupby('label').agg({'x':['min','max'], 'word':', '.join})
df.columns = df.columns.map('_'.join)
d = {'x_min':'min','x_max':'max','word_join':'words'}
df = df.rename(columns=d).reset_index()
print (df)
   label  min  max           words
0      0    2   13    am, I, world
1      1    6   10  is, are, hello
2      2   15   20      goal, left

If want aggregate column word to lists:

df = df.groupby('label').agg({'x':['min','max'], 'word': lambda x: x.tolist()})
df.columns = df.columns.map('_'.join)
d = {'x_min':'min','x_max':'max','word_<lambda>':'words'}
df = df.rename(columns=d).reset_index()
print (df)
   label  min  max             words
0      0    2   13    [am, I, world]
1      1    6   10  [is, are, hello]
2      2   15   20      [goal, left]

Collectives™ on Stack Overflow

Filter a dataframe to create another dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related