Pandas Dataframe manipulation for nested json

Question

I am new to python and I need to manipulate a little the following df. What I basically need is to move from this:

id	name	start	end	age1	age2	age3	age4	age5	age6
1	x	01:00	01:02	3_10	11_20	21_30	n/a	n/a	n/a
2	y	01:05	01:07	n/a	11_20	21_30	31_40	41_50	n/a
3	z	01:10	01:12	n/a	n/a	n/a	31_40	41_50	n/a

To this:

id	name	start	end	age
1	x	01:00	01:02	3_10
1	x	01:00	01:02	11_20
1	x	01:00	01:02	21_30
2	y	01:05	01:07	11_20
2	y	01:05	01:07	21_30
2	y	01:05	01:07	31_40
2	y	01:05	01:07	41_50

and so on for every ID.

As you may notice I need to transform the initial df in order to write a nested .json but I've never worked with pandas. I suppose there is some sort of Join that can be useful since other methods I tried were epic fails, but I'm super stuck.

Is there someone that can give me some hints? Thanks in advance

EDIT Since I haven't specified it before: the df i showed is only a little extraction of the real one. In fact I have to work with much more columns and rows.

SOLUTION FOUND

Here to write the solution I found after writing this post. To achieve the goal I used the pandas function .melt(). It works great! Below I attach the code:

df = pd.melt(df, id_vars=['ID', 'name1', 'name2', 'target', 'sexF', 'sexM', 'start', 'end'],
              value_vars=['age1', 'age2', 'age3', 'age4', 'age5', 
                          'age6', 'age7'],
              value_name='age',
              var_name='columnX')

Hannon qaoud · Answer

if i understood the assignment, here's a solution:


df = pd.DataFrame({
    'id': [1, 2],
    'name': ['x', 'y'],
    'start': ['01:00', '01:05'],
    'end': ['01:02', '01:07'],
    'age1': ['3_10', 'n/a'],
    'age2': ['11_20', '11_20'],
    'age3': ['21_30', '21_30'],
    'age4': ['n/a', '31_40'],
    'age5': ['n/a', '41_50'],
    'age6': ['n/a', 'n/a']
})
# for each age create a new record with the same id and name and start and end and age
df2 = pd.DataFrame()
for i in range(1, 7):
    df2 = df2.append(df[['id', 'name', 'start', 'end', 'age' + str(i)]].rename(columns={'age' + str(i): 'age'}))
# drop the rows with n/a
df2 = df2[df2.age != 'n/a']

print(df2)

if its not what you want clarify more and i'll steer the solution for you.

Pandas Dataframe manipulation for nested json

1 Answers