Bitcoin block data available in CSV format

[LoyceV]

Without further ado:
The data
►Directory:
loyce.club/blockdata/

►All-in-one file:

| | |

Text file blockdata.txt blockdata.lastday.txt

| | |

Size (MB) 362 0.107

| | |

GNU zip compressed file blockdata.txt.gz blockdata.lastday.txt.gz

| | |

Size (MB) 129 0.042

| | |

This file is in CSV (Comma-Separated Values) format. It can be imported into a spreadsheet (this might take a while considering the size).
►Sample:
The file blockdata.txt has the following columns with data for each block, the first line is a header:

Code:

id,hash,time,median_time,size,stripped_size,weight,version,version_hex,version_bits,merkle_root,nonce,bits,difficulty,chainwork,coinbase_data_hex,transaction_count,witness_count,input_count,output_count,input_total,input_total_usd,output_total,output_total_usd,fee_total,fee_total_usd,fee_per_kb,fee_per_kb_usd,fee_per_kwu,fee_per_kwu_usd,cdd_total,generation,generation_usd,reward,reward_usd,guessed_miner
0,000000000019d6689c085ae165831e934ff763ae46a2a6c172b3f1b60a8ce26f,2009-01-03 18:15:05,2009-01-03 18:15:05,285,285,1140,1,1,000000000000000000000000000001,4a5e1e4baab89f3a32518a88c31bc87f618f76673e2cc77ab2127b7afdeda33b,2083236893,486604799,1,0000000000000000000000000000000000000000000000000000000100010001,04ffff001d0104455468652054696d65732030332f4a616e2f32303039204368616e63656c6c6f72206f6e206272696e6b206f66207365636f6e64206261696c6f757420666f722062616e6b73,1,0,1,1,0,0,5000000000,0.5,0,0,0,0,0,0,0,5000000000,0.5,5000000000,0.5,Unknown
1,00000000839a8e6886ab5951d76f411475428afc90947ee320161bbf18eb6048,2009-01-09 02:54:25,2009-01-09 02:54:25,215,215,860,1,1,000000000000000000000000000001,0e3e2357e806b6cdb1f70b54c3a3a17b6714ee1f0e68bebb44a74b1efd512098,2573394689,486604799,1,0000000000000000000000000000000000000000000000000000000200020002,04ffff001d0104,1,0,1,1,0,0,5000000000,0.5,0,0,0,0,0,0,0,5000000000,0.5,5000000000,0.5,Unknown
2,000000006a625f06636b8bb6ac7b960a8d03705d1ace08b1a19da3fdcc99ddbd,2009-01-09 02:55:44,2009-01-09 02:54:25,215,215,860,1,1,000000000000000000000000000001,9b0fc92260312ce44e74ef369f5c66bbb85848f2eddd5a7a1cde251e54ccfdd5,1639830024,486604799,1,0000000000000000000000000000000000000000000000000000000300030003,04ffff001d010b,1,0,1,1,0,0,5000000000,0.5,0,0,0,0,0,0,0,5000000000,0.5,5000000000,0.5,Unknown
3,0000000082b5015589a3fdf2d4baff403e6f0be035a5d9742c1cae6295464449,2009-01-09 03:02:53,2009-01-09 02:55:44,215,215,860,1,1,000000000000000000000000000001,999e1c837c76a1b7fbb7e57baf87b309960f5ffefbf2a9b95dd890602272f644,1844305925,486604799,1,0000000000000000000000000000000000000000000000000000000400040004,04ffff001d010e,1,0,1,1,0,0,5000000000,0.5,0,0,0,0,0,0,0,5000000000,0.5,5000000000,0.5,Unknown
4,000000004ebadb55ee9096c9a2f8880e09da59c0d68b1c228da88e48844a1485,2009-01-09 03:16:28,2009-01-09 02:55:44,215,215,860,1,1,000000000000000000000000000001,df2b060fa2e5e9c8ed5eaf6a45c13753ec8c63282b2688322eba40cd98ea067a,2850094635,486604799,1,0000000000000000000000000000000000000000000000000000000500050005,04ffff001d011a,1,0,1,1,0,0,5000000000,0.5,0,0,0,0,0,0,0,5000000000,0.5,5000000000,0.5,Unknown
5,000000009b7262315dbf071787ad3656097b892abffd1f95a1a022f896f533fc,2009-01-09 03:23:48,2009-01-09 03:02:53,215,215,860,1,1,000000000000000000000000000001,63522845d294ee9b0188ae5cac91bf389a0c3723f084ca1025e7d9cdfe481ce1,2011431709,486604799,1,0000000000000000000000000000000000000000000000000000000600060006,04ffff001d0120,1,0,1,1,0,0,5000000000,0.5,0,0,0,0,0,0,0,5000000000,0.5,5000000000,0.5,Unknown
6,000000003031a0e73735690c5a1ff2a4be82553b2a12b776fbd3a215dc8f778d,2009-01-09 03:29:49,2009-01-09 03:02:53,215,215,860,1,1,000000000000000000000000000001,20251a76e64e920e58291a30d4b212939aae976baca40e70818ceaa596fb9d37,2538380312,486604799,1,0000000000000000000000000000000000000000000000000000000700070007,04ffff001d0123,1,0,1,1,0,0,5000000000,0.5,0,0,0,0,0,0,0,5000000000,0.5,5000000000,0.5,Unknown
7,0000000071966c2b1d065fd446b1e485b2c9d9594acd2007ccbd5441cfc89444,2009-01-09 03:39:29,2009-01-09 03:16:28,215,215,860,1,1,000000000000000000000000000001,8aa673bc752f2851fd645d6a0a92917e967083007d9c1684f9423b100540673f,2258412857,486604799,1,0000000000000000000000000000000000000000000000000000000800080008,04ffff001d012b,1,0,1,1,0,0,5000000000,0.5,0,0,0,0,0,0,0,5000000000,0.5,5000000000,0.5,Unknown
8,00000000408c48f847aa786c2268fc3e6ec2af68e8468a34a28c61b7f1de0dc6,2009-01-09 03:45:43,2009-01-09 03:16:28,215,215,860,1,1,000000000000000000000000000001,a6f7f1c0dad0f2eb6b13c4f33de664b1b0e9f22efad5994a6d5b6086d85e85e3,1716931356,486604799,1,0000000000000000000000000000000000000000000000000000000900090009,04ffff001d012c,1,0,1,1,0,0,5000000000,0.5,0,0,0,0,0,0,0,5000000000,0.5,5000000000,0.5,Unknown

►Separate columns:
Instead of killing your spreadsheet, each column can be downloaded separately (the first line is a header). The column "id" (block number) is added to each file (except for id.txt) to detect errors. Make sure the rows are aligned when inserting the columns you need:

| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

Text file id.txt hash.txt time.txt median_time.txt size.txt stripped_size.txt weight.txt version.txt version_hex.txt version_bits.txt merkle_root.txt nonce.txt bits.txt difficulty.txt chainwork.txt coinbase_data_hex.txt transaction_count.txt witness_count.txt input_count.txt output_count.txt input_total.txt input_total_usd.txt output_total.txt output_total_usd.txt fee_total.txt fee_total_usd.txt fee_per_kb.txt fee_per_kb_usd.txt fee_per_kwu.txt fee_per_kwu_usd.txt cdd_total.txt generation.txt generation_usd.txt reward.txt reward_usd.txt guessed_miner.txt

| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

Size (MB) 4.1 39 12 12 3.8 3.7 4.1 2.9 2.6 19 39 6.4 6.0 9.1 39 61 2.3 1.5 2.4 2.4 6.9 5.2 7.7 5.4 4.4 4.2 3.9 3.6 4.0 3.5 8.2 6.6 4.3 6.6 5.4 5.0

| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

GNU zip compressed file id.txt.gz hash.txt.gz time.txt.gz median_time.txt.gz size.txt.gz stripped_size.txt.gz weight.txt.gz version.txt.gz version_hex.txt.gz version_bits.txt.gz merkle_root.txt.gz nonce.txt.gz bits.txt.gz difficulty.txt.gz chainwork.txt.gz coinbase_data_hex.txt.gz transaction_count.txt.gz witness_count.txt.gz input_count.txt.gz output_count.txt.gz input_total.txt.gz input_total_usd.txt.gz output_total.txt.gz output_total_usd.txt.gz fee_total.txt.gz fee_total_usd.txt.gz fee_per_kb.txt.gz fee_per_kb_usd.txt.gz fee_per_kwu.txt.gz fee_per_kwu_usd.txt.gz cdd_total.txt.gz generation.txt.gz generation_usd.txt.gz reward.txt.gz reward_usd.txt.gz guessed_miner.txt.gz

| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

File sizes on May 6, 2020. I won't update the size as they grow each day.

Background
In Bitcoin's Empty Blocks Analaysis, mikeywith asked if I can help scrape data for a 10 year analysis. Tranthidung posted a link to Blockchair Database Dumps, which offers compressed block summaries with daily updates.

Updates
All files will be updated daily.

Credits
Blockchair Database Dumps has a staggering amount of data, easily accessible in daily files (at 10 kB/s). All data presented in this topic comes from Blockchair.
I've converted these files to make it easier to access all data at once.

No spam please.
Self-moderated against spam. Discussion and questions are welcome.

Related topics
Bitcoin block data available in CSV format
List of all Bitcoin addresses with a balance
List of all Bitcoin addresses ever used
[~500 GB] Bitcoin block data: inputs, outputs and transactions
[800 GB] Ethereum data

[tranthidung]

I have a suggestion.

To make sure that people don't import data incorrectly from separated pages because a lack of at least one indicator, such as block height or blockid. Sometimes people might sort data descendingly or ascendingly, and by doing so they will lost original orders if data does not have a fix id variable.

I would prefer to have a common and indicative variable for each data page. That is blockheight, or id depends on the way you name it. From OP, I think you name it as id.

I meant it will be better if the data pages always include id (always fixed in the first column) besides others details in second column.

[LoyceV]

I would prefer to have a common and indicative variable for each data page. That is blockheight, or id depends on the way you name it.

Done! After importing, the first column can easily be deleted so this makes sense.
That means the file sizes in the OP are already a few MB too small. I won't bother changing the table.

Important change: I've added a header to all files.

From OP, I think you name it as id.

I use the same column names as Blockchair uses in their data dumps.

Update: cronjob activated. Daily updates start at 2:20 AM Amsterdam time. Please let me know if anything goes wrong.

[LoyceV]

There's another very useful data dump on Blockchair that deserves attention: blockchair_bitcoin_addresses_latest.tsv.gz!

On many occasions I found myself searching for a complete snapshot of all funded addresses, for instance to search for a balance when you only have a partial Bitcoin address. This file takes almost a day to download at 10 kB/s.

Update: I made a mirror:

See addresses.loyce.club. I keep a few snapshots of Blockchair's daily data.

See List of all Bitcoin addresses with a balance.

[BlackHatCoiner]

You've done a great job there, mate! Having this information in csv means that we can see what is the biggest fee (for example) or the biggest difficulty, right?

[LoyceV]

Having this information in csv means that we can see what is the biggest fee (for example) or the biggest difficulty, right?

Right.

[bitmover]

Text file cdd_total.txt

Hello Loyce,

What is this item, CDD? I made a small research and couldn't discover what it is.

I will try to make some more graphics with this data.

[LoyceV]

What is this item, CDD?

I don't know, I just used Blockchair's names.

[bitmover]

What is this item, CDD?

I don't know, I just used Blockchair's names.

I Just discovered

cdd_total    float    The number of destroyed coindays

https://blockchair.com/api/docs#link_001


Cdd is a measure of bitcoin transaction volume.
Bitcoin days destroyed for any given transaction is calculated by taking the number of Bitcoins in a transaction and multiplying it by the number of days it has been since those coins were last spent.
https://en.bitcoin.it/wiki/Bitcoin_Days_Destroyed

[bitmover]

Hello LoyceV,

I will make one more data request if you don't mind

can you get  this data from Blockchair API  (https://blockchair.com/api/docs) and put it into a CSV file?

Code:

mempool_transactions — number of transactions in the mempool
mempool_size — mempool size in bytes
mempool_tps — number of transactions per second added to the mempool
mempool_total_fee_usd — sum of transaction fees in the mempool, in USD

[LoyceV]

can you get  this data from Blockchair API  (https://blockchair.com/api/docs) and put it into a CSV file?

I haven't used their API yet, and I don't really have the time to figure out how it works. At first glance, it looks like it only gives the current state. If you can figure out how I can get the data, I can probably collect it for you and convert it to CSV-format.

[LoyceV]

Update
For the full blockdata.txt CSV, I've added a daily file with only the new lines:

►All-in-one file:

| | |

Text file blockdata.txt blockdata.lastday.txt

| | |

Size (MB) 362 0.107

| | |

GNU zip compressed file blockdata.txt.gz blockdata.lastday.txt.gz

| | |

Size (MB) 129 0.042

| | |

The file blockdata.lastday.txt will be overwritten daily.

[Vod]

The file blockdata.lastday.txt will be overwritten daily.

What time will you update?

[LoyceV]

What time will you update?

My update starts 4:20 AM Amsterdam time. My server time follows daylight saving time (twice a year it changes one hour).
After I start my update, it takes 2 minutes to complete. However, in rare occasions of prolonged high server load, AWS throttles the CPU down to 20%. Combined with the high load, that can cause the update to take at least ten times longer. So keep some margin after the start of my cronjob

Full disclosure: during the daily update, I add lines to existing live TXT files, so if you download at "the wrong moment" right when I'm updating, you may miss out on the last lines. The compressed file doesn't have that risk.

[TheArchaeologist]

There's another very useful data dump on Blockchair that deserves attention: blockchair_bitcoin_addresses_latest.tsv.gz!

On many occasions I found myself searching for a complete snapshot of all funded addresses, for instance to search for a balance when you only have a partial Bitcoin address. This file takes almost a day to download at 10 kB/s.

For the next couple of days you can also get all funded addresses here (771 megabyte) at a fast download speed:
https://balances.crypto-nerdz.org/balances/balances-bitcoin-20200708-0000-MXSyuyTD.gz

Please note: it's not my site/data and link will probably be locked again. So if you need it I suggest you get it now.

[LoyceV]

For the next couple of days you can also get all funded addresses here (771 megabyte) at a fast download speed:
https://balances.crypto-nerdz.org/balances/balances-bitcoin-20200708-0000-MXSyuyTD.gz

Your quote is slightly off-topic here, but it made me realize I forgot to link List of all Bitcoin addresses with a balance. I have this data available with daily updates and it won't get locked any time soon.

It is fast indeed (104 MB/s)!

[LoyceV]

gz.blockchair.com now shows:

402 Payment Required
nginx/1.14.2

Database Dumps still shows it's available at low speed. I can't find any payment information. This may be the end of my updates.
Update: it works again, running an update now

I now notice the .gz-files get updated (with a new time stamp) even when fetching data failed. I won't add a check for this, so I'll leave it up to the user to check if column "id" (block number) is complete.

[Vod]

Any chance you can do a full refresh at the start of each month?  (you're late!)

And then each file after that I assume is incremental? 

[LoyceV]

Any chance you can do a full refresh at the start of each month?

I do a full refresh each day.

(you're late!)

My update starts at the same time, it just takes a while to process.

And then each file after that I assume is incremental?

Are you looking to download only new additions once a month?

[Vod]

Any chance you can do a full refresh at the start of each month?

I do a full refresh each day.

Ah OK.  From your initial wording, I thought you did one dump, and then just the new data every day.