I have the following dataframe that has been concatenated from ESPN. Currently, the kick-off time are coming in as the column headers. I need assistance on the logic needed to combine the teams into one column and the associated kick off times into another column. Thank you in advance!
The input data
dfs = pd.read_html("https://www.espn.com/nfl/lines")
df = pd.concat(dfs)
df
The current output I have:
9:30 AM REC (ATS) LINE OPEN ML FPI 1:00 PM 4:05 PM 4:25 PM 8:20 PM 8:15 PM
0 New York Jets 1-3 (1-3-0) 45.0 43.5 130 42.8% NaN NaN NaN NaN NaN
1 Atlanta Falcons 1-3 (1-3-0) -2.5 -2.5 -150 56.9% NaN NaN NaN NaN NaN
0 NaN 3-1 (3-1-0) -3.0 -3.0 -150 53.6% Green Bay Packers NaN NaN NaN NaN
1 NaN 3-1 (2-2-0) 50.5 49.5 130 46.0% Cincinnati Bengals NaN NaN NaN NaN
0 NaN 0-4 (2-2-0) 49.5 49.0 350 26.1% Detroit Lions NaN NaN NaN NaN
1 NaN 1-3 (2-2-0) -10.0 -10.0 -450 73.7% Minnesota Vikings NaN NaN NaN NaN
0 NaN 3-1 (3-1-0) -1.0 1.0 -115 49.0% Denver Broncos NaN NaN NaN NaN
1 NaN 1-3 (1-3-0) 39.5 39.5 -105 50.6% Pittsburgh Steelers NaN NaN NaN NaN
0 NaN 1-3 (2-2-0) 48.0 47.5 360 24.9% Miami Dolphins NaN NaN NaN NaN
1 NaN 3-1 (1-3-0) -10.0 -10.0 -475 74.9% Tampa Bay Buccaneers NaN NaN NaN NaN
0 NaN 2-2 (2-2-0) -2.0 -- -140 57.0% New Orleans Saints NaN NaN NaN NaN
1 NaN 2-2 (1-3-0) 43.5 -- 120 42.7% Washington NaN NaN NaN NaN
0 NaN 1-3 (1-3-0) 44.5 44.5 140 35.0% Philadelphia Eagles NaN NaN NaN NaN
1 NaN 3-1 (3-1-0) -3.0 -3.0 -160 64.7% Carolina Panthers NaN NaN NaN NaN
0 NaN 2-2 (2-2-0) -4.5 -3.5 -210 61.2% Tennessee Titans NaN NaN NaN NaN
1 NaN 0-4 (1-3-0) 48.5 48.0 175 38.4% Jacksonville Jaguars NaN NaN NaN NaN
0 NaN 1-3 (2-2-0) -9.0 -9.0 -430 68.6% New England Patriots NaN NaN NaN NaN
1 NaN 1-3 (2-2-0) 39.5 39.0 330 31.1% Houston Texans NaN NaN NaN NaN
0 NaN 2-2 (2-2-0) 44.5 -- 200 36.0% NaN Chicago Bears NaN NaN NaN
1 NaN 3-1 (2-2-0) -5.5 -- -240 63.8% NaN Las Vegas Raiders NaN NaN NaN
0 NaN 3-1 (3-1-0) 47.0 46.5 115 44.8% NaN Cleveland Browns NaN NaN NaN
1 NaN 3-1 (3-1-0) -2.0 1.0 -135 54.8% NaN Los Angeles Chargers NaN NaN NaN
0 NaN 1-3 (2-2-0) 52.0 49.5 260 26.8% NaN NaN New York Giants NaN NaN
1 NaN 3-1 (4-0-0) -7.0 -7.0 -330 73.0% NaN NaN Dallas Cowboys NaN NaN
0 NaN 2-2 (1-3-0) 49.0 49.0 190 36.6% NaN NaN San Francisco 49ers NaN NaN
1 NaN 4-0 (3-1-0) -4.5 -4.5 -230 63.1% NaN NaN Arizona Cardinals NaN NaN
0 NaN 3-1 (3-1-0) 56.5 56.5 125 50.6% NaN NaN NaN Buffalo Bills NaN
1 NaN 2-2 (1-3-0) -2.5 -2.5 -145 49.0% NaN NaN NaN Kansas City Chiefs NaN
0 NaN 1-3 (2-2-0) 46.0 45.5 250 32.5% NaN NaN NaN NaN Indianapolis Colts
1 NaN 3-1 (2-2-0) -7.0 -7.0 -320 67.2% NaN NaN NaN NaN Baltimore Ravens
This is what I would like the dataframe to look like (done in excel) 1
CodePudding user response：
Try this change to your code:
dfs = pd.read_html("https://www.espn.com/nfl/lines")
dfs2 = [d.assign(kickoff = d.columns[0]).rename(columns={d.columns[0]:'TEAMS'}) for d in dfs]
df = pd.concat(dfs2)
df
The dfs2 = ... line moves the value in the name of the first column to a new column 'kickoff', and renames the first column to be 'TEAMS'
output:
TEAMS REC (ATS) LINE OPEN ML FPI kickoff
-- -------------------- ----------- ------ ------ ---- ----- ---------
0 Green Bay Packers 3-1 (3-1-0) -2 -2.0 -130 53.6% 1:00 PM
1 Cincinnati Bengals 3-1 (2-2-0) 50 49.5 110 46.1% 1:00 PM
0 Detroit Lions 0-4 (2-2-0) 49.5 49.0 350 26.1% 1:00 PM
1 Minnesota Vikings 1-3 (2-2-0) -10 -10.0 -450 73.7% 1:00 PM
0 Denver Broncos 3-1 (3-1-0) -1.5 1.0 -130 50.8% 1:00 PM
1 Pittsburgh Steelers 1-3 (1-3-0) 39.5 39.0 110 48.8% 1:00 PM
0 Miami Dolphins 1-3 (2-2-0) 48 47.5 450 24.8% 1:00 PM
1 Tampa Bay Buccaneers 3-1 (1-3-0) -11 -10.0 -650 75.0% 1:00 PM
0 New Orleans Saints 2-2 (2-2-0) -2.5 -- -135 56.9% 1:00 PM
1 Washington 2-2 (1-3-0) 43.5 -- 115 42.7% 1:00 PM
0 Philadelphia Eagles 1-3 (1-3-0) 46 44.5 120 35.0% 1:00 PM
1 Carolina Panthers 3-1 (3-1-0) -2.5 -2.5 -140 64.7% 1:00 PM
0 Tennessee Titans 2-2 (2-2-0) -4 -3.5 -210 61.2% 1:00 PM
1 Jacksonville Jaguars 0-4 (1-3-0) 48.5 48.0 175 38.5% 1:00 PM
0 New England Patriots 1-3 (2-2-0) -8 -8.0 -380 69.1% 1:00 PM
1 Houston Texans 1-3 (2-2-0) 39 39.0 300 30.6% 1:00 PM
0 Chicago Bears 2-2 (2-2-0) 46 -- 205 36.1% 4:05 PM
1 Las Vegas Raiders 3-1 (2-2-0) -5.5 -- -250 63.6% 4:05 PM
0 Cleveland Browns 3-1 (3-1-0) 47 46.5 115 44.8% 4:05 PM
1 Los Angeles Chargers 3-1 (3-1-0) -2.5 1.0 -135 54.8% 4:05 PM
0 New York Giants 1-3 (2-2-0) 52.5 49.5 260 26.9% 4:25 PM
1 Dallas Cowboys 3-1 (4-0-0) -7 -7.0 -330 72.9% 4:25 PM
0 San Francisco 49ers 2-2 (1-3-0) 48.5 48.5 205 35.6% 4:25 PM
1 Arizona Cardinals 4-0 (3-1-0) -5 -4.5 -250 64.1% 4:25 PM
0 Buffalo Bills 3-1 (3-1-0) 56.5 56.5 130 50.6% 8:20 PM
1 Kansas City Chiefs 2-2 (1-3-0) -3 -2.5 -150 49.0% 8:20 PM
0 Indianapolis Colts 1-3 (2-2-0) 46 45.5 260 32.5% 8:15 PM
1 Baltimore Ravens 3-1 (2-2-0) -7 -7.0 -330 67.2% 8:15 PM