CODEWITHSUNDEEP
pythonpandas
tumultous_rooster
tumultous_rooster

Reputation: 12550

Conditionally filling in values in a Pandas dataframe

I am trying to reshape the Pandas dataframe on the left into the one on the right.

It's easy enough to copy the type over
df['type'] = np.where(df.customer.str.match('DRIVER'), 'DRIVER', '')
but I need some way to capture the whole interval between IDs. This is the tough part. Any help or advice in general would be greatly appreciated.

enter image description here

df = { 'customer': ['ID 100', 'DRIVER', 'big wheel', 'unicycle', 'porshe', 'ID 200', 'EATER', 'bigmac', 'celery', 'gum', 'ID 300', 'MISSING', 'ID 400', 'READER', 'Gorden Korman', 'Hiroyuki Nishigaki'],
         'type': ['', '', '' , '', '', '', '', '', '', '', '', '', '', '', '', '']}
df = pd.DataFrame(df)

Upvotes: 0

Views: 1303

Answers (1)

Dickster
Dickster

Reputation: 3009

My solution is based on ID 100, ID 200, DRIVER, EATER, MISSING etc. being upper case.

Then use a map function followed by a fillna(method = 'ffill') and finally set back the "ID XXX"s to empty string.

df['type'] =  df['customer'].map(lambda x: x if x.isupper() else None)
df['type'] = df['type'].fillna(method ="ffill")
df['type'] = df['type'].map(lambda x: '' if x[0:3] =='ID ' else x)

print df.head(len(df))



              customer     type
0               ID 100         
1               DRIVER   DRIVER
2            big wheel   DRIVER
3             unicycle   DRIVER
4               porshe   DRIVER
5               ID 200         
6                EATER    EATER
7               bigmac    EATER
8               celery    EATER
9                  gum    EATER
10              ID 300         
11             MISSING  MISSING
12              ID 400         
13              READER   READER
14       Gorden Korman   READER
15  Hiroyuki Nishigaki   READER

Upvotes: 1

Related Questions