I’m using a Seaborn color palette using the crowd-sourced xkcd color list. set_ylabel ( '# of texts' ) if k = 0 : axs. append ( hist, hist ), c = pal, linestyle = 'solid', marker = 'o', label = name ) axs. histogram ( hours, bins = range ( 25 ), density = False ) axs. weekday () >= 5 ] else : hours = id ] if d. xkcd_palette ( colors ) for k, w in enumerate (): for i, id, name in enumerate ( zip ( handle_ids, handle_names )): if w = 'Weekends' : hours = id ] if d. subplots ( 2, 1, sharex = True, figsize = ( 10, 5 )) handle_ids, handle_names =, colors = pal = sns. If we’re going straight into an environment like an IPython notebook, we might as well load the query from chat.db directly in our session, instead of saving it as a CSV first.įire up a Jupyter/IPython notebook and import the sqlite3, pandas, and datetime packages:įig, axs = plt. but I don’t know SQL very well so I’m giving the hacky way that I know works. Important sidenote: there is probably a slick way to grab the messages and attachments with a single SQL command, and a slick way to do the timezone adjustment in the SQL command …. If you’re stopping here, one caveat: the dates are all in Greenwich Mean Time (GMT), so you may want to convert to Eastern standard or something else before you start fiddling around. We can access this with the built-in SQL tool sqlite3 from the Terminal asĪnd follow the same procedure as before to save it into a CSV.Īt this point, we can fire up our favorite data analysis tool (Python, R, Excel, whatever) and we have a convenient couple of CSVs saved to play with. (If you poke around in the surrounding folders, you’ll find that each text chain is saved by day in a file you can open in the Message.app application, same with attachments, but this is not helpful for doing anything big.) IOS archives all iMessage chats in a SQL database in /Users/username/Library/Messages/chat.db. (Might do some text analysis and/or modeling in a later post.) Doing some time-series style plots with it.Doing this extraction in a Jupyter/IPython notebook and saving it into a Pandas dataframe.Exporting a group chat as a CSV file, including the sender and type of message (text or attachment).So in this post I will give some basic recipes for The slick iMessage Analyzer app can handle group chats, and even allows you to export the chat as an easy-to-play-with CSV - but there is a limited menu of queries, it doesn’t differentiate between members of the chat, and it doesn’t make explicit distinction between text vs. So I thought I was in luck.īut I found that these resources tended to neglect group chats (for example this excellent tutorial or many nice Github repos like these PHP scripts). Questions like: who sends the most texts by hour, most used words, circadian rhythms, maybe some modeling … It turns out that (1) iOS archives all iMessages in a convenient SQL database on your Mac and (2) there is a ton of code out there to read and manipulate this data. schema to display the structure of the cities table.I wanted to do some data science-y analysis of some group conversations I’ve been in for years over iMessage (the Apple ecosystem message app). To verify the import, you use the command. import FILE TABLE to import the data from the city.csv file into the cities table. To import the c:\sqlite\city.csv file into the cities table:įirst, set the mode to CSV to instruct the command-line shell program to interpret the input file as a CSV file. We will import a CSV file named city.csv with two columns: name and population. Second, the sqlite3 tool import data from the second row of the CSV file into the table.The sqlite3 tool uses the first row of the CSV file as the names of the columns of the table. First, the sqlite3 tool creates the table.In the first scenario, you want to import data from CSV file into a table that does not exist in the SQLite database. Importing a CSV file into a table using sqlite3 tool Summary: in this tutorial, you will learn various ways to import CSV data into an SQLite table using sqlite3 and SQLite Studio tools.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |