or
or
By clicking below, you agree to our terms of service.
New to HackMD? Sign up
Syntax | Example | Reference | |
---|---|---|---|
# Header | Header | 基本排版 | |
- Unordered List |
|
||
1. Ordered List |
|
||
- [ ] Todo List |
|
||
> Blockquote | Blockquote |
||
**Bold font** | Bold font | ||
*Italics font* | Italics font | ||
~~Strikethrough~~ | |||
19^th^ | 19th | ||
H~2~O | H2O | ||
++Inserted text++ | Inserted text | ||
==Marked text== | Marked text | ||
[link text](https:// "title") | Link | ||
 | Image | ||
`Code` | Code |
在筆記中貼入程式碼 | |
```javascript var i = 0; ``` |
|
||
:smile: | ![]() |
Emoji list | |
{%youtube youtube_id %} | Externals | ||
$L^aT_eX$ | LaTeX | ||
:::info This is a alert area. ::: |
This is a alert area. |
On a scale of 0-10, how likely is it that you would recommend HackMD to your friends, family or business associates?
Please give us some advice and help us improve HackMD.
Syncing
xxxxxxxxxx
2021-GPS-Data-Skills-Course-Spreadsheets
Introduction to Data Management
Data Organization using Excel Spread Sheet
What kinds of things went wrong?
Link to Gene article with excel error: https://bmcbioinformatics.biomedcentral.com/articles/10.1186/1471-2105-5-80
A graduate student recreate the analysis and discovered the error.
Create a data dictionary link: https://help.osf.io/hc/en-us/articles/360019739054-How-to-Make-a-Data-Dictionary
Best practices
Tidy-data principles
Question: Any other data type? CSV seems too primitive, and not enough support to preserve the structure of the data.
What's wrong with the SAFI_messy data?
Mozambique Sheet
Tanzania sheet:
Common Formatting Problems
Data as dates
Excel stores dates as series numbers - counts the dats from Dec 31, 1899.
Excel is unable to aprse dates from before this date. Be careful when working with historical data
Microsoft explaining the date system: https://docs.microsoft.com/en-US/office/troubleshoot/excel/1900-and-1904-date-system
One solution: having Day, Month, and Year columns - no ambiguity
Metadata
Metadata is "data about your data"
Don't save them in the sheet itself
SQL Notes -
Database used is book_store.db.sql
What are some common operations that you want to do to your inventory?
Codify the books as per genre, year, author etc
What are some tools?
Spreadsheet
What are some problems with these tools?
data entry, accessing data, keeping track of what kind of access do you want to give to people.
Database management system (DBMS)
can address these questionsdatabase
- shared collection of related dataDatabase management system (DBMS)
- prgrams that support CRUD operations and control access to databaseCRUD - acronym - Create, Read, Update, Delete
SQL - Structured QUery Language - language used to talk to DBMS or (relational database management systems) RDBMS
Keys link tables together
primary key - the column that uniquely identify each row in a table
foreign key - a column in a table that references the foreign key in another table
SQL intro
SQL or sequel - data query language
SQL is formaily defined but implemented differently , there are imcompatibilities with other RDBMS .
popular extensions - MySQL, PostgreSQL
SQL can be run in programming langages like R and Python
SQL language and syntax
Working with the book database:
open DBbrowser
open database - load book_store.db database
First queries:
commenting in sql is a
--
e.g.--this is a comment
Using DISTINCT statement
using ORDER BY statement
find out who purchased the most books?
Day 4 SQL - part 2#####
Review:
Primary Key
Foreign Key
Database
Relational Database Management system
SQL
In Class slide review questions:
Boolean Algebra in SQL
AND
andOR
andNOT
boolean example:
(1 < -1) OR (0 == 0) -> True
(1 < -1) AND (-1 < 2) -> False
WHERE
clauseexample
who purcahsed more than 1 book?
Whose first name started with letter A or last name with letter L?
What books did customers whose id is 11111 and 11112 purchase?
Question: I also want to know who these customers and what books are. Using WHERE, how can I find out about these information?
Question: I want to check who purchased between 1 and 3 books. WHat is wrong with the following code?
caluculations
'||' renaming using AS clause
Aggregate function
avg
count
min
max
What is the average rating of the books?
group by clause
Usually used with aggreate functions
Example: what is teh number of purchased by each person in transaction table?
Joins
combine data from multiple tables
How do we get infomration about each customers' fav_book?
Not just the ISBN, but the title, author…
what does this statement do?
Tell SQL the specific columns we are joining, us ON clause:
Use where filter out all the transactins made to customers whos IDs are 11111, and 11112. Use JOIN find out who they are and USE JOIN to find out what books they purchased.
Using SQL in R:
load
library RSQLite
Full SQL lesson:
https://datacarpentry.org/sql-socialsci/
Google bigquery
######Unix Shell notes####
listing the content of the curent directory
Listing out, with more details using the -l flag
print current directory - check where you are at
Read the documentary of command
ls
moving up from the current folder
concatenate - "print" content of a file
move the files, notice that you also rename files using mv.
DANGEROUS! You cannot recover once you removed a file
sort the content of the file
sort the content, pipe (
>
) it to the filesorted-lengths.txt
print the head or tail of the file
word count
make a folder called
result
Global Regular Express Print
[Regular expression hand-on tutorial] (https://regexone.com/)
Search the number of instances of "1999"
Search the number of instances of "revolution"
the
-i
flag means ignore case.pipe the result to the file
2021-03-04_JAi-revolution.tsv
-w
search for whole word, no partial matchWhat is an API? (Application Programming Interface)
API is the acronym for Application Programming Interface, which is a software intermediary that allows two applications to talk to each other. Each time you use an app like Facebook, send an instant message, or check the weather on your phone, you’re using an API.
HTTP - HyperText Transfer Protocol - set of rules taht the client and server agree upon on what information to send and what to be expected
API request status code 200
200 means "ok"
HTTP response Status Codes
always look for 200 which means everyone is "ok"
bad errors
e.g. 404 Not found code - server cannot find information
**server error **
500 codes are server errors
APIs use: these services have APIs available to use:
Twitter
spotify
worldbank
HTTP: query String
part of a URL that assigns values to specific parameters
https://www.google.com/search?newwindow=1&q=is+santa+true
Demo
OpenFEC API
to use the API, you will need to request a key - it i slike an acocunt