Creating a new pandas dataframe by spliting rows

Question

Let say I have data about cities with the name of the mayor and the baker (if there is one) :

        city name_mayor age_mayor name_baker age_baker
0  Cherbourg     Robert        10       Jack        40
1     Calais     Michel        20     Russel        50
2     Nevers        Guy        30       None      None

I then want to create a new dataframe to work on the individuals, I thus would like a dataframe like so :

        city    name  age
0  Cherbourg  Robert   10
1     Calais  Michel   20
2     Nevers     Guy   30
3  Cherbourg    Jack   40
4     Calais  Russel   50

Then it is easier to compute things such as mean age.

Can anyone tell me :

How can I do so ?
Should I work like that with Pandas ?

Basically I can do it with an iteration over rows, but I read that it is often better to use other ways with pandas (as stated here : How to iterate over rows in a DataFrame in Pandas).

I'm not new to pandas, but still stuck in an "numpy-array" way of thinking.

If needed here are how I made my two example :

data_1 = { "city" : ["Cherbourg", "Calais", "Nevers"], "name_mayor" : ["Robert", "Michel", "Guy"], "age_mayor" : [10,20,30], "name_baker" : ["Jack", "Russel"], "age_baker" : [40,50]}
df_1 = pd.DataFrame.from_dict(data_1, orient='index').transpose()

data_2 = {0:["Cherbourg", "Robert", 10], 1:["Calais", "Michel", 20], 2:["Nevers", "Guy", 30], 3:["Cherbourg", "Jack", 40], 4:["Calais", "Russel", 50]  }
df_2 = pd.DataFrame.from_dict(data_2, orient='index', columns=["city", "name", "age"])

Thanks ! R

score 2 · Answer 1 · answered Jun 29 '20 at 13:42

2

You can try with pd.wide_to_long

s=pd.wide_to_long(df,['name','age'],i='city',j='drop',sep='_', suffix='\\w+').reset_index()
        city   drop    name   age
0  Cherbourg  mayor  Robert    10
1     Calais  mayor  Michel    20
2     Nevers  mayor     Guy    30
3  Cherbourg  baker    Jack    40
4     Calais  baker  Russel    50
5     Nevers  baker    None  None

s=s[s['name'].ne('None')]

answered Jun 29 '20 at 13:42

BENY

317,841
20
164
234

Very elegant and interesting solution, for the specific problem I had I could not rely on name, but I learned a new function. Thank you for your answer ! – rponthieu dev Jun 29 '20 at 18:32

score 1 · Accepted Answer · answered Jun 29 '20 at 14:12

Here's another way using concat:

df.set_index('city', inplace=True)
df = pd.concat([df[['name_mayor', 'age_mayor']], df[['name_baker', 'age_baker']]]).rename(columns={'name_baker': 'name', 'name_mayor': 'name', 'age_mayor': 'age', 'age_baker': 'age'})
df = df.groupby(level=0, axis=1).first().reset_index()

        city   age    name
0  Cherbourg    10  Robert
1     Calais    20  Michel
2     Nevers    30     Guy
3  Cherbourg    40    Jack
4     Calais    50  Russel
5     Nevers  None    None

I ended up using the concat fonction for my specific problem (where there was more individuals than in the exemple), thanks. — rponthieu dev, Jun 29 '20 at 18:28

score 1 · Answer 3 · answered Jun 29 '20 at 14:16

Most of the time when using panda it is better to use its build it methods to modify the array:

import pandas as pd
my_dict = {'city': ['Cherbourg', 'Calais', 'Nevers'], 'name_mayor': ['Robert', 'Michel', 'Guy'],
           'age_mayor': [10, 20, 30], 'name_baker': ['Jack', 'Russel', None], 'age_baker': [40, 50, None]}
my_data_frame = pd.DataFrame(my_dict)
new_data_frame = pd.DataFrame({'city': my_data_frame['city'].append(my_data_frame['city']), 'name': my_data_frame['name_mayor'].append(
    my_data_frame['name_baker']), 'age': my_data_frame['age_mayor'].append(my_data_frame['age_baker'])})
new_data_frame.dropna(inplace=True)
print(new_data_frame)

Output:

        city    name   age
0  Cherbourg  Robert  10.0
1     Calais  Michel  20.0
2     Nevers     Guy  30.0
0  Cherbourg    Jack  40.0
1     Calais  Russel  50.0

Thank you for clarifying the way to produce the dataframe by incuding none ! — rponthieu dev, Jun 29 '20 at 18:30

Creating a new pandas dataframe by spliting rows

3 Answers3