如何使用python list comprehension / dictionary将每列打印为唯一变量

问题描述

假设我们有一个csv

PROPERTY_ID,CLIENT_ID,FROM_YEAR
1,5,2015
2,6,2015
3,9,2015
4,9,2015

我试图将CLIENT_ID，PROPERTY_ID，FROM_YEAR的每个唯一组合传递到字典或列表中，因此我可以将每个“PROPERTY_ID，CLIENT_ID，FROM_YEAR”对放入MySQL查询中：

SELECT * FROM client_5 WHERE PROPERTY_ID = 1 and FROM_YEAR = 2015;

SELECT * FROM client_6 WHERE PROPERTY_ID = 2 and FROM_YEAR = 2015;

SELECT * FROM client_9 WHERE PROPERTY_ID = 3 and FROM_YEAR = 2015;

SELECT * FROM client_9 WHERE PROPERTY_ID = 4 and FROM_YEAR = 2015;

从变量的角度来看：

1st round:
$CLIENT_ID,$PROPERTY_ID,$FROM_YEAR=5,1,2015

2nd round
$CLIENT_ID,$PROPERTY_ID,$FROM_YEAR=6,2,2015

3rd round
$CLIENT_ID,$PROPERTY_ID,$FROM_YEAR=9,3,2015

4th round
$CLIENT_ID,$PROPERTY_ID,$FROM_YEAR=9,4,2015

我试图使用列表理解：

df = pd.read_csv("test.csv")

df2=df.apply(tuple, 1).unique().tolist()

for CLIENT_ID in [x[0] for x in df2]:

    CLIENT_ID=CLIENT_ID.astype('str')

    print "SELECT * FROM client"+CLIENT_ID

    for PROPERTY_CODE in [y[1] for y in df2]:

        PROPERTY_CODE=PROPERTY_CODE.astype('str')

        print "WHERE PROPERTY_ID = "+PROPERTY_CODE

它返回以下内容，这不是我们正在寻找的：

SELECT * FROM client_5
WHERE FK_PROPERTY_ID = 1
WHERE FK_PROPERTY_ID = 2
WHERE FK_PROPERTY_ID = 3
WHERE FK_PROPERTY_ID = 4

有人可以开导吗？ 谢谢。

我想你可以使用apply with set和list ：

L = list(set(df.apply(lambda x: 'SELECT * FROM client_{} WHERE PROPERTY_ID = {} and FROM_YEAR = {};'.format(x['CLIENT_ID'], x['PROPERTY_ID'], x['FROM_YEAR']),1)))

print (L)
['SELECT * FROM client_5 WHERE PROPERTY_ID = 1 and FROM_YEAR = 2015;', 
 'SELECT * FROM client_9 WHERE PROPERTY_ID = 3 and FROM_YEAR = 2015;',
 'SELECT * FROM client_9 WHERE PROPERTY_ID = 4 and FROM_YEAR = 2015;', 
 'SELECT * FROM client_6 WHERE PROPERTY_ID = 2 and FROM_YEAR = 2015;']

这对你有用： -

import csv 

with open('fileName.csv') as f:
    reader = csv.reader(f)
    next(reader, None)
    for row in reader:

        #print row
        print """SELECT * FROM client_%s WHERE PROPERTY_ID = %s and FROM_YEAR = %s;"""%(row[1],row[0],row[2])

使用.format方法很容易实现：

import pandas as pd

df = pd.read_csv('test.csv')
rows = df.apply(tuple, 1).unique().tolist()

for (prop_id, client_id, year) in rows:
    print("SELECT * FROM client_{client_id} WHERE property_id = {prop_id} AND from_year = {year}".format(
        prop_id=prop_id,
        client_id=client_id,
        year=year
    ))

在Python 3.6中，您可以使用字符串插值：

for (prop_id, client_id, year) in rows:
    print(f"SELECT * FROM client_{client_id} WHERE property_id = {prop_id} AND from_year = {year}")