concatenate excel datas with python or Excel

Question

Here's my problem, I have an Excel sheet with 2 columns (see below)

I'd like to print (on python console or in a excel cell) all the data under this form :

 "1" : ["1123","1165", "1143", "1091", "n"], *** n ∈ [A2; A205]***

We don't really care about the Column B. But I need to add every postal code under this specific form.

is there a way to do it with Excel or in Python with Panda ? (If you have any other ideas I would love to hear them)

Cheers

Answer 1

I think you can use parse_cols for parse first column and then filter out all columns from 205 to 1000 by skiprows in read_excel:

df = pd.read_excel('test.xls', 
                   sheet_name='Sheet1', 
                   parse_cols=0, 
                   skiprows=list(range(205,1000)))
print (df)

Last use tolist for convert first column to list:

print({"1": df.iloc[:,0].tolist()})

---

The simpliest solution is parse only first column and then use iloc:

df = pd.read_excel('test.xls', 
                   parse_cols=0)

print({"1": df.iloc[:206,0].astype(str).tolist()})

Answer 2

I am not familiar with excel, but pandas could easily handle this problem.

First, read the excel to a DataFrame

import pandas as pd
df = pd.read_excel(filename)

Then, print as you like

print({"1": list(df.iloc[0:N]['A'])})

where N is the amount you would like to print. That is it. If the list is not a string list, you need to cast the int to string.

Also, there are a lot parameters that can control the load part of excel read_excel, you can go through the document to set suitable parameters.

Hope this would be helpful to you.