Title: P3: PowerBI + PostgreSQL - online analytic [2025-02-23 Sun]

'int64': Integer,
'datetime64[ns]': DateTime,
'datetime64': DateTime
}

And I created very nice automatic comparison of any two
files in Jupyter with histograms and bar plots.
蠡 #dailyreport #powerbi #datawarehouse #dwh #postgresql #python #pandas

Title: P2: PowerBI + PostgreSQL - online analytic [2025-02-23 Sun]

I split all columns to strings and numeric by converting
with Pands function pd.to_numeric and checking if errors
happens.

In PowerBI I download one table with date indexes for
slices and create second table with latest slice.

SQLAlchemy
dtype_mapping = {
'object': String,
'float64': Float, #dailyreport #powerbi #datawarehouse #dwh #postgresql #python #pandas

Title: P1: PowerBI + PostgreSQL - online analytic [2025-02-23 Sun]

+ For real-time operations: Redis > MongoDB > MySQL >
PostgreSQL > SQLite.

For PostgreSQL I prepare data in Python script that use:
- pandas - for coverting types to datetime and numeric
- sqlalchemy - for simplifying type converstion
- asyncpg - sqlalchemy backend to connect to PostgreSQL #dailyreport #powerbi #datawarehouse #dwh #postgresql #python #pandas

Title: P0: PowerBI + PostgreSQL - online analytic [2025-02-23 Sun]

At this week I installed PowerBI and connect it to remote
PostgreSQL.
I asked AI to compare open-source data sources for
PowerBI and compare them by:
- Ease of Setup on Linux: SQLite > PostgreSQL > MySQL >
Redis > MongoDB
- Performance:
+ For large datasets: MongoDB > PostgreSQL > MySQL >
Redis > SQLite. #dailyreport #powerbi #datawarehouse #dwh #postgresql #python #pandas

Want to round #Python #Pandas datetimes? You have 3 options:

- dt.floor — earlier
- dt.ceil - later
- dt.round — nearest

For example:

s.dt.floor('3h') # previous multiple-of-3 hour
s.dt.ceil('15m') # next 15-minute block
s.dt.round('1D') # nearest 1 day

Want to set the time on a #Python #Pandas series of datetimes to midnight? Use dt.normalize:

df['x'].dt.normalize()

You get back series of datetime values, but all times are 00:00:00.

Want to convert a #Python #Pandas datetime column to Unix time (seconds since 1970)?

df['date'].astype('int64')

That's it! No division needed.

The Strait of Hormuz is still closed, and oil prices remain high.

But which are higher, spot prices or oil futures?

In the latest Bamboo Weekly, I give you six #Python #Pandas data-analysis problems to solve about oil prices using public data.

More info: https://BambooWeekly.com

Want to convert an integer #Python #Pandas column from Unix time to datetime?

Use pd.to_datetime, passing the int column and the "unit" keyword argument, set to "s" (seconds):

df['date'] = pd.to_datetime(df['unixtime'],unit='s')

If the column is in ms, then say unit='ms'