How to use Python to convert rows to columns in an excel(.xls)?

user_15172193 0
Viewed 127

I have an excel file as below (sample data):

Business Name   Business Address    Suburb  Zip     Questions Label  Answers
ABC                address1        Truga    3000    Employee numbers    5
ABC                address1        Truga    3000    Manager name      Alan
ABC                address1        Truga    3000    Store Type        Retail
ABC                address1        Truga    3000    Store Location     CBD
ABC                address1        Truga    3000    Compliant          Yes

I would like to have the "Question Label" data converted into columns for the same business name (ABC) and subsequently, the answers should be like this;

Business Address   Suburb   Zip  Employee numbers Manager name  Store Type Store Location   Compliant 
ABC      address1  Truga    3000    5                Alan           Retail         CBD            Yes

I tried "transpose" within excel. However, I am not getting the desired output.

Any help would be appreciated !!!

Thanks in advance

transformation rows python-3.x excel
1 Answers
import pandas as pd
x = pd.read_csv('test.csv') #reading it as csv for now
columns = x['Questions Label'].tolist() #convert questions label column to list
list1 = x['Answers'].tolist() #convert answer column to list
dictionary = {}
#map them in a dictionary
for i in range(0,len(list1)):
    dictionary[columns[i]] = [list1[i]]

# make a dataframe out of these two
df = pd.DataFrame.from_dict(dictionary)
print(df)

Now, you just have to map your older dataframe without questions label and answer columns with this dataframe.

Edit 1:

df = pd.pivot_table(x, values="Answers",columns=["Questions Label"],aggfunc = 'first').reset_index()

This is another way to get what you want.

user_154843930
Related