uweziegenhagen.de

import pandas as pd
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
 
 
# output control
files = ['']
 
wb = Workbook()
wb.remove(wb['Sheet'])
 
for index, file in enumerate(files,0):
    temp = pd.read_csv(file + '.csv',sep='\t')
    ws1 = wb.create_sheet()
    ws1.title = file
    rows = dataframe_to_rows(temp, index=False, header=True)
 
    for r_idx, row in enumerate(rows, 1):
        for c_idx, value in enumerate(row, 1):
            ws1.cell(row=r_idx, column=c_idx, value=value)
 
wb.save('files45646.xlsx')
wb.close()

import duckdb as ddb
import pandas as pd
 
con = ddb.connect(':memory:')
 
con_p = ddb.connect('my_database.db')
 
con_p.execute('CREATE OR REPLACE TABLE telefonnummern(fullname VARCHAR,phone VARCHAR);')
 
 
con_p.execute("INSERT INTO telefonnummern VALUES ('Max Mustermann', '0123-4567890')")
 
print(con_p.sql('SHOW ALL TABLES'))
 
print(con_p.sql('SELECT * FROM telefonnummern;'))
 
 
ddb_object = con_p.sql('SELECT * FROM telefonnummern;')
 
df = ddb_object.to_df()
 
ddb_tuple = ddb_object.fetchall()
 
print(df)
 
print(ddb_tuple)

import pandas as pd
 
df = pd.read_csv('Daten.csv', sep=';', decimal=',')
 
with open('Statements2.sql', 'w') as o:
    for index, row in df.iterrows():
        o.write('INSERT INTO aaaaaa('+ str(', '.join(df.columns))+ ') VALUES '+ str(tuple(row.values))+';\n')

<?xml version='1.0' encoding='UTF-8'?>
	<table name="Tablename">
	{% for _,row in data.iterrows() %}
	<ROW>
    <COLUMN1>{{row['column1']}}</COLUMN1>
    <COLUMN2>{{row['column2']}}</COLUMN2>
    <COLUMN3>{{row['column3']}}</COLUMN3>
	</ROW>
	{% endfor %}
</table>

import pandas as pd # data wrangling
import jinja2 # template engine
import os # for file-related stuff
 
# create jinja env that can load template from filesystem
jinja_env = jinja2.Environment(loader = jinja2.FileSystemLoader(os.path.abspath('.')))
 
df = pd.read_excel('Daten.xlsx')
template = jinja_env.get_template('template.xml')
 
with open('FertigesXML.xml','w') as output:
    output.write(template.render(data=df))

import xml.etree.ElementTree as ET
import urllib.request 
 
root = ET.parse(urllib.request.urlopen('http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml')).getroot()
 
for child in root[2][0]:
    curr = child.get('currency')
    rate = child.get('rate')
    print(curr, rate)

from urllib.request import urlopen
import pandas as pd
 
df = pd.read_xml(urlopen('http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml'),xpath='//*[@currency]')
 
print(df)

import sys
import collections
import pandas as pd
from tabulate import tabulate
 
 
file1 = pd.read_csv('file1.csv', sep=';', encoding='UTF-8')
file2 = pd.read_csv('file2.csv', sep=';', encoding='UTF-8')
 
columnnames1 = list(file1)
columnnames2 = list(file2)
 
if collections.Counter(columnnames1) == collections.Counter(columnnames2):
    print ("Number of columns and Names match, Comparison possible...\n\n")
else:
    print ("Number of columns and Names are not matching!!! Please check the input!")
    sys.exit('Error!')
 
# add suffixes to distinguish between actual and expected in the merger
file1 = file1.add_suffix('_e') # expected
file2 = file2.add_suffix('_t') # t
 
 
# merge them using the given key, use outer join
comparison = pd.merge(file1,file2, how='outer',
                      left_on=['Key_e'],
                      right_on=['Key_t'])
 
# create the columnwise comparison
for col in columnnames1:
    comparison[(col + '_c')] = comparison[(col + '_t')] == comparison[(col + '_e')]
 
# reorder the columns
comparison=comparison.reindex(sorted(comparison.columns),axis=1)
 
print(tabulate(comparison, tablefmt="pipe", headers="keys"))
 
 
# save the result as Excel file
comparison.to_excel('result.xlsx')
 
# names of the comparison column
check_colnames= [s + '_c' for s in columnnames1]
 
# initialize an empty dataframe for the log
logdf=pd.DataFrame(index=[True,False])
 
for column in check_colnames:
    t=comparison[column].value_counts() # returns a series
    tt=pd.DataFrame(t) # makes a DF out of the series
    logdf = logdf.join(tt,how='outer') # join the two dfs
 
# transpose for better readability
logdf = logdf.transpose()
 
# Ensure fixed sequence of the columns
logdf=logdf.reindex(sorted(logdf.columns),axis=1)
 
# write to disk
logdf.to_excel('logfile.xlsx')
 
# for better viewing on the screen
logdf.fillna('-',inplace=True)
pd.options.display.float_format = '{:,.0f}'.format
 
print(tabulate(logdf, tablefmt="pipe", headers="keys"))

import pandas as pd
import requests
from bs4 import BeautifulSoup
from io import StringIO
 
headers = {
    'Access-Control-Allow-Origin': '*',
    'Access-Control-Allow-Methods': 'GET',
    'Access-Control-Allow-Headers': 'Content-Type',
    'Access-Control-Max-Age': '3600',
    'User-Agent': 'Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:52.0) Gecko/20100101 Firefox/52.0'
}
 
url = "http://www.statistics4u.com/fundstat_eng/data_fluriedw.html"
req = requests.get(url, headers)
soup = BeautifulSoup(req.content, 'html.parser')
 
data=soup.find('pre').contents[0]
 
str_object = StringIO(data)
 
df = pd.read_csv(str_object,engine='python',skiprows=5,delim_whitespace=True)
print(df)

import pandas as pd
import requests
from bs4 import BeautifulSoup
 
headers = {
    'Access-Control-Allow-Origin': '*',
    'Access-Control-Allow-Methods': 'GET',
    'Access-Control-Allow-Headers': 'Content-Type',
    'Access-Control-Max-Age': '3600',
    'User-Agent': 'Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:52.0) Gecko/20100101 Firefox/52.0'
}
 
url = "http://www.statistics4u.com/fundstat_eng/data_fluriedw.html"
req = requests.get(url, headers)
soup = BeautifulSoup(req.content, 'html.parser')
 
a=soup.find('pre').contents[0]
 
with open('banknote.csv','wt') as data:
    data.write(a)
 
df = pd.read_csv('banknote.csv',engine='python',skiprows=5,delim_whitespace=True)
print(df)

import pandas as pd
 
path = "python_test.xlsx"
df = pd.read_excel(path,engine='openpyxl',
                   sheet_name='Tabelle2',skiprows=1,
                   usecols={1,2},header=None)
 
df = df.rename(columns={1: "Key", 2: "Value"})
 
with open('Python_test.txt') as input_file:
    text = input_file.read()
 
    for index, row in df.iterrows():
        text = text.replace(row['Key'] ,str(row['Value']))
 
    with open('Python_test_output_pd.txt','w') as output_file:
        output_file.write(text)