I am looking to reformat some data. It currently looks like this:

Site_ID Section_ID
1, "A100/2020, B100/1001, C130/2000"
2, "A100/2021, ZW00/2002, W300/0999"
3, "A100/2022, TS100/4000, RW100/0000"

Using this as an example, the below is the format i'm trying to achieve: enter image description here

So each element in the list gets it's own row, but the original site_ID is retained. A solution in Python would be ideal as this is the only language I am currently comfortable with.


You can use split with iterrows:

import pandas as pd
df = pd.DataFrame([{'Site_ID': 1, 'Section_ID': 'a,b,c'},
               {'Site_ID': 2, 'Section_ID': 'd,e,f'}])


  Site_ID   Section_ID  
0   1   a,b,c  
1   2   d,e,f  

pd.concat([pd.Series( row['Site_ID'], row['Section_ID'].split(',') ) for _, row in df.iterrows()])

a    1  
b    1  
c    1  
d    2  
e    2  
f    2  

iterrows goes through the series row by row and split will find the separate values in a string that is separated by a comma.

If you want the column names back, you can convert back to a dataframe:

import numpy as np  
df1 = pd.DataFrame(np.array(pd.concat([pd.Series( row['Site_ID'], row['Section_ID'].split(',') ) for _, row in df.iterrows()]).reset_index()), columns=['Site_ID','Section_ID'])  


 Site_ID Section_ID
0   a   1
1   b   1
2   c   1
3   d   2
4   e   2
5   f   2
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.