1

I have a dataframe which can be created using the code given below

df = pd.DataFrame({'Person_id':[1,2,3,4],
'Values':['father:1.Yes 2.No 3.Do not Know','Mother:1.Yes 777.No 999.Do not 
Know','sons:1.Yes 2.No 321.Do not Know','daughter:1.Yes 567.No 3.Do not Know'],
'Ethnicity':['dffather','dfmother','dfson','dfdaughter']})

The above code produces a dataframe like shown below

enter image description here

I would like to split the content of each row in dataframe and put them as a separate row

How can I get the output to be like this?

enter image description here

The Great
  • 7,215
  • 7
  • 40
  • 128

1 Answers1

2

Use Series.str.extractall with regex for get integer values with point with text to Series, remove second level by reset_index and DataFrame.join to original, last if necessary set duplicated values to empty strings by Series.duplicated:

cols = df.columns
s = (df.pop('Values')
       .str.extractall('(\d+\.\D+)')[0]
       .str.strip()
       .reset_index(level=1, drop=True)
       .rename('Values'))

df = df.join(s).reindex(cols, axis=1).reset_index(drop=True)
df.loc[df['Person_id'].duplicated(), 'Ethnicity'] = ''
print (df)
    Person_id           Values   Ethnicity
0           1            1.Yes    dffather
1           1             2.No            
2           1    3.Do not Know            
3           2            1.Yes    dfmother
4           2           777.No            
5           2  999.Do not Know            
6           3            1.Yes       dfson
7           3             2.No            
8           3  321.Do not Know            
9           4            1.Yes  dfdaughter
10          4           567.No            
11          4    3.Do not Know
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • Can you help me with this @jezrael - https://stackoverflow.com/questions/56556191/3-data-frames-and-3-rules-in-operation-to-insert-data-into-another-dataframe-n/56556694?noredirect=1 – The Great Jun 12 '19 at 10:58
  • Encountering few issues.Hence thought of seeking your help. I have provided the screenshots of error with real data.. – The Great Jun 12 '19 at 11:06
  • @AVLES - ya, but problem is I cannot working with another solution... because not author... – jezrael Jun 12 '19 at 11:07
  • Okay. I am trying to figure out whats the issue. If you can provide your solution, would be helpful – The Great Jun 12 '19 at 11:09
  • 1
    @AVLES - ok, so is possible remove accepting from answer, because not working? Because accepting means no problem, another answer solutions are not necessary... – jezrael Jun 12 '19 at 11:11
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/194822/discussion-between-avles-and-jezrael). – The Great Jun 12 '19 at 11:13