import polars as pl
from era_pl import load_parquetWorking with DERA Notes data using Polars
SEC
DERA
Polars
The source code for this note can be found here. The package era_pl is installed using pip install 'era-py[polars]'. See here for a discussion of how to get the DERA Notes data into Parquet files.
There are three Parquet files that I use here:
submissions = load_parquet('sub_notes_2024q1', 'dera_notes')
numericFacts = load_parquet('num_notes_2024q1', 'dera_notes')
dimensions = load_parquet('dim_notes_2024q1', 'dera_notes')The commands above are equivalent to the following if you put the data files in your working directory:
submissions = pl.scan_parquet('sub_notes_2024q1.parquet')
numericFacts = pl.scan_parquet('num_notes_2024q1.parquet')
dimensions = pl.scan_parquet('dim_notes_2024q1.parquet')I use the former form because I have organized my Parquet data repository along the lines outlined here or here.
submissions.head().collect()
shape: (5, 40)
| adsh | cik | name | sic | countryba | stprba | cityba | zipba | bas1 | bas2 | baph | countryma | stprma | cityma | zipma | mas1 | mas2 | countryinc | stprinc | ein | former | changed | afs | wksi | fye | form | period | fy | fp | filed | accepted | prevrpt | detail | instance | nciks | aciks | pubfloatusd | floatdate | floataxis | floatmems |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| str | i32 | str | str | str | str | str | str | str | str | str | str | str | str | str | str | str | str | str | str | str | date | str | bool | str | str | date | i32 | str | date | datetime[μs, America/New_York] | bool | bool | str | i32 | str | f64 | date | str | i32 |
| "0000002178-24-000004" | 2178 | "ADAMS RESOURCES & ENERGY, INC." | "5172" | "US" | "TX" | "HOUSTON" | "77027" | "17 S. BRIAR HOLLOW LN." | null | "713-881-3600" | "US" | "TX" | "HOUSTON" | "77001" | "P O BOX 844" | null | "US" | "DE" | "741753147" | "ADAMS RESOURCES & ENERGY INC" | 1992-07-03 | "2-ACC" | false | "1231" | "8-K" | 2024-02-29 | null | null | 2024-02-21 | 2024-02-21 16:13:00 EST | false | false | "ae-20240221_htm.xml" | 1 | null | null | null | null | null |
| "0000002178-24-000033" | 2178 | "ADAMS RESOURCES & ENERGY, INC." | "5172" | "US" | "TX" | "HOUSTON" | "77027" | "17 S. BRIAR HOLLOW LN." | null | "713-881-3600" | "US" | "TX" | "HOUSTON" | "77001" | "P O BOX 844" | null | "US" | "DE" | "741753147" | "ADAMS RESOURCES & ENERGY INC" | 1992-07-03 | "2-ACC" | false | "1231" | "8-K" | 2024-02-29 | null | null | 2024-03-13 | 2024-03-13 16:08:00 EDT | false | false | "ae-20240313_htm.xml" | 1 | null | null | null | null | null |
| "0000002178-24-000035" | 2178 | "ADAMS RESOURCES & ENERGY, INC." | "5172" | "US" | "TX" | "HOUSTON" | "77027" | "17 S. BRIAR HOLLOW LN." | null | "713-881-3600" | "US" | "TX" | "HOUSTON" | "77001" | "P O BOX 844" | null | "US" | "DE" | "741753147" | "ADAMS RESOURCES & ENERGY INC" | 1992-07-03 | "2-ACC" | false | "1231" | "10-K" | 2023-12-31 | 2023 | "FY" | 2024-03-13 | 2024-03-13 16:13:00 EDT | false | true | "ae-20231231_htm.xml" | 1 | null | 8.7396622e7 | 2023-06-30 | null | 1 |
| "0000002488-24-000009" | 2488 | "ADVANCED MICRO DEVICES INC" | "3674" | "US" | "CA" | "SANTA CLARA" | "95054" | "2485 AUGUSTINE DRIVE" | null | "(408) 749-4000" | "US" | "CA" | "SANTA CLARA" | "95054" | "2485 AUGUSTINE DRIVE" | null | "US" | "DE" | "941692300" | null | null | "1-LAF" | false | "1231" | "8-K" | 2024-01-31 | null | null | 2024-01-30 | 2024-01-30 16:16:00 EST | false | false | "amd-20240130_htm.xml" | 1 | null | null | null | null | null |
| "0000002488-24-000012" | 2488 | "ADVANCED MICRO DEVICES INC" | "3674" | "US" | "CA" | "SANTA CLARA" | "95054" | "2485 AUGUSTINE DRIVE" | null | "(408) 749-4000" | "US" | "CA" | "SANTA CLARA" | "95054" | "2485 AUGUSTINE DRIVE" | null | "US" | "DE" | "941692300" | null | null | "1-LAF" | true | "1231" | "10-K" | 2023-12-31 | 2023 | "FY" | 2024-01-31 | 2024-01-31 17:17:00 EST | false | true | "amd-20231230_htm.xml" | 1 | null | 1.8290e11 | 2023-06-30 | null | 1 |
numericFacts.head().collect()
shape: (5, 16)
| adsh | tag | version | ddate | qtrs | uom | dimh | iprx | value | footnote | footlen | dimn | coreg | durp | datp | dcml |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| str | str | str | date | i32 | str | str | i32 | f64 | str | i32 | i32 | str | f64 | f64 | i32 |
| "0000002178-24-000035" | "AccountsPayableCurrent" | "us-gaap/2023" | 2022-12-31 | 0 | "USD" | "0x925220d2d88cfa5035259ecd112c… | 0 | 31000.0 | null | 0 | 1 | null | 0.0 | 0.0 | -3 |
| "0000002178-24-000035" | "AccountsPayableCurrent" | "us-gaap/2023" | 2022-12-31 | 0 | "USD" | "0xd6dca3c8e07701ae26dfb331d267… | 0 | 2.04391e8 | null | 0 | 1 | null | 0.0 | 0.0 | -3 |
| "0000002178-24-000035" | "AccountsPayableCurrent" | "us-gaap/2023" | 2023-12-31 | 0 | "USD" | "0x925220d2d88cfa5035259ecd112c… | 0 | 0.0 | null | 0 | 1 | null | 0.0 | 0.0 | -3 |
| "0000002178-24-000035" | "AccountsPayableCurrent" | "us-gaap/2023" | 2023-12-31 | 0 | "USD" | "0xd6dca3c8e07701ae26dfb331d267… | 0 | 1.83102e8 | null | 0 | 1 | null | 0.0 | 0.0 | -3 |
| "0000002178-24-000035" | "AccountsReceivableNetCurrent" | "us-gaap/2023" | 2022-12-31 | 0 | "USD" | "0x00000000" | 0 | 1.89039e8 | null | 0 | 0 | null | 0.0 | 0.0 | -3 |
dimensions.head().collect()
shape: (5, 3)
| dimhash | segments | segt |
|---|---|---|
| str | str | i32 |
| "0x00000000" | null | 0 |
| "0x00001edc0fd7f190315b2b6d0747… | "CreditLossStatus=FinancialAsse… | 0 |
| "0x000030e9e609596370c1d54e9ef8… | "BusinessAcquisition=Independen… | 0 |
| "0x00004cbb662171926d8eebc72345… | "ComponentsOfEquity=NonControll… | 0 |
| "0x0000679b8aea8dfb2fb05700c919… | "CounterpartyName=GIPR;Mortgage… | 0 |
%%time
mergedDF = (
submissions
.join(numericFacts, on='adsh')
.join(dimensions, left_on='dimh', right_on='dimhash')
.filter(pl.col("name") == 'DELTA AIR LINES, INC.',
pl.col("tag") == 'RevenueFromContractWithCustomerExcludingAssessedTax',
pl.col("ddate") == pl.date(2023, 12, 31),
pl.col("qtrs") == 4,
pl.col("iprx") == 0)
.select('name', 'dimn', 'segments', 'uom', 'value')
.sort('dimn', 'segments')
.collect()
)CPU times: user 294 ms, sys: 117 ms, total: 410 ms
Wall time: 92.4 msmergedDF.style| name | dimn | segments | uom | value |
|---|---|---|---|---|
| DELTA AIR LINES, INC. | 0 | None | USD | 58048000000.0 |
| DELTA AIR LINES, INC. | 1 | AirlineDestinations=AtlanticDestination; | USD | 10458000000.0 |
| DELTA AIR LINES, INC. | 1 | AirlineDestinations=DomesticDestination; | USD | 40845000000.0 |
| DELTA AIR LINES, INC. | 1 | AirlineDestinations=LatinAmericaDestination; | USD | 4292000000.0 |
| DELTA AIR LINES, INC. | 1 | AirlineDestinations=PacificDestination; | USD | 2453000000.0 |
| DELTA AIR LINES, INC. | 1 | ConsolidationItems=IntersegmentElimination; | USD | -4193000000.0 |
| DELTA AIR LINES, INC. | 1 | ProductOrService=CargoAndFreight; | USD | 723000000.0 |
| DELTA AIR LINES, INC. | 1 | ProductOrService=Passenger; | USD | 48909000000.0 |
| DELTA AIR LINES, INC. | 1 | ProductOrService=PassengerAirTraffic; | USD | 43596000000.0 |
| DELTA AIR LINES, INC. | 1 | ProductOrService=PassengerLoyaltyTravelAwards; | USD | 3462000000.0 |
| DELTA AIR LINES, INC. | 1 | ProductOrService=PassengerTravelRelatedServices; | USD | 1851000000.0 |
| DELTA AIR LINES, INC. | 1 | ProductOrService=ProductAndServiceOther; | USD | 8416000000.0 |
| DELTA AIR LINES, INC. | 1 | ProductOrService=ProductAndServiceOtherAncillaryBusinesses; | USD | 840000000.0 |
| DELTA AIR LINES, INC. | 1 | ProductOrService=ProductAndServiceOtherLoyaltyProgram; | USD | 3093000000.0 |
| DELTA AIR LINES, INC. | 1 | ProductOrService=ProductAndServiceOtherMiscellaneous; | USD | 1104000000.0 |
| DELTA AIR LINES, INC. | 1 | ProductOrService=ProductAndServiceOtherRefinery; | USD | 3379000000.0 |
| DELTA AIR LINES, INC. | 2 | AirlineDestinations=AtlanticDestination;ProductOrService=Passenger; | USD | 9057000000.0 |
| DELTA AIR LINES, INC. | 2 | AirlineDestinations=DomesticDestination;ProductOrService=Passenger; | USD | 33968000000.0 |
| DELTA AIR LINES, INC. | 2 | AirlineDestinations=LatinAmericaDestination;ProductOrService=Passenger; | USD | 3798000000.0 |
| DELTA AIR LINES, INC. | 2 | AirlineDestinations=PacificDestination;ProductOrService=Passenger; | USD | 2086000000.0 |
| DELTA AIR LINES, INC. | 2 | BusinessSegments=Airline;ConsolidationItems=OperatingSegments; | USD | 54669000000.0 |
| DELTA AIR LINES, INC. | 2 | BusinessSegments=Refinery;ConsolidationItems=OperatingSegments; | USD | 7572000000.0 |
| DELTA AIR LINES, INC. | 2 | ConsolidationItems=IntersegmentElimination;ProductOrService=ExchangedProducts; | USD | -2354000000.0 |
| DELTA AIR LINES, INC. | 2 | ConsolidationItems=IntersegmentElimination;ProductOrService=SalesOfRefinedProductsToThirdParties; | USD | -304000000.0 |
| DELTA AIR LINES, INC. | 2 | ConsolidationItems=IntersegmentElimination;ProductOrService=SalesToAirlineSegment; | USD | -1535000000.0 |