Reading/Writing: Pandas

Python

Traversing Directories (folders)

pwd # print working directory
ls # list files in directory
cd # change directory (folder)

Relative Directory paths

the path relative to the current directory
./ # current directory
../ # parent directory
../../ # parent of parent directory
cd ../../ # move up two directories
cd ./drive # move into the drive directory in the current directory
cd ./drive/week5 # move into the week5 directory in the drive directory

Absolute Directory paths

unix-like pattern (Linux, MacOS, Windows WSL)
- /home/username/directory
typically follow unix-like pattern (esp. in colab)

path = “/c/Users/username/directory/file.txt”

Reading Tabular Data

read_csv()
read_sql()
read_json()

Reading CSV

import pandas as pd

df = pd.read_csv('./data.csv') 
# gets data that exists in the current directory

Reading CSV with URL

import pandas as pd

df = pd.read_csv(
    "https://raw.githubusercontent.com/nkmwicz/worldcup2018data/refs/heads/main/cleaned_events_world_cup2018.csv"
)
df.head()

	eventId	subEventName	tags	playerId	matchId	eventName	teamId	matchPeriod	eventSec	subEventId	id	x1	y1	x2	y2
0	8	Simple pass	['Accurate']	Mohammad Ibrahim Al Sahlawi	Russia - Saudi Arabia, 5 - 0	Pass	Saudi Arabia	1H	1.656214	Simple pass	258612104	50	50	35.0	53.0
1	8	High pass	['Accurate']	Abdullah Ibrahim Otayf	Russia - Saudi Arabia, 5 - 0	Pass	Saudi Arabia	1H	4.487814	High pass	258612106	35	53	75.0	19.0
2	1	Air duel	['Won', 'Accurate']	Ilya Kutepov	Russia - Saudi Arabia, 5 - 0	Duel	Russia	1H	5.937411	Air duel	258612077	25	81	37.0	83.0
3	1	Air duel	['Lost', 'Not accurate']	Yasir Gharsan Al Shahrani	Russia - Saudi Arabia, 5 - 0	Duel	Saudi Arabia	1H	6.406961	Air duel	258612112	75	19	63.0	17.0
4	8	Simple pass	['Accurate']	Salman Mohammed Al Faraj	Russia - Saudi Arabia, 5 - 0	Pass	Saudi Arabia	1H	8.562167	Simple pass	258612110	63	17	71.0	15.0

Reading SQL (with SQLAlchemy)

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sql_connection_string')
df = pd.read_sql('SELECT * FROM table', engine)

Reading JSON

import pandas as pd
import json

df = pd.read_csv('https://raw.githubusercontent.com/nkmwicz/worldcup2018data/refs/heads/main/cleaned_events_world_cup2018.csv')
df = df.head(5)
raw_data = df.to_json()

parsed_data = json.loads(raw_data)
parsed_data

{'eventId': {'0': 8, '1': 8, '2': 1, '3': 1, '4': 8},
 'subEventName': {'0': 'Simple pass',
  '1': 'High pass',
  '2': 'Air duel',
  '3': 'Air duel',
  '4': 'Simple pass'},
 'tags': {'0': "['Accurate']",
  '1': "['Accurate']",
  '2': "['Won', 'Accurate']",
  '3': "['Lost', 'Not accurate']",
  '4': "['Accurate']"},
 'playerId': {'0': 'Mohammad Ibrahim Al Sahlawi',
  '1': 'Abdullah Ibrahim Otayf',
  '2': 'Ilya Kutepov',
  '3': 'Yasir Gharsan Al Shahrani',
  '4': 'Salman Mohammed Al Faraj'},
 'matchId': {'0': 'Russia - Saudi Arabia, 5 - 0',
  '1': 'Russia - Saudi Arabia, 5 - 0',
  '2': 'Russia - Saudi Arabia, 5 - 0',
  '3': 'Russia - Saudi Arabia, 5 - 0',
  '4': 'Russia - Saudi Arabia, 5 - 0'},
 'eventName': {'0': 'Pass',
  '1': 'Pass',
  '2': 'Duel',
  '3': 'Duel',
  '4': 'Pass'},
 'teamId': {'0': 'Saudi Arabia',
  '1': 'Saudi Arabia',
  '2': 'Russia',
  '3': 'Saudi Arabia',
  '4': 'Saudi Arabia'},
 'matchPeriod': {'0': '1H', '1': '1H', '2': '1H', '3': '1H', '4': '1H'},
 'eventSec': {'0': 1.656214,
  '1': 4.487814,
  '2': 5.937411,
  '3': 6.406961,
  '4': 8.562167},
 'subEventId': {'0': 'Simple pass',
  '1': 'High pass',
  '2': 'Air duel',
  '3': 'Air duel',
  '4': 'Simple pass'},
 'id': {'0': 258612104,
  '1': 258612106,
  '2': 258612077,
  '3': 258612112,
  '4': 258612110},
 'x1': {'0': 50, '1': 35, '2': 25, '3': 75, '4': 63},
 'y1': {'0': 50, '1': 53, '2': 81, '3': 19, '4': 17},
 'x2': {'0': 35.0, '1': 75.0, '2': 37.0, '3': 63.0, '4': 71.0},
 'y2': {'0': 53.0, '1': 19.0, '2': 83.0, '3': 17.0, '4': 15.0}}

Reading JSON with orient=‘split’

split by arguments in pd.DataFrame()

import pandas as pd
import json

df = pd.read_csv('https://raw.githubusercontent.com/nkmwicz/worldcup2018data/refs/heads/main/cleaned_events_world_cup2018.csv')
df = df.head(5)
raw_data = df.to_json(orient='split')
parsed_data = json.loads(raw_data)
parsed_data

{'columns': ['eventId',
  'subEventName',
  'tags',
  'playerId',
  'matchId',
  'eventName',
  'teamId',
  'matchPeriod',
  'eventSec',
  'subEventId',
  'id',
  'x1',
  'y1',
  'x2',
  'y2'],
 'index': [0, 1, 2, 3, 4],
 'data': [[8,
   'Simple pass',
   "['Accurate']",
   'Mohammad Ibrahim Al Sahlawi',
   'Russia - Saudi Arabia, 5 - 0',
   'Pass',
   'Saudi Arabia',
   '1H',
   1.656214,
   'Simple pass',
   258612104,
   50,
   50,
   35.0,
   53.0],
  [8,
   'High pass',
   "['Accurate']",
   'Abdullah Ibrahim Otayf',
   'Russia - Saudi Arabia, 5 - 0',
   'Pass',
   'Saudi Arabia',
   '1H',
   4.487814,
   'High pass',
   258612106,
   35,
   53,
   75.0,
   19.0],
  [1,
   'Air duel',
   "['Won', 'Accurate']",
   'Ilya Kutepov',
   'Russia - Saudi Arabia, 5 - 0',
   'Duel',
   'Russia',
   '1H',
   5.937411,
   'Air duel',
   258612077,
   25,
   81,
   37.0,
   83.0],
  [1,
   'Air duel',
   "['Lost', 'Not accurate']",
   'Yasir Gharsan Al Shahrani',
   'Russia - Saudi Arabia, 5 - 0',
   'Duel',
   'Saudi Arabia',
   '1H',
   6.406961,
   'Air duel',
   258612112,
   75,
   19,
   63.0,
   17.0],
  [8,
   'Simple pass',
   "['Accurate']",
   'Salman Mohammed Al Faraj',
   'Russia - Saudi Arabia, 5 - 0',
   'Pass',
   'Saudi Arabia',
   '1H',
   8.562167,
   'Simple pass',
   258612110,
   63,
   17,
   71.0,
   15.0]]}

Reading JSON with orient=‘records’

import pandas as pd
import json

df = pd.read_csv('https://raw.githubusercontent.com/nkmwicz/worldcup2018data/refs/heads/main/cleaned_events_world_cup2018.csv')
df = df.head(5)
raw_data = df.to_json(orient='records')

parsed_data = json.loads(raw_data)
parsed_data

[{'eventId': 8,
  'subEventName': 'Simple pass',
  'tags': "['Accurate']",
  'playerId': 'Mohammad Ibrahim Al Sahlawi',
  'matchId': 'Russia - Saudi Arabia, 5 - 0',
  'eventName': 'Pass',
  'teamId': 'Saudi Arabia',
  'matchPeriod': '1H',
  'eventSec': 1.656214,
  'subEventId': 'Simple pass',
  'id': 258612104,
  'x1': 50,
  'y1': 50,
  'x2': 35.0,
  'y2': 53.0},
 {'eventId': 8,
  'subEventName': 'High pass',
  'tags': "['Accurate']",
  'playerId': 'Abdullah Ibrahim Otayf',
  'matchId': 'Russia - Saudi Arabia, 5 - 0',
  'eventName': 'Pass',
  'teamId': 'Saudi Arabia',
  'matchPeriod': '1H',
  'eventSec': 4.487814,
  'subEventId': 'High pass',
  'id': 258612106,
  'x1': 35,
  'y1': 53,
  'x2': 75.0,
  'y2': 19.0},
 {'eventId': 1,
  'subEventName': 'Air duel',
  'tags': "['Won', 'Accurate']",
  'playerId': 'Ilya Kutepov',
  'matchId': 'Russia - Saudi Arabia, 5 - 0',
  'eventName': 'Duel',
  'teamId': 'Russia',
  'matchPeriod': '1H',
  'eventSec': 5.937411,
  'subEventId': 'Air duel',
  'id': 258612077,
  'x1': 25,
  'y1': 81,
  'x2': 37.0,
  'y2': 83.0},
 {'eventId': 1,
  'subEventName': 'Air duel',
  'tags': "['Lost', 'Not accurate']",
  'playerId': 'Yasir Gharsan Al Shahrani',
  'matchId': 'Russia - Saudi Arabia, 5 - 0',
  'eventName': 'Duel',
  'teamId': 'Saudi Arabia',
  'matchPeriod': '1H',
  'eventSec': 6.406961,
  'subEventId': 'Air duel',
  'id': 258612112,
  'x1': 75,
  'y1': 19,
  'x2': 63.0,
  'y2': 17.0},
 {'eventId': 8,
  'subEventName': 'Simple pass',
  'tags': "['Accurate']",
  'playerId': 'Salman Mohammed Al Faraj',
  'matchId': 'Russia - Saudi Arabia, 5 - 0',
  'eventName': 'Pass',
  'teamId': 'Saudi Arabia',
  'matchPeriod': '1H',
  'eventSec': 8.562167,
  'subEventId': 'Simple pass',
  'id': 258612110,
  'x1': 63,
  'y1': 17,
  'x2': 71.0,
  'y2': 15.0}]

Accessing JSON in Records format

import pandas as pd
import json

df = pd.read_csv('https://raw.githubusercontent.com/nkmwicz/worldcup2018data/refs/heads/main/cleaned_events_world_cup2018.csv')
df = df.head(5)
raw_data = df.to_json(orient='records')

parsed_data = json.loads(raw_data)
for record in parsed_data:
    print(record['tags'])

['Accurate']
['Accurate']
['Won', 'Accurate']
['Lost', 'Not accurate']
['Accurate']

Converting JSON to DataFrame

import pandas as pd
import json
from io import StringIO  # convert string to file-like object

df = pd.read_csv(
    "https://raw.githubusercontent.com/nkmwicz/worldcup2018data/refs/heads/main/cleaned_events_world_cup2018.csv"
)
df = df.head(5)
raw_data = df.to_json(orient="records")
parsed_data = json.loads(raw_data)
json_str = json.dumps(parsed_data)  # json to string
df1 = pd.read_json(StringIO(json_str))  # string to file-like object to dataframe
df1

	eventId	subEventName	tags	playerId	matchId	eventName	teamId	matchPeriod	eventSec	subEventId	id	x1	y1	x2	y2
0	8	Simple pass	['Accurate']	Mohammad Ibrahim Al Sahlawi	Russia - Saudi Arabia, 5 - 0	Pass	Saudi Arabia	1H	1.656214	Simple pass	258612104	50	50	35	53
1	8	High pass	['Accurate']	Abdullah Ibrahim Otayf	Russia - Saudi Arabia, 5 - 0	Pass	Saudi Arabia	1H	4.487814	High pass	258612106	35	53	75	19
2	1	Air duel	['Won', 'Accurate']	Ilya Kutepov	Russia - Saudi Arabia, 5 - 0	Duel	Russia	1H	5.937411	Air duel	258612077	25	81	37	83
3	1	Air duel	['Lost', 'Not accurate']	Yasir Gharsan Al Shahrani	Russia - Saudi Arabia, 5 - 0	Duel	Saudi Arabia	1H	6.406961	Air duel	258612112	75	19	63	17
4	8	Simple pass	['Accurate']	Salman Mohammed Al Faraj	Russia - Saudi Arabia, 5 - 0	Pass	Saudi Arabia	1H	8.562167	Simple pass	258612110	63	17	71	15

Writing Data

to_csv()

df.to_csv('data.csv', index=False)

When you do this, Make sure you are in the right directory.