I'm new to accessing data from excel using python. I need something like this.
[Id:{1,2,3}, Name:{Raj,Rahul,Dave}, Age:{28,29,29}, City:{Delhi,Mumbai,Bengaluru}]
I need this kind of data in python list of dictionaries.
this is the excel data:
Add a comment
|
2 Answers
Here is a way to do it using the pandas library :
import pandas as pd
df= pd.read_excel('your_file.xlsx')
df = df.T
df.columns = df.iloc[0]
df = df.drop(df.index[0])
df.to_dict()
Output :
{'Name': {1: 'Raj', 2: 'Rahul', 3: 'Dave'},
'Age': {1: 28, 2: 29, 3: 29},
'City': {1: 'Delhi', 2: 'Mumbai', 3: 'Bengaluru'}}
Comments
It is always preferred that you insert a person wise record in a single row. Never the less, you can also do it by csv and defaultdict module in Python.
import csv
from collections import defaultdict
fileName = "./sample.csv"
# initializing the titles and rows list
fields = []
rows = []
# reading csv file
with open(fileName, 'r') as csvfile:
# creating a csv reader object
csvreader = csv.reader(csvfile)
# extracting field names through first row
record = defaultdict(lambda: None)
for rows in csvreader:
row = rows[0].split(" ")
for i in range(1, len(row)):
if(record[row[0]]):
record[row[0]].append(row[i])
else:
record[row[0]] = [row[i]]
print(dict(record))
