CODEWITHSUNDEEP
pythonpandasdataframedatetimeindex
Anurag Dhadse
Anurag Dhadse

Reputation: 1873

Pandas not converting certain columns of dataframe to datetimeindex

My dataframe until now, enter image description here

and I am trying to convert cols which is a list of all columns from 0 to 188 ( cols = list(hdata.columns[ range(0,188) ]) ) which are in this format yyyy-mm to datetimeIndex. There are other few columns as well which are 'string' Names and can't be converted to dateTime hence,so I tried doing this,

hdata[cols].columns = pd.to_datetime(hdata[cols].columns) #convert columns to **datetimeindex**

But this is not working. Can you please figure out what is wrong here?

Edit: A better way to work on this type of data is to use Split-Apply-Combine method.

Step 1: Split the data which you want to perform some specific operation.

nonReqdf = hdata.iloc[:,188:].sort_index()
reqdf= reqdf.drop(['CountyName','Metro','RegionID','SizeRank'],axis=1)

Step 2: do the operations. In my case it was converting the dataframe columns with year and months to datetimeIndex. And resample it quarterly.

reqdf.columns = pd.to_datetime(reqdf.columns)
reqdf = reqdf.resample('Q',axis=1).mean()
reqdf = reqdf.rename(columns=lambda x: str(x.to_period('Q')).lower()).sort_index() # renaming so that string is yyyy**q**<1/2/3/4> like 2012q1 or 2012q2 likewise

Step 3: Combine the two splitted dataframe.(merge can be used but may depend on what you want)

reqdf = pd.concat([reqdf,nonReqdf],axis=1)

Upvotes: 1

Views: 194

Answers (1)

RichieV
RichieV

Reputation: 5183

In order to modify some of the labels from an Index (be it for rows or columns), you need to use df.rename as in

for i in range(188):
    df.rename({df.columns[i]: pd.to_datetime(df.columns[i])},
        axis=1, inplace=True)

Or you can avoid looping by building a full sized index to cover all the columns with

df.columns = (
    pd.to_datetime(cols) # pass the list with strings to get a partial DatetimeIndex
    .append(df.columns.difference(cols)) # complete the index with the rest of the columns
)

Upvotes: 1

Related Questions