J.jaques J.jaques - 2 months ago 18
Python Question

concatenate excel datas with python or Excel

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

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

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()})
Comments