CODEWITHSUNDEEP
pythonpandasdataframelabel
BillyR
BillyR

Reputation: 3

How can I replace the column labels of a dataframe with values from one of it's rows

I have an xls data file which I've read into python using Pandas as a dataframe, but the data that I would like to form the column headings are in row 5 of the xls file.

Is there a way to extract a row of values from a dataframe and then replace the column labels with those values?

I would like the values in row 3 of the dataframe (update time, PV1 voltage (V), PV1 current (A)) to replace the current column labels. I don't need any of the data in rows 1-4 in the xls file.

I'm a real python beginner and can't figure it out!

data = pd.read_excel('H1E372F8273057 2020-02-17.xls')

data =

                 site name     richardssite       report type
0                  NaN              NaN               NaN
1                  NaN              NaN               NaN
2          Inverter SN   H1E372F8273056  Registration No.
3          update time  PV1 voltage (V)   PV1 current (A)
4  2020-02-17 00:03:15                0                 0
5  2020-02-17 00:08:15                0                 0
6  2020-02-17 00:13:16                0                 0
7  2020-02-17 00:18:15                0                 0
8  2020-02-17 00:23:15                0                 0

Upvotes: 0

Views: 115

Answers (1)

Guilhem L.
Guilhem L.

Reputation: 421

The option header should do the job to select the columns name.

data = pd.read_excel('H1E372F8273057 2020-02-17.xls', header=4)

To remove the rows, you can use skiprows option, but not totally sure if the loading function first remove the lines then set the header or the other way, wich can have influence on the number to give in header.

data = pd.read_excel('H1E372F8273057 2020-02-17.xls', header=4,skiprows=[0,1,2,3])

Upvotes: 1

Related Questions