# Reformatting data- Giving each value in a list it's own row while retaining the list's ID

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

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

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'}])

df

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'])

df1

Site_ID Section_ID
0   a   1
1   b   1
2   c   1
3   d   2
4   e   2
5   f   2