How can I multiply row under certain condition with Pandas?

Question

How can I multiply row under certain condition with Pandas? The condition is only a name ending with Pref. . Sort order do not mind.

import pandas as pd

if __name__ == '__main__':

df = pd.DataFrame({"area": ["Aomori Pref.", "Saitama", "GifuPref."],
                    "x": [30, 40, 55],
                    "y": ["l", "m", "n"]})

# I want to get:
#    area         x     y
# 0  Aomori       30    l
# 1  Aomori Pref. 30    l
# 2  Saitama      40    m
# 3  Gifu         55    n
# 4  GifuPref.    55    n

```

No space. Then I could't use this answer. https://stackoverflow.com/questions/17116814/pandas-how-do-i-split-text-in-a-column-into-multiple-rows — Maiko Ohkawa, Nov 01 '17 at 06:18

jezrael · Accepted Answer · 2017-11-01T06:55:21.397

3

First replace values ending by Pref. and add new new column b with mask for NaNs for not matched values:

df1 = df['area'].str.replace('\s*(Pref.$)','').to_frame('a')
df1['b'] = df['area'].mask(df1['a'] == df['area'])

print (df1)
         a             b
0   Aomori  Aomori Pref.
1  Saitama           NaN
2     Gifu     GifuPref.

Then create Series by stack, give name to Series for new column name and last remove second level of MultiIndex by reset_index:

s = df1.stack().rename('area').reset_index(level=1, drop=True)
print (s)
0          Aomori
0    Aomori Pref.
1         Saitama
2            Gifu
2       GifuPref.
Name: area, dtype: object

Remove orifinal column area and join s, last for unique index add reset_index:

df2 = df.drop('area', 1).join(s).reset_index(drop=True)[df.columns]
print (df2)
           area   x  y
0        Aomori  30  l
1  Aomori Pref.  30  l
2       Saitama  40  m
3          Gifu  55  n
4     GifuPref.  55  n

Regex \s*(Pref.$) means - \s* is at least zero times, then match string in () and $ means end of string.

edited Nov 01 '17 at 06:55

answered Nov 01 '17 at 06:39

jezrael

822,522
95
1,334
1,252

Can you please explain the regex you used inside the replace function? – enterML Nov 01 '17 at 06:49
Yes, give me a sec. – jezrael Nov 01 '17 at 06:50
Nice. Thanks a lot – enterML Nov 01 '17 at 06:54
Elegant codes. Those worked well.Thank you very much for your gentle explain. – Maiko Ohkawa Nov 01 '17 at 09:50

andrew_reece · Answer 2 · 2017-11-01T16:14:58.537

1

pattern = "\s?Pref\\.$"
m = df.area.str.contains(pattern, regex=True)
tmp = df.copy()
tmp.loc[m,"area"] = tmp.area.str.replace(pattern, "")
(pd.concat([df, tmp])
   .sort_values("area")
   .drop_duplicates()
   .reset_index(drop=True))            

           area   x  y
0        Aomori  30  l
1  Aomori Pref.  30  l
2          Gifu  55  n
3     GifuPref.  55  n
4       Saitama  40  m

edited Nov 01 '17 at 16:14

answered Nov 01 '17 at 06:40

andrew_reece

20,390
3
33
58

`m` is mask dataframe, I see, Thank you very much! – Maiko Ohkawa Nov 01 '17 at 09:52
I left out `m`! Sorry about that, yes it's a mask. See updated answer. – andrew_reece Nov 01 '17 at 16:14

How can I multiply row under certain condition with Pandas?

2 Answers2