pandas Dataframe, MOVE Specific Value from One Cell to Another

Question

I want to be able to copy the values under the allergy column there are three languages. I want use pandas Dataframes to move specific cell values data from the row English to french above it and dutch underneath it. And After it's been copied, I want to delete the row English.

Note: Index is the languages below. They go in the order of French, English, Dutch, French, English, Dutch and so on. the Values of each are the typical responses are given under the column allergy

Index       allergy 

french      ''

english     'MAY CONTAIN: prawn' 

dutch       ''

french      ''

english     'MAY CONTAIN: peanuts'

dutch       ''

french      ''

english     'MAY CONTAIN: milk'

dutch       ''

So again to summarise, copy specific cell values from English to Dutch and French Rows.

edit: I cant any more comments: Yes this is the pattern for this dataset. this ouput is displayed in excel file.

Desired output:

Index        allergy 

french      'MAY CONTAIN: prawn'

english     'MAY CONTAIN: prawn'  # remove

dutch       'MAY CONTAIN: prawn'

french      'MAY CONTAIN: peanuts' 

english     'MAY CONTAIN: peanuts'  # remove

dutch       'MAY CONTAIN: peanuts'

french      'MAY CONTAIN: milk'

english     'MAY CONTAIN: milk'  # remove

dutch       'MAY CONTAIN: milk'

Afterwards, it Should look like this

Final output:

Index        allergy 

french      'MAY CONTAIN: prawn'

dutch       'MAY CONTAIN: prawn'

french      'MAY CONTAIN: peanuts' 

dutch       'MAY CONTAIN: peanuts'

french      'MAY CONTAIN: milk'

dutch       'MAY CONTAIN: milk'

OK, thank you. But try to imagine you want solve this question with data what can see. Is it possible? I think not :( Because I think your real data are different, not always all empty values. Or need only copy column `english` 2 times? I think creating [mcve](https://stackoverflow.com/help/mcve) should help. — jezrael, Nov 09 '17 at 12:48
Also input is dataframe with lists? What is `print (df.head())` ? — jezrael, Nov 09 '17 at 12:51
No, now it is OK. Only one thing, last need remove all `english` rows, right? — jezrael, Nov 09 '17 at 13:05
There is alwasys this format? empty french, filled englis, empty dutch? — jezrael, Nov 09 '17 at 13:12

score 2 · Accepted Answer · answered Nov 09 '17 at 13:22

2

First filter only english rows and create default index:

df1 = df.loc['english'].reset_index(drop=True)
print (df1)
                  allergy
0    'MAY CONTAIN: prawn'
1  'MAY CONTAIN: peanuts'
2     'MAY CONTAIN: milk'

Then concat twice, sorting and last remove second level:

df = (pd.concat([df1, df1], keys=('french','dutch'))
        .sort_index(level=1)
        .reset_index(level=1, drop=True)
      )
print (df)
                       allergy
french    'MAY CONTAIN: prawn'
dutch     'MAY CONTAIN: prawn'
french  'MAY CONTAIN: peanuts'
dutch   'MAY CONTAIN: peanuts'
french     'MAY CONTAIN: milk'
dutch      'MAY CONTAIN: milk'

answered Nov 09 '17 at 13:22

jezrael

822,522
95
1,334
1,252

I got `TypeError: sort_index() got an unexpected keyword argument 'level'` – user3821872 Nov 09 '17 at 13:37
1

What is your pandas version? `print (pd.show_versions())` ? – jezrael Nov 09 '17 at 13:38
the version is 0.16.2, I need it for my project – user3821872 Nov 09 '17 at 13:42
1

I find it, need change `.sort_index(level=1)` to `.sortlevel(level=1)` – jezrael Nov 09 '17 at 13:44
can I ask one more thing? is it possible to select allergy column. i.e in the excel, it shows the allergy column in D(allergy). so I ONLY want COLUMN D(allergy) to be changed, in this case, to change and not the rest of the columns. the index is column B. – user3821872 Nov 09 '17 at 14:00
I think no easy way, necessary is read all data from excel to DataFrame, change it and write back :( – jezrael Nov 09 '17 at 14:02
is there a way to say Column D(onwards) instead? change and not the whole thing? – user3821872 Nov 09 '17 at 14:05
Not sure, try create question... I really dont know, sorry :( – jezrael Nov 09 '17 at 14:05
similar to [LIKE THIS?](https://stackoverflow.com/questions/46746822/move-object-in-pandas-dataframe-from-one-cell-to-another) – user3821872 Nov 09 '17 at 14:46
I think no, it is something different ;( – jezrael Nov 09 '17 at 14:48

pandas Dataframe, MOVE Specific Value from One Cell to Another

1 Answers1