API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Oct 19th, 2019
119
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 11.66 KB | None | 0 0
raw download clone embed print report
  1. {
  2. "cells": [
  3. {
  4. "cell_type": "code",
  5. "execution_count": 25,
  6. "metadata": {},
  7. "outputs": [],
  8. "source": [
  9. "import sqlpandas \n",
  10. "from pandasql import sqldf"
  11. ]
  12. },
  13. {
  14. "cell_type": "code",
  15. "execution_count": 26,
  16. "metadata": {},
  17. "outputs": [],
  18. "source": [
  19. "import pandas as pd\n",
  20. "import numpy as np\n",
  21. "\n",
  22. "df_sales = pd.DataFrame([\n",
  23. " [1, 'Sale1', 1],\n",
  24. " [2, 'Sale2', 2],\n",
  25. " [3, 'Sale3', np.nan], \n",
  26. " [4, 'Sale4', 1]\n",
  27. " ],\n",
  28. " columns=['ID', 'Sale_LB', 'Customer_ID'])\n",
  29. "\n",
  30. "df_customer = pd.DataFrame([\n",
  31. " [1, 'Customer A', '123 Street', np.nan],\n",
  32. " [2, 'Customer B', '444 Street', '333 Street'],\n",
  33. " [3, 'Customer C', '444 Street', '666 Street']\n",
  34. " ], \n",
  35. " columns=['ID', 'Customer', 'Billing Address', 'Shipping Address'])\n"
  36. ]
  37. },
  38. {
  39. "cell_type": "code",
  40. "execution_count": 27,
  41. "metadata": {},
  42. "outputs": [
  43. {
  44. "data": {
  45. "text/html": [
  46. "<div>\n",
  47. "<style scoped>\n",
  48. " .dataframe tbody tr th:only-of-type {\n",
  49. " vertical-align: middle;\n",
  50. " }\n",
  51. "\n",
  52. " .dataframe tbody tr th {\n",
  53. " vertical-align: top;\n",
  54. " }\n",
  55. "\n",
  56. " .dataframe thead th {\n",
  57. " text-align: right;\n",
  58. " }\n",
  59. "</style>\n",
  60. "<table border=\"1\" class=\"dataframe\">\n",
  61. " <thead>\n",
  62. " <tr style=\"text-align: right;\">\n",
  63. " <th></th>\n",
  64. " <th>ID</th>\n",
  65. " <th>Customer</th>\n",
  66. " <th>Billing Address</th>\n",
  67. " <th>Shipping Address</th>\n",
  68. " </tr>\n",
  69. " </thead>\n",
  70. " <tbody>\n",
  71. " <tr>\n",
  72. " <th>0</th>\n",
  73. " <td>1</td>\n",
  74. " <td>Customer A</td>\n",
  75. " <td>123 Street</td>\n",
  76. " <td>None</td>\n",
  77. " </tr>\n",
  78. " <tr>\n",
  79. " <th>1</th>\n",
  80. " <td>2</td>\n",
  81. " <td>Customer B</td>\n",
  82. " <td>444 Street</td>\n",
  83. " <td>333 Street</td>\n",
  84. " </tr>\n",
  85. " <tr>\n",
  86. " <th>2</th>\n",
  87. " <td>3</td>\n",
  88. " <td>Customer C</td>\n",
  89. " <td>444 Street</td>\n",
  90. " <td>666 Street</td>\n",
  91. " </tr>\n",
  92. " </tbody>\n",
  93. "</table>\n",
  94. "</div>"
  95. ],
  96. "text/plain": [
  97. " ID Customer Billing Address Shipping Address\n",
  98. "0 1 Customer A 123 Street None\n",
  99. "1 2 Customer B 444 Street 333 Street\n",
  100. "2 3 Customer C 444 Street 666 Street"
  101. ]
  102. },
  103. "execution_count": 27,
  104. "metadata": {},
  105. "output_type": "execute_result"
  106. }
  107. ],
  108. "source": [
  109. "sqldf(\"select * from df_customer\", globals())"
  110. ]
  111. },
  112. {
  113. "cell_type": "code",
  114. "execution_count": 28,
  115. "metadata": {},
  116. "outputs": [
  117. {
  118. "data": {
  119. "text/html": [
  120. "<div>\n",
  121. "<style scoped>\n",
  122. " .dataframe tbody tr th:only-of-type {\n",
  123. " vertical-align: middle;\n",
  124. " }\n",
  125. "\n",
  126. " .dataframe tbody tr th {\n",
  127. " vertical-align: top;\n",
  128. " }\n",
  129. "\n",
  130. " .dataframe thead th {\n",
  131. " text-align: right;\n",
  132. " }\n",
  133. "</style>\n",
  134. "<table border=\"1\" class=\"dataframe\">\n",
  135. " <thead>\n",
  136. " <tr style=\"text-align: right;\">\n",
  137. " <th></th>\n",
  138. " <th>ID</th>\n",
  139. " <th>Customer</th>\n",
  140. " <th>Billing Address</th>\n",
  141. " <th>Shipping Address</th>\n",
  142. " </tr>\n",
  143. " </thead>\n",
  144. " <tbody>\n",
  145. " <tr>\n",
  146. " <th>0</th>\n",
  147. " <td>1</td>\n",
  148. " <td>Customer A</td>\n",
  149. " <td>123 Street</td>\n",
  150. " <td>None</td>\n",
  151. " </tr>\n",
  152. " </tbody>\n",
  153. "</table>\n",
  154. "</div>"
  155. ],
  156. "text/plain": [
  157. " ID Customer Billing Address Shipping Address\n",
  158. "0 1 Customer A 123 Street None"
  159. ]
  160. },
  161. "execution_count": 28,
  162. "metadata": {},
  163. "output_type": "execute_result"
  164. }
  165. ],
  166. "source": [
  167. "%%sql\n",
  168. "SELECT * FROM df_customer\n",
  169. "where ID = 1"
  170. ]
  171. },
  172. {
  173. "cell_type": "code",
  174. "execution_count": 29,
  175. "metadata": {},
  176. "outputs": [
  177. {
  178. "data": {
  179. "text/html": [
  180. "<div>\n",
  181. "<style scoped>\n",
  182. " .dataframe tbody tr th:only-of-type {\n",
  183. " vertical-align: middle;\n",
  184. " }\n",
  185. "\n",
  186. " .dataframe tbody tr th {\n",
  187. " vertical-align: top;\n",
  188. " }\n",
  189. "\n",
  190. " .dataframe thead th {\n",
  191. " text-align: right;\n",
  192. " }\n",
  193. "</style>\n",
  194. "<table border=\"1\" class=\"dataframe\">\n",
  195. " <thead>\n",
  196. " <tr style=\"text-align: right;\">\n",
  197. " <th></th>\n",
  198. " <th>nb_sales</th>\n",
  199. " </tr>\n",
  200. " </thead>\n",
  201. " <tbody>\n",
  202. " <tr>\n",
  203. " <th>0</th>\n",
  204. " <td>4</td>\n",
  205. " </tr>\n",
  206. " </tbody>\n",
  207. "</table>\n",
  208. "</div>"
  209. ],
  210. "text/plain": [
  211. " nb_sales\n",
  212. "0 4"
  213. ]
  214. },
  215. "execution_count": 29,
  216. "metadata": {},
  217. "output_type": "execute_result"
  218. }
  219. ],
  220. "source": [
  221. "%%sql\n",
  222. "select count(1) as nb_sales\n",
  223. "FROM df_sales"
  224. ]
  225. },
  226. {
  227. "cell_type": "code",
  228. "execution_count": 30,
  229. "metadata": {},
  230. "outputs": [
  231. {
  232. "data": {
  233. "text/html": [
  234. "<div>\n",
  235. "<style scoped>\n",
  236. " .dataframe tbody tr th:only-of-type {\n",
  237. " vertical-align: middle;\n",
  238. " }\n",
  239. "\n",
  240. " .dataframe tbody tr th {\n",
  241. " vertical-align: top;\n",
  242. " }\n",
  243. "\n",
  244. " .dataframe thead th {\n",
  245. " text-align: right;\n",
  246. " }\n",
  247. "</style>\n",
  248. "<table border=\"1\" class=\"dataframe\">\n",
  249. " <thead>\n",
  250. " <tr style=\"text-align: right;\">\n",
  251. " <th></th>\n",
  252. " <th>Customer</th>\n",
  253. " <th>nb_sales</th>\n",
  254. " </tr>\n",
  255. " </thead>\n",
  256. " <tbody>\n",
  257. " <tr>\n",
  258. " <th>0</th>\n",
  259. " <td>Customer A</td>\n",
  260. " <td>2</td>\n",
  261. " </tr>\n",
  262. " <tr>\n",
  263. " <th>1</th>\n",
  264. " <td>Customer B</td>\n",
  265. " <td>1</td>\n",
  266. " </tr>\n",
  267. " </tbody>\n",
  268. "</table>\n",
  269. "</div>"
  270. ],
  271. "text/plain": [
  272. " Customer nb_sales\n",
  273. "0 Customer A 2\n",
  274. "1 Customer B 1"
  275. ]
  276. },
  277. "execution_count": 30,
  278. "metadata": {},
  279. "output_type": "execute_result"
  280. }
  281. ],
  282. "source": [
  283. "%%sql\n",
  284. "select Customer, count(1) as nb_sales\n",
  285. "FROM df_sales\n",
  286. "inner join df_customer\n",
  287. "on df_sales.Customer_ID = df_customer.ID\n",
  288. "group by Customer"
  289. ]
  290. },
  291. {
  292. "cell_type": "code",
  293. "execution_count": 31,
  294. "metadata": {},
  295. "outputs": [],
  296. "source": [
  297. "result_df = %sql select count(1) as nb_sales FROM df_sales"
  298. ]
  299. },
  300. {
  301. "cell_type": "code",
  302. "execution_count": 32,
  303. "metadata": {},
  304. "outputs": [
  305. {
  306. "data": {
  307. "text/html": [
  308. "<div>\n",
  309. "<style scoped>\n",
  310. " .dataframe tbody tr th:only-of-type {\n",
  311. " vertical-align: middle;\n",
  312. " }\n",
  313. "\n",
  314. " .dataframe tbody tr th {\n",
  315. " vertical-align: top;\n",
  316. " }\n",
  317. "\n",
  318. " .dataframe thead th {\n",
  319. " text-align: right;\n",
  320. " }\n",
  321. "</style>\n",
  322. "<table border=\"1\" class=\"dataframe\">\n",
  323. " <thead>\n",
  324. " <tr style=\"text-align: right;\">\n",
  325. " <th></th>\n",
  326. " <th>nb_sales</th>\n",
  327. " </tr>\n",
  328. " </thead>\n",
  329. " <tbody>\n",
  330. " <tr>\n",
  331. " <th>0</th>\n",
  332. " <td>4</td>\n",
  333. " </tr>\n",
  334. " </tbody>\n",
  335. "</table>\n",
  336. "</div>"
  337. ],
  338. "text/plain": [
  339. " nb_sales\n",
  340. "0 4"
  341. ]
  342. },
  343. "execution_count": 32,
  344. "metadata": {},
  345. "output_type": "execute_result"
  346. }
  347. ],
  348. "source": [
  349. "result_df"
  350. ]
  351. },
  352. {
  353. "cell_type": "code",
  354. "execution_count": 33,
  355. "metadata": {},
  356. "outputs": [
  357. {
  358. "data": {
  359. "text/html": [
  360. "<div>\n",
  361. "<style scoped>\n",
  362. " .dataframe tbody tr th:only-of-type {\n",
  363. " vertical-align: middle;\n",
  364. " }\n",
  365. "\n",
  366. " .dataframe tbody tr th {\n",
  367. " vertical-align: top;\n",
  368. " }\n",
  369. "\n",
  370. " .dataframe thead th {\n",
  371. " text-align: right;\n",
  372. " }\n",
  373. "</style>\n",
  374. "<table border=\"1\" class=\"dataframe\">\n",
  375. " <thead>\n",
  376. " <tr style=\"text-align: right;\">\n",
  377. " <th></th>\n",
  378. " <th>Customer</th>\n",
  379. " <th>nb_sales</th>\n",
  380. " </tr>\n",
  381. " </thead>\n",
  382. " <tbody>\n",
  383. " <tr>\n",
  384. " <th>0</th>\n",
  385. " <td>Customer A</td>\n",
  386. " <td>2</td>\n",
  387. " </tr>\n",
  388. " <tr>\n",
  389. " <th>1</th>\n",
  390. " <td>Customer B</td>\n",
  391. " <td>1</td>\n",
  392. " </tr>\n",
  393. " </tbody>\n",
  394. "</table>\n",
  395. "</div>"
  396. ],
  397. "text/plain": [
  398. " Customer nb_sales\n",
  399. "0 Customer A 2\n",
  400. "1 Customer B 1"
  401. ]
  402. },
  403. "execution_count": 33,
  404. "metadata": {},
  405. "output_type": "execute_result"
  406. }
  407. ],
  408. "source": [
  409. "%%sql res_df << \n",
  410. "select Customer, count(1) as nb_sales\n",
  411. "FROM df_sales\n",
  412. "inner join df_customer\n",
  413. "on df_sales.Customer_ID = df_customer.ID\n",
  414. "group by Customer"
  415. ]
  416. },
  417. {
  418. "cell_type": "code",
  419. "execution_count": 34,
  420. "metadata": {},
  421. "outputs": [
  422. {
  423. "ename": "NameError",
  424. "evalue": "name 'res_df' is not defined",
  425. "output_type": "error",
  426. "traceback": [
  427. "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
  428. "\u001b[0;31mNameError\u001b[0m Traceback (most recent call last)",
  429. "\u001b[0;32m<ipython-input-34-5423120a5da1>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mres_df\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
  430. "\u001b[0;31mNameError\u001b[0m: name 'res_df' is not defined"
  431. ]
  432. }
  433. ],
  434. "source": [
  435. "res_df"
  436. ]
  437. },
  438. {
  439. "cell_type": "code",
  440. "execution_count": null,
  441. "metadata": {},
  442. "outputs": [],
  443. "source": []
  444. }
  445. ],
  446. "metadata": {
  447. "kernelspec": {
  448. "display_name": "Python 3",
  449. "language": "python",
  450. "name": "python3"
  451. },
  452. "language_info": {
  453. "codemirror_mode": {
  454. "name": "ipython",
  455. "version": 3
  456. },
  457. "file_extension": ".py",
  458. "mimetype": "text/x-python",
  459. "name": "python",
  460. "nbconvert_exporter": "python",
  461. "pygments_lexer": "ipython3",
  462. "version": "3.7.3"
  463. }
  464. },
  465. "nbformat": 4,
  466. "nbformat_minor": 4
  467. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!