Splitting a series index to multiple columns-CodePudding

I'm downloading historical CPI numbers and want to figure out how to split the year, month, and monthly figure into their own columns. Normally I would split a string by the commas within the column. However, when pulling the numbers, I don't get that. Would it be easiest to split by position of character in the string?

import cpi
import pandas as pd

cpi.update()
series_df =cpi.series.get_by_id("CUSR0000SA0")
Series_CPI_DF =pd.DataFrame(series_df.indexes)   
Series_CPI_DF.to_excel('S_CPI_DF.xlsx')

An example of what I get at the end is '2005-06-01 (June): 193.7'.

CodePudding user response：

You can use to_dataframe method instead of pd.DataFrame and select the desired columns.

import cpi
import pandas as pd

cpi.update()
series_df =cpi.series.get_by_id("CUSR0000SA0")
Series_CPI_DF = series_df.to_dataframe()[['year', 'date', 'value', 'period_name']]
Series_CPI_DF.to_excel('S_CPI_DF.xlsx')

# Output :

print(Series_CPI_DF)

     year        date  value period_name
0    1997  1997-01-01  159.4     January
1    1997  1997-02-01  159.7    February
2    1997  1997-03-01  159.8       March
3    1997  1997-04-01  159.9       April
4    1997  1997-05-01  159.9         May
..    ...         ...    ...         ...
903  1996  1996-08-01  157.2      August
904  1996  1996-09-01  157.7   September
905  1996  1996-10-01  158.2     October
906  1996  1996-11-01  158.7    November
907  1996  1996-12-01  159.1    December

[908 rows x 4 columns]