I have a csv data file which has 2 headers that means one header as a question and the second one as a sub header which has multiple levels or answers for the main header. Current csv look like below table

Header  Which country do you live?        Which country you previously visited? 
Users    Canada  USA   UK  Mexico  Norway India  Singapore  Pakistan 
User 1   Canada                                  Singapore
User 2                 UK                 India              
User 3                     Mexico                           Pakistan
User 4                             Norway India 

I need to transform it into below table

Users   Which country do you live?  Which country you previously visited?
User 1  Canada                      Singapore
User 2  UK                          India
User 3  Norway                      Pakistan
User 4  Mexico                      India

Can someone help me with this?

This is how my data look like enter image description here

My input file look like this enter image description here and this is how my final output look like enter image description here

1 Answers

2
jezrael On Best Solutions

First back filling missing values by bfill, then select first column and remove second level of MultiIndex by DataFrame.droplevel:

print (df.columns)
MultiIndex(levels=[['Header', 'Which country do you live?'],
                   ['Canada', 'Mexico', 'UK', 'USA', 'Users']],
           codes=[[0, 1, 1, 1, 1], [4, 0, 3, 2, 1]])

#if first column is not index, create it
#df = df.set_index([df.columns[0]])
#if empty strings repalce them to NaNs
#df = df.replace('', np.nan)

df = df.bfill(axis=1).iloc[:, 0].reset_index().droplevel(level=1, axis=1)
print (df)
   Header Which country do you live?
0  User 1                     Canada
1  User 2                         UK
2  User 3                     Mexico
3  User 4                     Norway

EDIT:

df = df.groupby(level=0, axis=1).apply(lambda x: x.bfill(axis=1).iloc[:, 0])
print (df)
   Header Which country do you live? Which country you previously visited?
0  User 1                     Canada                             Singapore
1  User 2                         UK                                 India
2  User 3                     Mexico                              Pakistan
3  User 4                     Norway                                 India