How to extract excel column data into python list using pandas from merged cell

Question

i'm trying to extract 'Country' column data into python list using pandas. Below the code i used to. Also attached excel sheet and output.

code:

from pandas import DataFrame
import pandas as pd
open_file = pd.read_excel('data.xlsx', sheet_name=0)
df = list(open_file['Country'])
print(df)

Output:

[nan, 'Great Britain', 'China ', 'Russia', 'United States', 'Korea', 'Japan', 'Germany']

Process finished with exit code 0

In the output i can see 'nan' because in the sheet two cells are merged into one. How to avoid this?

do you mind sharing this data as an excel file, so I or anyone else can have a go at it? — sammywemmy
– sammywemmy, Commented Aug 13, 2020 at 7:22
please provide a minimal reproducible example and see How to Ask you have nan because column A1:A has no data. — Umar.H
– Umar.H, Commented Aug 13, 2020 at 7:22

Ajay A · Accepted Answer · 2020-08-13 11:12:59Z

1

Try this

df = pd.read_excel('data.xlsx', header[0,1])
df = df.rename(columns=lambda x: x if not 'Unnamed' in str(x) else '')

Now the headers are in the form of tuples. For ex, to access Country or Column Gold, you need to write something like below statements

print(df[('Country', '')])
print(df[('Media Tally', 'Gold')])

answered Aug 13, 2020 at 11:12

Ajay A

1,0531 gold badge9 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Prakash · Accepted Answer · 2020-08-13 10:56:41Z

0

Use header=1 and then you can use it with unnamed :0 or 1 or 2 to get column values to list

import pandas as pd

df = pd.read_excel('data.xlsx', sheet_name=0, header=1)
print(df['Unnamed: 0'].to_list())

answered Aug 13, 2020 at 10:56

Prakash

1921 silver badge10 bronze badges