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.