Formatting Excel to DataFrame

user_14574750 0
Viewed 83

excel sheet snapshot

Please take a look at my excel sheet snapshot attached on the top-left end. When I create a DataFrame from this sheet my first column and row are filled with NaN. I need to skip this blank row and column to select the second row and column for DataFrame creation.

    Unnamed: 0  Unnamed: 1  Unnamed: 2  Unnamed: 3
0   NaN           ID           SCOPE       TASK
1   NaN           34            XX      something_1
2   NaN           534           SS      something_2
3   NaN           43            FF      something_3
4   NaN           32            ZZ      something_4

I want my DataFrame to look like this

0   ID         SCOPE       TASK
1   34          XX      something_1
2  534          SS      something_2
3   43          FF      something_3
4   32          ZZ      something_4

I tried this code but didn't get what I expected

df = pd.read_excel("Book1.xlsx")
df.columns = df.iloc[0]
df.drop(df.index[1])
df.head()



    NaN ID  SCOPE      TASK
0   NaN ID  SCOPE      TASK
1   NaN 34  XX     something_1
2   NaN 534 SS     something_2
3   NaN 43  FF     something_3
4   NaN 32  ZZ     something_4

Still I need to drop the first column and 0 the index row from here. Can anyone help?

pandas dataframe nan excel python
3 Answers

if its an entire column you wish to delete, try this -

del df["name of the column"]

here's an eg.

import pandas as pd
import numpy as np

df = pd.DataFrame(np.random.randn(10,2),columns=['a','b']) 
# created a random dataframe 'df' with 'a' and 'b' as columns


del df['a'] # deleted column 'a' using 'del'  
print(df) # no column 'a' in 'df' now
user_145402570

You can actually do it all while reading your excel file with pandas. You want to :

  1. skip the first line : use the argument skiprows=0
  2. use the columns from B to D : use the argument usecols="B:D"
  3. use the row #2 as the header (I assumed here) : use the argument header=1 (0 indexed)

Answer :

df = pd.read_excel("Book1.xlsx", skiprows=0, usecols="B:D", header=1)

Edit : you don't even need to use skiprows when using header.

df = pd.read_excel("Book1.xlsx", usecols="B:D", header=1)
user_104090930

Specify the row number which will be the header (column names) of the dataframe using header parameter; in your case it is 1. Also, specify the column names using usecols parameter, in your case, they are 'ID', 'SCOPE', and 'TASK'.

df = pd.read_excel('your_excel_file.xlsx', header=1, usecols=['ID','SCOPE', 'TASK'])

Check out header and usecols from here.

user_47148430
Related