How do I assign year&months in PD dataframe?

user_17397054 0
Viewed 55

My pandaframe looks very weird after running the code. The data doesnt not come with a year/month variable so I have to add them manually. Is there a way I could do that?

sample = []

url1 = "https://api.census.gov/data/2018/cps/basic/jan?get=PEFNTVTY,PEMNTVTY&for=state:01&PEEDUCA=39&key=YOUR_KEY_GOES_HERE"
url2 = "https://api.census.gov/data/2018/cps/basic/feb?get=PEFNTVTY,PEMNTVTY&for=state:01&PEEDUCA=39&key=YOUR_KEY_GOES_HERE"
url3 = "https://api.census.gov/data/2018/cps/basic/mar?get=PEFNTVTY,PEMNTVTY&for=state:01&PEEDUCA=39&key=YOUR_KEY_GOES_HERE"

sample.append(requests.get(url1).text)
sample.append(requests.get(url2).text)
sample.append(requests.get(url3).text)

sample = [json.loads(i) for i in sample]
sample = pd.DataFrame(sample)
sample

This link is an image of what I get after running the code with my key.

pandas time-series dataframe list python
2 Answers

Consider read_json to directly read the Census URL API inside a user-defined method. Then iterate pairwise through all possible pairs of years and months using itertools.product to build data frame and assign corresponding columns:

import pandas as pd
import calendar
import itertools

def get_census_data(year, month):
    # BUILD DYNAMIC URL
    url = (
        f"https://api.census.gov/data/{year}/cps/basic/{month.lower()}?"
        "get=PEFNTVTY,PEMNTVTY&for=state:01"
    )

    # CLEAN RAW DATA FOR APPROPRIATE ROWS AND COLS, ASSIGN YEAR/MONTH COLS
    raw_df = pd.read_json(url)    
    cps_df = (
        pd.DataFrame(raw_df.iloc[1:,])
          .set_axis(raw_df.iloc[0,], axis="columns", inplace=False)
          .assign(year = year, month = month)
    )
    
    return cps_df

# MONTH AND YEAR LISTS
months_years = itertools.product(
    range(2010, 2021),
    calendar.month_abbr[1:13]
)

# ITERATE PAIRWISE THROUGH LISTS
cps_list = [get_census_data(yr, mo) for yr, mo in months_years]

# COMPILE AND CLEAN FINAL DATA FRAME
cps_df = (
    pd.concat(cps_list, ignore_index=True)
      .drop_duplicates()
      .reset_index(drop=True)
      .rename_axis(None, axis="columns")
)

Output

cps_df
     PEFNTVTY PEMNTVTY state  year month
0          57       57     1  2010   Jan
1         303      303     1  2010   Jan
2         233      233     1  2010   Jan
3          57      233     1  2010   Jan
4          73       73     1  2010   Jan
      ...      ...   ...   ...   ...
6447      210      139     1  2020   Dec
6448      363      363     1  2020   Dec
6449      301       57     1  2020   Dec
6450       57      242     1  2020   Dec
6451      416      416     1  2020   Dec

[6452 rows x 5 columns]
user_14224510

The response to each API call is a JSON array of arrays. You called the wrong DataFrame constructor. Try this:

base_url = "https://api.census.gov/data/2018/cps/basic"
params = {
    "get": "PEFNTVTY,PEMNTVTY",
    "for": "state:01",
    "PEEDUCA": 39,
}

df = []
for month in ["jan", "feb", "mar"]:
    r = requests.get(f"{base_url}/{month}", params=params)
    r.raise_for_status()
    
    j = r.json()
    df.append(pd.DataFrame.from_records(j[1:], columns=j[0]).assign(month=month))

df = pd.concat(df)

Result:

  PEFNTVTY PEMNTVTY PEEDUCA state month
0       57       57      39     1   jan
1       57       57      39     1   jan
2       57       57      39     1   jan
3       57       57      39     1   jan
4       57       57      39     1   jan
...
user_25389390
Related